Queries w/ Joins & Concatenated Fields: find_by_sql vs find

Rails
1

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
2

SELECT 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 !!Es gratis!!

3

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

4

Thank you for your comments! - Brian