Andrew Andrew - 1 year ago 62
SQL Question

Rails Query Issue

I have photos which have_many comments.

I want to select whatever photos have recent comments and display those photos in a kind of "timeline" where the most recently commented photo is at the top and other photos fall below.

I tried this, and it worked on SQLite:

@photos = Photo.select('DISTINCT photos.*').joins(:comments).order('comments.created_at DESC')


However testing on PostgreSQL raises this error:

PGError: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
\n: SELECT DISTINCT photos.* FROM \"photos\" INNER JOIN \"comments\" ON \...


So, the problem is, I'm selecting Photos but ordering by recency of comments... and Postgre doesn't like that.

Can anyone suggest either:

A: How I can fix this query...

or

B: A different way to retrieve photos by the recency of their comments?

The important reason I'm doing it this way instead of through the comments model is I want to show each photo once with any recent comments beside it, not show each comment by itself with the same photos appearing multiple times.

Thanks!

Answer Source

Check out the :touch parameter of of the belongs_to association:

:touch

If true, the associated object will be touched (the updated_at/on attributes set to now) when this record is either saved or destroyed. If you specify a symbol, that attribute will be updated with the current time instead of the updated_at/on attribute.

http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html#method-i-belongs_to

In your Comment model, therefore, you would have:

belongs_to :photo, :touch => :comments_updated_at

Now, in order to create a time line of photos with recently updated comments all you need to do is:

Photo.order('comments_updated_at DESC').all 

Just be sure to add the "comments_updated_at" datetime field to your Photo model.

Make sense?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download