Rails - Dec 2009 - A particularly complex find using group that I can't seem to figure out.

I''ve got:

User
has_many :reviews

Review
belongs_to :user
belongs_to :album

Album
has_many :reviews

Some background:

The front page of my site contains for reviews. Currently, these are
the four most recent reviews in the system. However, if I submit a
review in the morning, and someone goes and submits 4 reviews at
lunch, I get bumped off the front page. What I''d like to do is group
the reviews by user so that the front page only displays one review --
the most recent -- per user. It follows that if my review 4th on the
front page (the oldest), and I submit a new review, I will get bumped
to 1st on the front page with my new review showing.

So originally, my finder was:
    @recent_reviews = Review.find(:all, :order => "created_at
DESC", :limit => 4)

I tried modifying it to:
    @recent_reviews = Review.find(:all, :order => "reviews.created_at
DESC", :group => "user_id", :limit => 4)

But it behaved strangely. My front page only contained one review per
user, as expected, however, the order was not what I expected. With
the latter finder, the system seems to take the first four reviews
created by the users with the most recent _first_ review, if you
follow.

So does anyone know what I''m doing wrong and how I can accomplish this
sort of sorting in the database? Is it even possible? Or am I looking
at some sorting in the ruby code itself. How might you accomplish
this?

Thanks to anyone and everyone who can lend a hand.

_john

--

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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Dec 23, 6:35 am, johnhutch
<johnhu...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> I''ve got:
>
> User
> has_many :reviews
>
> Review
> belongs_to :user
> belongs_to :album
>
> Album
> has_many :reviews
>
> Some background:
>
> The front page of my site contains for reviews. Currently, these are
> the four most recent reviews in the system. However, if I submit a
> review in the morning, and someone goes and submits 4 reviews at
> lunch, I get bumped off the front page. What I''d like to do is
group
> the reviews by user so that the front page only displays one review --
> the most recent -- per user. It follows that if my review 4th on the
> front page (the oldest), and I submit a new review, I will get bumped
> to 1st on the front page with my new review showing.
>
> So originally, my finder was:
>     @recent_reviews = Review.find(:all, :order => "created_at
> DESC", :limit => 4)
>
> I tried modifying it to:
>     @recent_reviews = Review.find(:all, :order =>
"reviews.created_at
> DESC", :group => "user_id", :limit => 4)
>
> But it behaved strangely. My front page only contained one review per
> user, as expected, however, the order was not what I expected. With
> the latter finder, the system seems to take the first four reviews
> created by the users with the most recent _first_ review, if you
> follow.
>
> So does anyone know what I''m doing wrong and how I can accomplish
this
> sort of sorting in the database? Is it even possible? Or am I looking
> at some sorting in the ruby code itself. How might you accomplish
> this?
>
The problem I think occurs because when you tell mysql to group rows
like this (ie there are columns other than the columns grouped by and
that are not aggregates like SUM or COUNT) mysql is free to pick what
it wants as the representative row. So if there are 3 reviews with
user_id 1, it can pick any of those rows for the columns you want (see
http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html :
"The server is free to return any value from the group, so the results
are indeterminate unless all values are the same").

You''ve got an order clause, but ordering takes place after grouping.

you might be able to do something like

select distinct user_id from reviews order by reviews.created_at desc
limit 4

which would get you the last 4 users who have posted and then retrieve
their reviews (maybe with a subselect or something)

Fred
> Thanks to anyone and everyone who can lend a hand.
>
> _john
--

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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.