# Queries w/ Joins & Concatenated Fields: find\_by\_sql vs find

**URL:** <https://www.ruby-forum.com/t/queries-w-joins-concatenated-fields-find-by-sql-vs-find/88844>\
**Category:** Rails\
**Created:** [March 14, 2007, 10:49pm UTC](https://www.ruby-forum.com/t/queries-w-joins-concatenated-fields-find-by-sql-vs-find/88844 "2007-03-14T22:49:28Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Brian\_L](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ab097/32.png) [@Brian\_L](https://www.ruby-forum.com/u/Brian_L)\
**Post date:** [March 14, 2007, 10:49pm UTC](https://www.ruby-forum.com/t/queries-w-joins-concatenated-fields-find-by-sql-vs-find/88844/1 "2007-03-14T22:49:28Z")

</div>

Hello,

I have the following query I want to use to create a html list  
in  
one of my forms:

SELECT  
contact.id,  
concat(contact.lname, ", ", contact.fname) AS name  
FROM  
contacts contact  
INNER JOIN  
lu\_contact\_categories category  
ON  
(contact.category\_id = category.id)  
WHERE  
(category.name = ‘Employee’)

I know i can use the find\_by\_sql method of ActiveRecord::Base to  
accomplish  
this.

I would, however, like to understand if:

1. this is an appropriate use of that method
2. the find method can also accomplish this.
3. which is the recommended approach.

Here is a little background on the tables & models.

Table definitions

[contacts]  
- id  
- fname  
- lname  
- category\_id

```
(category_id is a foreign key pointing to lu_contact_categories.id)

```

[lu\_contact\_categories]  
- id  
- name

Model for `contacts` table

class Contact \< ActiveRecord::Base  
has\_one :lu\_contact\_category, :foreign\_key =\> “id”  
end

Model for `lu_contact_categories` table

class LuContactCategory \< ActiveRecord::Base  
belongs\_to :contact, :foreign\_key =\> “category\_id”  
end

Any suggestions are appreciated!

- Brian

---

<div class="post-metadata">

**Author:** ![Brian\_L](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ab097/32.png) [@Brian\_L](https://www.ruby-forum.com/u/Brian_L)\
**Post date:** [March 15, 2007, 10:17am UTC](https://www.ruby-forum.com/t/queries-w-joins-concatenated-fields-find-by-sql-vs-find/88844/2 "2007-03-15T10:17:41Z")

</div>

> SELECT contact.id [http://contact.id](http://contact.id), concat(contact.lname,  
> ", ", contact.fname) AS name  
> FROM contacts contact INNER JOIN lu\_contact\_categories category  
> 2. the find method can also accomplish this.

you can do this with find, yes. The :select parameter represents the  
field list to retrieve (defaults to \*). Also, the :joins and :conditions  
parameters can be used to define a inner join. You could also use  
:include and save the :joins/:conditions part, but that will give you a  
LEFT OUTER.

regards,

javier ramirez

## –

Estamos de estreno… si necesitas llevar el control de tus gastos  
visita [http://www.gastosgem.com](http://www.gastosgem.com) !!Es gratis!!

---

<div class="post-metadata">

**Author:** ![Brian\_L](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ab097/32.png) [@Brian\_L](https://www.ruby-forum.com/u/Brian_L)\
**Post date:** [March 15, 2007, 1:55pm UTC](https://www.ruby-forum.com/t/queries-w-joins-concatenated-fields-find-by-sql-vs-find/88844/3 "2007-03-15T13:55:26Z")

</div>

FYI find\_by\_sql is more efficient then find because you are building  
the sql manually as opposed to it being built from the method

---

<div class="post-metadata">

**Author:** ![Brian\_L](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ab097/32.png) [@Brian\_L](https://www.ruby-forum.com/u/Brian_L)\
**Post date:** [March 15, 2007, 3:07pm UTC](https://www.ruby-forum.com/t/queries-w-joins-concatenated-fields-find-by-sql-vs-find/88844/4 "2007-03-15T15:07:21Z")

</div>

Thank you for your comments! - Brian