BACKGROUND: I'm on edge Rails (4.1.0.rc1). Users has many Communities through CommunityUser model. The following users belong to various communities:
USERS TABLE
ID | COMMUNITY_IDS
---|--------------
1 | [2, 7, 8]
2 | [3, 4, 8]
3 | [4, 5, 7]
4 | [3, 5, 7]
5 | [3, 8, 10]
6 | [4, 6, 7]
7 | [1, 8, 10]
8 | [3, 8, 10]
9 | [2, 9, 10]
10 | [3, 6, 10]
User.joins(:communities).where(communities: {id: [5,7]}).uniq
Returns all users associated with either Community 5 or Community 7:
SQL => SELECT DISTINCT "users".* FROM "users" INNER JOIN "community_users" ON "community_users"."user_id" = "users"."id" INNER JOIN "communities" ON "communities"."id" = "community_users"."community_id" WHERE "communities"."id" IN (5, 7)
ID | COMMUNITY_IDS
---|--------------
1 | [2, 7, 8]
3 | [4, 5, 7]
4 | [3, 5, 7]
6 | [4, 6, 7]
Trying to filter these further (to return those from this group also associated with Community 6) by adding another where clause is returning an empty ActiveRecord::Relation:
User.joins(:communities).where(communities: {id: [5,7]}).where(communities: {id: [6]}).uniq
=> SQL: SELECT DISTINCT "users".* FROM "users" INNER JOIN "community_users" ON "community_users"."user_id" = "users"."id" INNER JOIN "communities" ON "communities"."id" = "community_users"."community_id" WHERE "communities"."id" IN (5, 7) AND "communities"."id" IN (6)
=> #<ActiveRecord::Relation []>
OBJECTIVE: Is this the correct behavior for this query? If so, how would I write this query to return Users associated with EITHER Community 5 or Community 7 AND associated with Community 6.
Generally, the problem lies in AND operator. You should rewrite your query with OR operator instead.
Generally, there are impossible now, if you want real chains. Best, to pass all communities ids as a single array, or use multiple queries..
However, it is possible, if you real hipster, and want real chain.
In future Rails releases, ActiveRecord::Relation#or method may be appeared. Nowadays, there are possible to perform safe-for-your-project monkey patch.
Add this gist to /initializers folder: https://gist.github.com/j-mcnally/250eaaceef234dd8971b This will extends yours with extra-functionality.
Add a handy scope to User model:
scope :within_communities, -> (ids) { where(communities: {id: ids}) }
Use it:
relation = User.joins(:communities)
relation.within_communities([5,7])
relation.within_communities([5,7]).or.within_communities([1])