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.
--
Posted via http://www.ruby-forum.com/.
> 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) -- Posted via http://www.ruby-forum.com/.
On Oct 26, 5:32 pm, Mitchell Gould <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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> Thanks in advance. > -- > Posted viahttp://www.ruby-forum.com/.
On Oct 26, 2:06 pm, Mitchell Gould <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> 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) > > -- > 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).
> > 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. -- Posted via http://www.ruby-forum.com/.