Queries w/ Joins & Concatenated Fields: find_by_sql vs find

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

SELECT 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

Thank you for your comments! - Brian