How to write this SQL in Rails

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.

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.

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)

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

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

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.