Rails ActiveRecord查询以匹配所有参数 [英] Rails ActiveRecord query to match all params

问题描述

我需要一个ActiveRecord Postgres查询，该查询返回的结果与通过数组传递的所有参数匹配。

I need to have an ActiveRecord Postgres query that returns results which match all the parameters passed in through an array.

某些背景：我有一个User模型，有很多主题（通过专业）。我将主题ID作为字符串传递（参数：{ topics => 1,8,3} ），然后将它们转换为数组与 .split（'，'）在一起，所以我最终得到 topic_params = [ 1， 8， 3] 。

Some background: I have a User model, which has many Topics (through Specialties). I'm passing in the Topic ids as a string (Parameters: {"topics"=>"1,8,3"}) and then turning them into an array with .split(',') so I end up with topic_params = ["1","8","3"].

现在，我试图返回所有主题与所有主题匹配/包括的用户。在按照此问题中的答案后，我设法返回与以下任何主题匹配的用户：

Now I'm trying to return all Users who have Topics that match/include all of those. After following the answer in this question, I managed to return Users who match ANY of the Topics with this:

@users = User.includes(:topics, :organization).where(:topics => {:id => topic_params})

但是我需要返回与ALL匹配的结果。我也乐于接受更好的方法来总体上完成此类任务。

But I need it to return results that match ALL. I'd also be open to better ways to accomplish this sort of task overall.

推荐答案

一种方法就是这样

User.joins(:topics).where(topics: { id: [1, 2, 3] }).group('users.id').having('count(distinct topics.id) = 3')

显然，我没有您确切的架构，因此您可能需要对其进行一些微调，但这是基本设置。

Obviously I haven't your exact schema so you might have to tweak it a bit, but this is the basic setup.

重要的是，having子句计数器必须与您要匹配的项目数。

Important is that the having clause counter must match the number of items you're matching with.

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