Forum: Ruby on Rails newbie question : select distinct in model

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Robert Z. (Guest)
on 2006-04-19 15:12
(Received via mailing list)
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
Wiebe C. (Guest)
on 2006-04-19 17:07
(Received via mailing list)
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] http://api.rubyonrails.com/classes/ActiveRecord/Ba...
Robert Z. (Guest)
on 2006-04-19 17:26
(Received via mailing list)
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
Wiebe C. (Guest)
on 2006-04-19 17:42
(Received via mailing list)
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.
This topic is locked and can not be replied to.