I would like to have a method called feed in my User model that returns all the entries from two tables (discussions, exchanges).
In User.rb
def feed
SELECT * FROM discussions, exchanges GROUP BY created_at
end
This doesn't work, i get a problem in rails console
syntax error, unexpected ';', expecting '='
Can anyone show me how to write SQL in here? Basically I want to return and sort entries from two different tables..
if you want actual ActiveRecord objects you can try the following
def feed
exchanges = Exchange.all
discussions = Discussion.all
(exchanges + discussions).sort! { |a, b| a.created_at <=> b.created_at }
end
this is quite ineffective, as the sorting could be done in sql, but ActiveRecord cannot instantiate records selected from different tables (you can somehow override this by using STI)
Firstly - you can't just write plain SQL in your ruby code and expect it to work.
It's ruby, not SQL. They are different languages. If you can - use the ruby-way with associations instead (as per the other example).
However - if you desperately need to use raw SQL (eg you have legavy tables that don't match to models or have some complex combination-logic in teh SQL that doesn't easily map to assocations); then you need to pass SQL to the database... which means using a connection via Active Record.
Try:
def feed
ActiveRecord::Base.connection.execute("SELECT * FROM discussions, exchanges GROUP BY created_at")
end
It will not return ruby models for you - just a raw results-object. I'd recommend trying this in script/console and then doing a "puts my_user.feed.inspect" to have a look at the kind of thing it returns so you know how to use it.
Note: the presence of this kind of thing is considered a strong code smell - only use it where you really need it
