Unable to add custom relationships to avoid n+1 query problem

[khrisnagunanasurya-iav]

Expected Behaviour

Only 1 SQL query will be executed

administrators = Administrator.eager_load(:permisi_roles)
administrators.first.permisi_roles.first  # Permisi::Backend::ActiveRecord::Actor
administrators.last.permisi_roles.first # Permisi::Backend::ActiveRecord::Actor

Actual Behavior

class Administrator < ApplicationRecord
  include Permisi::Actable

  has_one :permisi_actor, as: :aka, dependent: :destroy, class_name: "Permisi::Backend::ActiveRecord::Actor"
  has_many :permisi_actor_roles, through: :permisi_actor, source: :aka, source_type: "Administrator"
  has_many :permisi_roles, through: :permisi_actor_roles, source: :permisi_actor
...

when I tried to call #permisi_roles it return this

$ Administrator.first.permisi_roles
  Administrator Load (1.5ms)  SELECT "administrators".* FROM "administrators" ORDER BY "administrators"."id" ASC LIMIT $1  [["LIMIT", 1]]
  Permisi::Backend::ActiveRecord::Actor Load (2.1ms)  SELECT "permisi_actors".* FROM "permisi_actors" INNER JOIN "administrators" ON "permisi_actors"."aka_id" = "administrators"."id" INNER JOIN "permisi_actors" "permisi_actors_permisi_roles" ON "administrators"."id" = "permisi_actors_permisi_roles"."aka_id" WHERE "permisi_actors_permisi_roles"."aka_id" = $1 AND "permisi_actors_permisi_roles"."aka_type" = $2 AND "permisi_actors"."aka_type" = $3 AND "permisi_actors_permisi_roles"."aka_type" = $4  [["aka_id", 1], ["aka_type", "Administrator"], ["aka_type", "Administrator"], ["aka_type", "Administrator"]]
  [#<Permisi::Backend::ActiveRecord::Actor:0x00007f5b2004a290
  id: 1,
  aka_type: "Administrator",
  aka_id: 1,
  created_at: Mon, 16 Aug 2021 14:14:01.563540000 +07 +07:00,
  updated_at: Wed, 08 Sep 2021 16:45:21.564520000 +07 +07:00>]

In this case, it's returning the Permisi::Backend::ActiveRecord::Actor not Permisi::Backend::ActiveRecord::Role

Is there a built-in way to be able to do the same that I'm trying to achieve? Thanks!

[ukazap]

A workaround would look like this:

# keep the model as is without custom associations:
class Administrator
  include Permisi::Actable
end

# say you have already loaded admins:
admins = Administrator.all # or Administrator.where(...), whichever you want.
admin_actor_dictionary = Permisi.actors.where(aka: admins).includes(:roles).reduce({}) do |dict, actor|
                          dict.merge({ actor.aka_id => actor }) }
                        end

# then you can access the dictionary like so:
admin_actor_dictionary[admins.first.id]
# or
admins.each do |admin|
  # do other stuff
  roles = admin_actor_dictionary[admin.id].roles
  # do other stuff
end

I agree this is ugly, ideally it should look like this:

Administrator.all.includes(actors: :roles)

Let's keep this issue open until the next release 🤞

I don't have enough time or energy to work on the fix, please submit a PR if you don't mind 🙂