1

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

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})

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.

Community
  • 1
  • 1
Andrew
  • 472
  • 2
  • 24

1 Answers1

2

One way would be something like this

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.

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

Eyeslandic
  • 14,553
  • 13
  • 41
  • 54
  • How would I reflect that the number of items to match with is variable? Meaning that it can range from 1 up to ~10 (as opposed to being set at 3). – Andrew Dec 17 '16 at 20:38
  • having('count(distinct topics.id) = ?', topic_params.split(',').count) or something like that – Eyeslandic Dec 17 '16 at 20:41
  • Alright. One more part of this: is there a way to eager load the other table(s), because I'd like to if possible. – Andrew Dec 17 '16 at 20:47
  • Take a look at this http://blog.bigbinary.com/2013/07/01/preload-vs-eager-load-vs-joins-vs-includes.html – Eyeslandic Dec 17 '16 at 20:55