mysql - 多对多关系的排序和过滤

Question

我正在尝试将标签合并到一个网站中，但现在已经遇到了几个小时的障碍。在我的索引页面上，我想显示所有至少具有当前所选标签的所有文章。我看到了Rails 3 many to many query condition并添加了

@articles = Article.find_by_sql([ "SELECT * FROM articles p
JOIN (
    SELECT pt.post_id FROM articles_tags pt
    JOIN posts p ON p.id = pt.articles_id
    JOIN tags t ON t.id = pt.tag_id
    WHERE t.label IN (?)
    GROUP BY pt.post_id
    HAVING count(pt.post_id) = ?
) pt ON p.id = pt.post_id", names_array, names_array.size])

但现在我想包括创建它的用户并按时间戳对其进行排序。它不会让您包含或按 find_by_sql 的结果排序

帮助

Answer 1

假设作者在表“creators”中，这样​​怎么样:

@articles = Article.find_by_sql([ "SELECT * FROM articles p
JOIN (
    SELECT pt.post_id FROM articles_tags pt
    JOIN posts p ON p.id = pt.articles_id
    JOIN tags t ON t.id = pt.tag_id
    JOIN creators c ON c.id = p.creator_id
    WHERE t.label IN (?)
    GROUP BY pt.post_id
    HAVING count(pt.post_id) = ?
) pt ON p.id = pt.post_id ORDER BY articles.created_at", names_array, names_array.size])