Squeel query syntax to retrieve users' elements sharing the same group

Question

Using Ruby on Rails 3.2.13 and Squeel I have the following models:

class Group < ActiveRecord::Base
  has_and_belongs_to_many :users
end

class User < ActiveRecord::Base
  has_and_belongs_to_many :groups
  has_many :characters, :dependent => :destroy
end

class Character < ActiveRecord::Base
  belongs_to :user
end

Characters have a boolean attribute :public.

Within the Character model, I want to retrieve all characters that are visible to the current user, as determined by the following conditions:

The character belongs to the current user OR
The character is public OR
The current user shares a group with the character's user

The result has to be an ActiveRecord::Relation.

Matching the first two conditions is simple enough:

def self.own_or_public user_to_check
  where{
    (user_id == user_to_check.id) |
    (public)
  }
end

For the third condition the following query yields the correct results, but is probably not the best way to do it:

def self.shares_group_with user_to_check
  user_groups = Group.joins{users}.where{users.id == user_to_check.id}
  joins{user.groups}.
    where{
      user.groups.id.in(user_groups.select(id))
    }.uniq
end

Furthermore, I cannot find a way to concatenate the two results yielding an ActiveRecord::Relation containing the results from both queries (merge yields elements that match both queries, and + returns an Array instead of an ActiveRecord::Relation).

Any help on how to handle this in one single Squeel query is much appreciated.

Gosh, got a tumbleweed for this: No one knows, no one cares... — McLibboc, Apr 18 '13 at 15:05

erroric · Accepted Answer · 2013-08-16T18:48:58.750

Let's try restructuring you problem a little and replace the has_and_belongs_to_many associations with has_many, through associations, and we will add another has_many, through association on the Character model as follows:

class Membership < ActiveRecord::Base
    belongs_to :user
    belongs_to :group
end

class Group < ActiveRecord::Base
    has_many :memberships
    has_many :users, through: :memberships
end

class User < ActiveRecord::Base
    has_many :memberships
    has_many :groups, through: :memberships
    has_many :characters
end

class Character < ActiveRecord::Base
    belongs_to :user
    has_many :groups, through: :user
end

The Membership model is the representation of the relationship between a User and a Group - essentially the join table that is hidden when using has_and_belongs_to_many. I prefer to see the relationship (especially if it is important).

We also have an association on the Character model to the Groups associated with the user. This is helpful when we try to join our scopes.

Fleshing the Character model out, let's add the following:

sifter :by_user do |user|
  user_id == user.id
end

sifter :public do
  public
end

Using the sifters as our building blocks, we can add the following to get the visible characters (as you defined it) with:

def self.get_visible(user)
    Character.uniq.joins{groups.outer}.where{(sift :public)|(sift :by_user, user)|(groups.id.in(user.groups))}
end

This method takes an instance of User and finds the following Characters:

All public characters.
All the user's characters.
All characters that belong to the user's groups.

And then we only take the distinct list of characters from those sets.

From rails console:

irb(main):053:0> Character.get_visible(User.find(4))
  User Load (0.6ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 4]]
  Group Load (0.7ms)  SELECT "groups".* FROM "groups" INNER JOIN "memberships" ON "groups"."id" = "memberships"."group_id" WHERE "memberships"."user_id" = 4
  Character Load (0.9ms)  SELECT DISTINCT "characters".* FROM "characters" LEFT OUTER JOIN "users" ON "users"."id" = "characters"."user_id" LEFT OUTER JOIN "memberships" ON "memberships"."user_id" = "users"."id" LEFT OUTER JOIN "groups" ON "groups"."id" = "memberships"."group_id" WHERE ((("characters"."public" OR "characters"."user_id" = 4) OR "groups"."id" IN (2)))
[
    [0] #<Character:0x00000005a16b48> {
                :id => 4,
           :user_id => 4,
              :name => "Testiculies",
        :created_at => Tue, 13 Aug 2013 14:35:50 UTC +00:00,
        :updated_at => Tue, 13 Aug 2013 14:35:50 UTC +00:00,
            :public => nil
    },
    [1] #<Character:0x00000005d9db40> {
                :id => 1,
           :user_id => 1,
              :name => "conan",
        :created_at => Mon, 12 Aug 2013 20:18:52 UTC +00:00,
        :updated_at => Tue, 13 Aug 2013 12:53:42 UTC +00:00,
            :public => true
    }
]

To find all characters a particular User has, add an instance method to the User model:

def get_visible_characters
  Character.get_visible(self)
end

I think that will get you where you want to go.

Thanks a lot for your answer. There are two gotchas, however: The `distinct` scope yields an `ActiveRecord::StatementInvalid`error and I replaced the `joins{groups}` with `joins{groups.outer}`to get own characters when user is not in a group. Need some more tests, but without the `distinct`scope it seems to work with the sifters. — McLibboc, Aug 15 '13 at 09:07
The distinct scope works for me using Postgres, but I'm going to edit the answer and use `uniq` instead. Does that work for you? And good catch on the outer join to groups. — erroric, Aug 16 '13 at 18:41
I did some more testing, and everything is working fine. Even without the `uniq`. So thanks again for your answer, much appreciated. — McLibboc, Aug 16 '13 at 19:51

Squeel query syntax to retrieve users' elements sharing the same group

1 Answers1