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.

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

Any ideas?

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

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)

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

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.

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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs