Newbie question : select distinct in model

Rails
1

Hi guys,

I’m new so be gentle :slight_smile:

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

2

On Wednesday 19 April 2006 13:12, Robert Z. wrote:

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] Peak Obsession

3

On 19/04/2006, at 11:06 PM, Wiebe C. 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

4

On Wednesday 19 April 2006 15:21, Robert Z. 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.