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 photo开发者_运维知识库s appearing multiple times.

Thanks!

---

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?

---

Just for the future readers of this question, the real answer to your SQL issue in SQlite vs Postgresql is that in the SQL "standard", every selected column needs to be in the GROUP BY or be an aggregate function.

https://www.techonthenet.com/sql/group_by.php (or whatever SQL ref you want to take a look at)

Your SQLite query used SELECT * instead of specific columns. That would have blown up with a similar error on most databases like Postgresql (MySQL, Maria, probably MSSQL Server). It's definitely invalid SQL grammar for a lot of good reasons.

Under the hood, I have no clue what SQlite does -- maybe it expands the * into fields and adds them to the GROUP BY under the hood? But its not a good SQL statement which is which it threw the error.