How to write this SQL in Rails

Rails
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

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

Any ideas?

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

3

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

On Oct 26, 5:32 pm, Mitchell G. [email protected]
wrote:

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

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.

6

On Oct 26, 2:06 pm, Mitchell G. [email protected]
wrote:

find(:first, :joins => :manuf_description,:include =>

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)

–
Posted viahttp://www.ruby-forum.com/.

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