# How to write this SQL in Rails

**URL:** <https://www.ruby-forum.com/t/how-to-write-this-sql-in-rails/177917>\
**Category:** Rails\
**Created:** [October 26, 2009, 6:32pm UTC](https://www.ruby-forum.com/t/how-to-write-this-sql-in-rails/177917 "2009-10-26T18:32:02Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![mitch\_newbie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ea5d25/32.png) [@mitch\_newbie](https://www.ruby-forum.com/u/mitch_newbie)\
**Post date:** [October 26, 2009, 6:32pm UTC](https://www.ruby-forum.com/t/how-to-write-this-sql-in-rails/177917/1 "2009-10-26T18:32:02Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![mitch\_newbie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ea5d25/32.png) [@mitch\_newbie](https://www.ruby-forum.com/u/mitch_newbie)\
**Post date:** [October 26, 2009, 6:42pm UTC](https://www.ruby-forum.com/t/how-to-write-this-sql-in-rails/177917/2 "2009-10-26T18:42:57Z")

</div>

> find(:first, :joins =\> :manuf\_description, :conditions =\> {:id =\>
> 
> Any ideas?

Add an :include =\> :manuf\_descriptions to your find call.

---

<div class="post-metadata">

**Author:** ![mitch\_newbie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ea5d25/32.png) [@mitch\_newbie](https://www.ruby-forum.com/u/mitch_newbie)\
**Post date:** [October 26, 2009, 7:06pm UTC](https://www.ruby-forum.com/t/how-to-write-this-sql-in-rails/177917/3 "2009-10-26T19:06:06Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![mitch\_newbie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ea5d25/32.png) [@mitch\_newbie](https://www.ruby-forum.com/u/mitch_newbie)\
**Post date:** [October 26, 2009, 8:00pm UTC](https://www.ruby-forum.com/t/how-to-write-this-sql-in-rails/177917/4 "2009-10-26T20:00:15Z")

</div>

On Oct 26, 5:32 pm, Mitchell G. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
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

---

<div class="post-metadata">

**Author:** ![mitch\_newbie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ea5d25/32.png) [@mitch\_newbie](https://www.ruby-forum.com/u/mitch_newbie)\
**Post date:** [October 27, 2009, 11:41am UTC](https://www.ruby-forum.com/t/how-to-write-this-sql-in-rails/177917/5 "2009-10-27T11:41:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mitch\_newbie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ea5d25/32.png) [@mitch\_newbie](https://www.ruby-forum.com/u/mitch_newbie)\
**Post date:** [October 26, 2009, 9:19pm UTC](https://www.ruby-forum.com/t/how-to-write-this-sql-in-rails/177917/6 "2009-10-26T21:19:53Z")

</div>

On Oct 26, 2:06 pm, Mitchell G. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
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).