Rails：搜索方法破坏了 PostgreSQL，有替代方案吗？
我的应用程序中有以下方法：
def self.tagged_with( string )
array = string.split(',').map{ |s| s.lstrip }
joins(:tags).where('tags.name' => array ).group('photos.id')
end
这是搜索照片，通过标记有很多标签。
此方法采用逗号分隔的标签列表，并返回具有与给定名称关联的标签的所有照片。
问题是，它会破坏 PostgreSQL，并显示以下错误消息：
ActionView::Template::Error (PGError: ERROR: column "photos.custom_title" must appear in the GROUP BY clause or be used in an aggregate function
: SELECT "photos".* FROM "photos" INNER JOIN "taggings" ON "photos"."id" = "taggings"."photo_id" INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE "tags"."name" IN ('village') AND ("photos".collection_id = 1) GROUP BY photos.id LIMIT 20 OFFSET 0):
在另一个有点类似的问题中，回答者建议，每当使用 .group() 时，都必须包含表中的所有列。这对我来说似乎很疯狂 - 如果您只能通过键入模型中的所有字段来使用 .group ，那它到底有什么意义呢？这很脆弱，而且总体来说是一个坏主意。
任何人都可以建议一种替代方法来使此方法与 PostgreSQL 一起使用吗？
I have the following method in my app:
def self.tagged_with( string )
array = string.split(',').map{ |s| s.lstrip }
joins(:tags).where('tags.name' => array ).group('photos.id')
end
This is searching photos, which have many tags through taggings.
This method takes a comma separated list of tags and returns any photos that have associated tags with the given names.
The problem is, it breaks PostgreSQL with the following error message:
ActionView::Template::Error (PGError: ERROR: column "photos.custom_title" must appear in the GROUP BY clause or be used in an aggregate function
: SELECT "photos".* FROM "photos" INNER JOIN "taggings" ON "photos"."id" = "taggings"."photo_id" INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE "tags"."name" IN ('village') AND ("photos".collection_id = 1) GROUP BY photos.id LIMIT 20 OFFSET 0):
In another somewhat similar question an answerer suggested that whenever using .group() it was necessary to include all columns in the table. This seems insane to me -- what the heck is the point of .group if you can only use it by typing up all the fields in your model. That's brittle, and just a bad idea overall.
Can anyone suggest an alternative way to make this method work with PostgreSQL?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
请查看这篇文章。
建议之一（“选项 2”）是在加入之前添加
select("distinct photos.*")。我从来没有这样做过，但似乎值得一试；当然比将每个选定的字段放入聚合函数中要好。Take a look at this article.
One of the suggestions ("Option 2") is to add
select("distinct photos.*")before your join. I've never done this but it seems worth a try; certainly better than putting every selected field into an aggregate function.