How do I use ActiveRecord for selects that return data across multiple tables, or a database view? (


#1

Hi,

If I want to return SQL data that cuts across multiple models (e.g.
not specific to one mode) how do I do this? Actually the use case
could be either (a) custom SQL I want to call or via (b) SQL call to a
database view I’ve created?

The select method
http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/DatabaseStatements.html#M001704
seems to be what I need however when I try to use this I’m getting an
error:

ActiveRecord::Base.connection.select(“select * from categories”)
NoMethodError: private method `select’ called for
#ActiveRecord::ConnectionAdapters::MysqlAdapter:0x253710c
from (irb):11


Greg
http://blog.gregnet.org/


#2

Greg H. wrote:

Hi,

If I want to return SQL data that cuts across multiple models (e.g.
not specific to one mode) how do I do this? Actually the use case
could be either (a) custom SQL I want to call or via (b) SQL call to a
database view I’ve created?

If I understand your question then the way to do this is through
associations defined in your model. You can have more than one
association defined between identical tables, each with a different name
and select conditions.

See:


#3

ok - I see I can do the following

ActiveRecord::Base.connection.select_all(‘select * from categories’)

and get a hash back

2009/2/2 Greg H. removed_email_address@domain.invalid:

error:

Greg
http://blog.gregnet.org/


Greg
http://blog.gregnet.org/


#4

Greg H. wrote:

ok - I see I can do the following

ActiveRecord::Base.connection.select_all(‘select * from categories’)

and get a hash back

How is that superior to:

Category.find(:all)

Or, if you have conditions:

Category.find(:all, :conditions => [:status = ?, my_status])