Andrew Andrew - 2 years ago 79
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 ='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...


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.


Answer Source

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


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.

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