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

rubyonrails-talk
1

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

2

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