Rails - Mar 2007 - Queries w/ Joins & Concatenated Fields: find_by_sql vs find

Hello,

I have the following query I want to use to create a html <select> 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Thank you for your comments!  - Brian


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---