Rails 3多对多查询条件 [英] Rails 3 many to many query condition

问题描述

我正在尝试在Rails 3中建立一个简单的Post/Tags关系. 一切正常，除非我想查询与几个标签相关的帖子. 基本上我想做这种事情:

I'm trying to do a simple Post/Tags relation in rails 3. Everything working fine except when I want to query the Posts which are related to several tags. Basically I'd like to do this kind of thing :

Post.joins(:tags).where('tags.name ILIKE ?', var)

但是我不想使用一个变量，而是要使用一个数组. 我试过了:

But instead of having just one var, I'd like to use an array. I tried :

Post.joins(:tags).where('tags.name IN (?)', names_array)

但不幸的是，它执行的是简单的LIKE(不是ILIKE)，并且像OR条件一样听起来很合逻辑.

But unfortunately it does a simple LIKE (not ILIKE) and works like a OR condition which sounds perfectly logical.

在这篇文章中，我还通过使用find_by_sql找到了另一个解决方案 http://snippets.dzone.com/posts/show/32

I also found another solution by using find_by_sql in this post http://snippets.dzone.com/posts/show/32

但是对我来说似乎有点难看.

But it seems a bit ugly to me.

以更好地理解问题. 我有3个帖子: 邮政 邮政B PostC

To better understand the problem. I've got 3 posts : PostA PostB PostC

PostA与TagA TagB和TagC标签有关. PostB与TagA和TagB标签相关. PostC仅与TagA相关.

PostA is related to TagA TagB and TagC tags. PostB is related to TagA and TagB tags. PostC is only related to TagA.

如果我查找TagA和TagC帖子，我想找到PostA，因为它与两个Tag相关. 使用哈希条件将返回PostA，PostB和PostC. 我想要的是与至少"所有指定标签有关的帖子.

If I look for TagA and TagC Posts I'd like to finds PostA because it is related to both Tags. Using a hash condition returns PostA PostB and PostC. What I want is the Posts which are related to "at least" all the specified Tags.

那么任何人都有更好的方法来解决这个问题?

So anyone has a better way to handle this ?

谢谢.

推荐答案

我不知道它是否可以解决您的问题，但是对于像这样的复杂查询，我几乎总是只使用

I don't know if it will solve you problem but for complex queries like that I almost always just use Squeel.

然后执行以下操作:

@posts = Post.joins(:tags)
  .where{tags.name.like_any names_array}
  .group("post_id")
  .having("count(post_id) = #{names_array.size}")

SQL希望看起来像这样

The SQL hopefully looks something like this

SELECT "posts".* FROM "posts"
  INNER JOIN "tags" ON "tags"."post_id" = "posts"."id"
  WHERE (("tags"."name" LIKE "TagA" OR "tags"."name" LIKE "TagB"))
  GROUP BY post_id
  HAVING count(post_id) = 2

如果我记得根据使用的数据库，squeel非常擅长使用ILIKE而不是LIKE. (至少比AR好)

If I remember squeel is pretty good at using ILIKE instead of LIKE depending on the database used. (atleast better than AR)

此外，您也可以使用AR而无需 squeel 来做到这一点，但我真的很喜欢其中的一些想法和助手随附 squeel ，如_all

Also you could do this using AR without squeel but I REALLY like some of the ideas and helpers that come with squeel like _all

关于爆炸...

假设我搜索了TagA和B.

Assume I searched for TagsA and B.

要做的是找到所有带有这些标签的帖子.

What that does is finds all the Posts with those tags.

所以您会得到类似的东西:

So you'll have something like:

• PostA TagA
• PostA TagB
• PostB TagA
• PostB TagB
• PostC TagA

然后，它将使用post_id将所有这些不同的Post结果按连接的标签分组.

Then it will group all those different Post results by the joined tags using post_id.

• PostA TagA TagB
• PostB TagA TagB
• PostC TagA

然后它将通过检查存在的forgien_ids来检查SQL行具有的标签数. 由于A和B具有2个标签，因此您知道它与您输入的所有内容匹配.

Then it will check the number of Tags the SQL line has by checking how many forgien_ids are present. Since A and B have 2 tags you know it matched all you input.

这篇关于Rails 3多对多查询条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！