How to write this SQL in Rails

rubyonrails-talk
1

I would like to write the following sql in Rails form.

SELECT manufacturers.*, manuf_descriptions.* FROM manufacturers INNER JOIN manuf_descriptions ON manuf_descriptions.manufacturer_id = manufacturers.id WHERE (manuf_descriptions.locale = fr AND manufacturers.id = 4)

I have tried the following:

find(:first, :joins => :manuf_description, :conditions => {:id => manufacturer_id, :manuf_descriptions => {:locale => I18n.locale}})

but it produces:

SELECT `manufacturers`.* FROM `manufacturers` INNER JOIN `manuf_descriptions` ON manuf_descriptions.manufacturer_id = manufacturers.id WHERE (`manuf_descriptions`.`locale` = 'en' AND `manufacturers`.`id` = '8') LIMIT 1

I am not getting the columns from the table manuf_details.

Any ideas?

Thanks in advance.

2

I would like to write the following sql in Rails form.

SELECT manufacturers.*, manuf_descriptions.* FROM manufacturers INNER JOIN manuf_descriptions ON manuf_descriptions.manufacturer_id = manufacturers.id WHERE (manuf_descriptions.locale = fr AND manufacturers.id = 4)

I have tried the following:

find(:first, :joins => :manuf_description, :conditions => {:id => manufacturer_id, :manuf_descriptions => {:locale => I18n.locale}})

but it produces:

SELECT `manufacturers`.* FROM `manufacturers` INNER JOIN `manuf_descriptions` ON manuf_descriptions.manufacturer_id = manufacturers.id WHERE (`manuf_descriptions`.`locale` = 'en' AND `manufacturers`.`id` = '8') LIMIT 1

I am not getting the columns from the table manuf_details.

Any ideas?

Add an :include => :manuf_descriptions to your find call.

3

Philip Hallstrom wrote:

find(:first, :joins => :manuf_description, :conditions => {:id =>

Any ideas?

Add an :include => :manuf_descriptions to your find call.

Hi Philip thanks for your help. I did as you suggested and got the following

find(:first, :joins => :manuf_description,:include => :manuf_description, :conditions => {:id => manufacturer_id, :manuf_descriptions => {:locale => I18n.locale}})

But Rails still produced the following output which I can see in the console:

SELECT `manufacturers`.* FROM `manufacturers` INNER JOIN `manuf_descriptions` ON manuf_descriptions.manufacturer_id = manufacturers.id WHERE (`manuf_descriptions`.`locale` = 'fr' AND `manufacturers`.`id` = '106') LIMIT 1

I need the descriptions column from the manuf_descriptions table so I need the generated SQL statement to include the line as follows:

SELECT manufacturers.*, manuf_descriptions.* FROM manufacturers INNER JOIN manuf_descriptions ON manuf_descriptions.manufacturer_id = manufacturers.id WHERE (manuf_descriptions.locale = fr AND manufacturers.id = 4)

4

SELECT `manufacturers`.* FROM `manufacturers` INNER JOIN `manuf_descriptions` ON manuf_descriptions.manufacturer_id = manufacturers.id WHERE (`manuf_descriptions`.`locale` = 'en' AND `manufacturers`.`id` = '8') LIMIT 1

I am not getting the columns from the table manuf_details.

Any ideas?

You can use the :select option to override the default table_name.* select clause (but be careful - attributes that have the same name in both tables (such as id) will shadow each other, which can cause all sorts of odd problems)

Fred

5

As far as I can think, you can't easily and usefully match this exact query via ActiveRecord's API. However, to achieve what it sounds like you want (loading both the manufacturers and manuf_descriptions tables in one query), something like this seems appropriate:

manufacturer = Manufacturers.first(:include => :manuf_description, :conditions => ["id = ? AND manuf_descriptions.locale = ?", manufacturer_id, locale])

Then again if you're only loading one record at a time, the overhead of the generated eager loading query *might* not outweight the cost of the second DB call when you call manufacturer.description (or whatever).

6

manufacturer = Manufacturers.first(:include => :manuf_description, :conditions => ["id = ? AND manuf_descriptions.locale = ?", manufacturer_id, locale])

Then again if you're only loading one record at a time, the overhead of the generated eager loading query *might* not outweight the cost of the second DB call when you call manufacturer.description (or whatever).

Hey thanks a lot. That works great and don't need to do the eager loading query because all the information I need is right there in the resulting hash.