having in ActiveRecord

user_13513921 0
Viewed 140

I have been trying to find a solution to my problem for a few days, so I am turning towards the community, hopefully I am not missing something obvious here.

I have 2 models in rails:

class Room
  has_many :accesses
end

class Access
  belongs_to :accessor, polymorphic: true
end

Accessor can be of 2 types: Person or Team

I am trying to find the most efficient way to find the rooms that a user has access to, but which are not accessible from any teams.

I tried:

Room.joins(:accesses).where(accesses: {accessor: Person.find(1234)}).where.not(accesses: {accessor_type: Team'})

But that returns the rooms that people have accesses to, it does not filter out the ones that Team AND People have access to.

I am thinking the having clause is the way to go, in which it would count the number of Teams accesses to rooms, and keep the rooms that have 0 team accesses. Though all my attempts are failing.

I would love to hear any advice.

activerecord ruby-on-rails
1 Answers

Left join

Instead of using HAVING, which requires us to add a GROUP BY, I'd start with a LEFT JOIN and a WHERE.

You can do this by left-joining to the room_accesses table specifically on "Team" accessor_type. We're left-joining because we're going to scope this join to only team accesses, and select only the rows where no such accesses exist. An inner join would not return these rows at all. We'll need to use a table alias as we're already using the room_accesses table to join to the person you are looking up.

We may as well admit Rails isn't great at this level of query abstraction, so let's just construct the raw SQL fragments for our first solution:

person = Person.find(1234)
person.rooms.joins(
  "LEFT JOIN room_accesses team_accesses
    ON team_accesses.room_id = rooms.id
    AND team_accesses.accessor_type = 'Team'"
).where("team_accesses.id IS NULL")

This generates, for SQLite,

SELECT "rooms".* FROM "rooms"
  INNER JOIN "room_accesses"
    ON "rooms"."id" = "room_accesses"."room_id"
  LEFT JOIN room_accesses team_accesses
    ON team_accesses.room_id = rooms.id
    AND team_accesses.accessor_type = 'Team'
  WHERE "room_accesses"."accessor_id" = 1
    AND "room_accesses"."accessor_type" = 'Person'
    AND (team_accesses.id IS NULL)

Having

You can do this with aHAVING by similarly joining to room_accesses again with the team_accesses alias, grouping by rooms.id (since we want at most one record per room), and selecting the groups HAVING a zero count of team accesses:

person.rooms.joins(
  "LEFT JOIN room_accesses team_accesses
    ON team_accesses.room_id = rooms.id
    AND team_accesses.accessor_type = 'Team'"
).group("rooms.id").having("COUNT(team_accesses.id) = 0")

generates:

SELECT "rooms".* FROM "rooms"
  INNER JOIN "room_accesses"
    ON "rooms"."id" = "room_accesses"."room_id"
  LEFT JOIN room_accesses team_accesses
    ON team_accesses.room_id = rooms.id
    AND team_accesses.accessor_type = 'Team'
  WHERE "room_accesses"."accessor_id" = 1
    AND "room_accesses"."accessor_type" = 'Person'
  GROUP BY rooms.id
  HAVING (COUNT(team_accesses.id) = 0)

Using associations instead of raw SQL

You can get halfway there in Rails by defining a scoped association:

class Room < ApplicationRecord
  has_many :room_accesses
  has_many :team_accesses, ->{ where accessor_type: "Team" }, class_name: "RoomAccess"
end

Assuming you're using a recent version of ActiveRecord, this allows you to do

person.rooms.left_joins(:team_accesses)

However, the table name used for this left joins is "team_accesses_rooms", which is predictable in this simple case but not part of the public API to my knowledge and subject to being changed if other joins are used in this same query. Still, if you're feeling daring:

person.rooms.left_joins(:team_accesses).where(team_accesses_rooms: {id: nil})

Frankly I would not recommend this method as you're relying on a table alias that you're not in control of and is not obvious where it comes from. With the raw SQL, you are in control of it and it's obvious where it came from.

user_21025910
Related