Rails 查询问题
我的照片有很多评论。
我想选择任何有最近评论的照片，并以一种“时间轴”的方式显示这些照片，其中最近评论的照片位于顶部，其他照片位于下方。
我尝试了这个，它在 SQLite 上工作：
@photos = Photo.select('DISTINCT photos.*').joins(:comments).order('comments.created_at DESC')
但是在 PostgreSQL 上测试会引发此错误：
PGError: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
\n: SELECT DISTINCT photos.* FROM \"photos\" INNER JOIN \"comments\" ON \...
所以，问题是，我选择照片，但按评论的新近度排序...而 Postgre 不喜欢这样。
任何人都可以建议：
A：我如何解决此查询...
或
B：通过评论的新近程度检索照片的不同方式？
我这样做而不是通过评论模型的重要原因是我想将每张照片显示一次，并在其旁边显示最近的评论，而不是单独显示每条评论，并多次出现相同的照片。
谢谢！
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!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
查看belongs_to关联的:touch参数：
http://api.rubyonrails.org/classes/ActiveRecord /Associations/ClassMethods.html#method-i-belongs_to
因此，在您的 Comment 模型中，您将拥有：
现在，为了创建包含最近更新的评论的照片时间线，您需要做的就是
：请务必将“comments_updated_at”日期时间字段添加到您的照片模型中。
有道理吗？
Check out the :touch parameter of of the belongs_to association:
http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html#method-i-belongs_to
In your Comment model, therefore, you would have:
Now, in order to create a time line of photos with recently updated comments all you need to do is:
Just be sure to add the "comments_updated_at" datetime field to your Photo model.
Make sense?
对于这个问题的未来读者，SQlite vs Postgresql 中 SQL 问题的真正答案是，在 SQL“标准”中，每个选定的列都需要位于 GROUP BY 中或者是聚合功能。
https://www.techonthenet.com/sql/group_by.php （或其他您想要查看的 SQL 参考）
您的 SQLite 查询使用了 SELECT * 而不是特定列。在 Postgresql（MySQL、Maria，可能还有 MSSQL Server）等大多数数据库上，这会因类似的错误而崩溃。出于很多充分的理由，这绝对是无效的 SQL 语法。
在幕后，我不知道 SQlite 做了什么——也许它将 * 扩展为字段并将它们添加到幕后的
GROUP BY中？但这不是一个好的 SQL 语句，这就是它引发错误的原因。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 BYunder the hood? But its not a good SQL statement which is which it threw the error.