查询 Activerecord HABTM 关系以包含数组的所有元素 [英] Query Activerecord HABTM relationship to include ALL elements of an array

问题描述

我有 Forum 和 ForumTag HABTM 关系.我还有一个名为 @tags 的变量数组.该数组包含一些论坛标签的名称.我希望能够查询并找到具有数组所有值的所有论坛.我目前有:

I have a Forum and ForumTag HABTM relationship. I also have an array of variables named @tags . This array contains the names of some ForumTags. I want to be able to query and find all forums that have ALL the values of the array. I currently have:

@forums = Forum.joines(:forum_tags).where(:forum_tags => {:name => @tags}).includes(:forum_tags).all

@forums = Forum.joines(:forum_tags).where(:forum_tags => {:name => @tags}).includes(:forum_tags).all

然而，这将返回数组中具有至少一个值的所有论坛.

However, this returns all the forums that have AT LEAST ONE value in the array.

推荐答案

以下将要求论坛具有 @tags 数组中的所有论坛标签.我假设 forum 不会多次使用相同的 forum_tag.

The following will require the forums to have all the forum tags in the @tags array. I am making the assumption that a forum will not have the same forum_tag more than once.

@forums = Forum.joins(:forum_tags).where(:forum_tags => {:name => @tags}).group("forums.id").having(['COUNT(*) = ?', @tags.length]).includes(:forum_tags).all

这将生成如下的 SQL 查询:

This will produce an SQL query like the following:

@tags = ['foo', 'bar']

SELECT forums.id, forum_tags.id FROM forums
  LEFT OUTER JOIN forum_tags_forums on forum_tags_forums.forum_id = forums.id
  LEFT OUTER JOIN forum_tags ON forum_tags.id = forum_tags_forums.forum_tag_id
  WHERE forum_tags.name IN ('foo', 'bar')
  GROUP BY forums.id
  HAVING COUNT(*) = 2;

这将按与给定标签匹配的论坛对连接表中的所有行进行分组.如果 COUNT 函数具有您要查找的标签总数的值(并且没有重复的 forum/forum_tag 对) 那么论坛必须包含所有标签.

This will group all the rows in the join table by forums that match the given tags. If the COUNT function has the value of the total number of tags that you're looking for (and there are no duplicate forum/forum_tag pairs) then the forum must contain all the tags.

获取剩余标签(评论中提出的问题):

To get the leftover tags (question asked in the comments):

forum_tags = ForumTag.where(:name => @tags)

@forums_with_leftovers = Forum.select("forums.*, GROUP_CONCAT(forum_tags.name) AS leftover_tags").joins(:forum_tags).where(['forums.id IN (?) AND NOT forum_tags.id IN (?)', @forums, forum_tags]).group("forums.id").all

@forums_with_leftovers 中的每个 Forum 对象都有一个额外的属性 leftover_tags，它包含每个论坛对象中的逗号分隔的标签列表不在原来的 @tags 变量中.

Each Forum object in @forums_with_leftovers will have an extra attribute leftover_tags that contains a comma separated list of tags in each forum object that is not in the original @tags variable.

这篇关于查询 Activerecord HABTM 关系以包含数组的所有元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！