CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3
jay
jay

Reputation: 12495

how do I write SQL in a ruby method?

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..

Upvotes: 2

Views: 3582

Answers (2)

Taryn East
Taryn East

Reputation: 27747

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

Upvotes: 3

binarycode
binarycode

Reputation: 1806

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)

Upvotes: 3

Related Questions