Rails - Apr 2006 - newbie question : select distinct in model

Hi guys,

I''m new so be gentle :-)

How do I put the following into a method inside a model....

select distinct(pairing_id), description from pairings_stories,  
pairings where pairing_id = pairings.id order by description

I basically want to have a @pairlist = Pairing.UniqueBookList line in  
a controller.

Just not sure how to wire up this method in the model.  And do I use  
find_by_sql or is there a nice rails/ruby technique rather than using  
the sql I typed above.

Thanks for your time and help,

Rob
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060419/9a56f18e/attachment.html

On Wednesday 19 April 2006 13:12, Robert Zolkos wrote:
> How do I put the following into a method inside a model....
> 
> select distinct(pairing_id), description from pairings_stories,
> pairings where pairing_id = pairings.id order by description
> 
> I basically want to have a @pairlist = Pairing.UniqueBookList line in
> a controller.
> 
> Just not sure how to wire up this method in the model.  And do I use
> find_by_sql or is there a nice rails/ruby technique rather than using
> the sql I typed above.
You can''t give the "find" method a distinct option (which is
logical, because
find on a model never results in duplicate hits), so you''ll have to use
"find_by_sql". If you could have used find, you could give an argument
like
":order => ''description''". See [1] for details. 

When making the method you want, be sure to make it as a class method, by
saying:

def Pairing.UniqueBookList
  find_by_sql(stuff)
end

BTW, in this particular case, I think you''ll be better off if you
create a join
between the "pairings" and "pairings_stories" tables with
the ON clause being
"pairings.id = pairings_stories.pairing_id".

References:
[1] http://api.rubyonrails.com/classes/ActiveRecord/Base.html#M000860

On 19/04/2006, at 11:06 PM, Wiebe Cazemier wrote:
> TW, in this particular case, I think you''ll be better off if you  
> create a join
> between the "pairings" and "pairings_stories" tables
with the ON
> clause being
> "pairings.id = pairings_stories.pairing_id".

Thanks for the reply.  With the above do you mean like this?

select distinct(pairings.id), pairings.description from pairings  
right join pairings_stories on pairings.id = pairings_stories.pairing_id


Regards,

Rob
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060419/1c7bd35f/attachment-0001.html

On Wednesday 19 April 2006 15:21, Robert Zolkos wrote:
> With the above do you mean like this?
> 
> select distinct(pairings.id), pairings.description from pairings
> right join pairings_stories on pairings.id = pairings_stories.pairing_id
More something like 

SELECT pairings.id FROM pairings
JOIN pairing_stories ON pairings.id = pairing_stories.pairing_id

I don''t know if this exact join will work, but it would be something
along
these lines. Also, the join is a suggestion. Try it out first to see if it
does what you want. Read the docs about joins of the DB you''re using as
well,
to learn what they do exactly.