Write an Arel inner join query without `find_by_sql`

Question

In a Rails 5 project, I have a query method for an association that currently uses an SQL string, and I'm trying to refactor it to use only Arel. I'm wondering if it's possible to write this whole query method without using to_sql and find_by_sql.

The query is an INNER_JOIN with a join table.

Here's the old, SQL string method:

class User < AbstractModel
  ...

  # Return an Array of Projects that this User is an admin for.
  def projects_admin
    @projects_admin ||= Project.find_by_sql(%(
      SELECT projects.* FROM projects, user_groups_users
      WHERE projects.admin_group_id = user_groups_users.user_group_id
        AND user_groups_users.user_id = #{id}
    ))
  end

Here's my Arel rewrite, which gets the same query results:

  # Return an Array of Projects that this User is an admin for.
  def projects_admin
    projects = Project.arel_table
    # for join tables with no model, need to create an Arel::Table object
    # so we can use Arel methods on it, eg access columns
    u_g_users = Arel::Table.new("user_groups_users")

    arel = projects.project(Arel.star).join(u_g_users).
           on(projects[:admin_group_id].eq(u_g_users[:user_group_id]).
                 and(u_g_users[:user_id].eq(id)))

    # is there a way to write this without going to_sql and back?
    Project.find_by_sql(arel.to_sql)
  end

What i'm trying to do is change the last line of my new projects_admin method so that it calls the relation directly, without using find_by_sql on arel.to_sql.

If i just return the arel, it's not an array of Projects as I would expect.

But maybe this is a nonsensical desire, and it has to be phrased this way. Many examples suggest this is the case.

I've tried every example I could find, but I think the root of the problem is my misunderstanding of how a join interacts with the class of object i'm generating, an Arel::SelectManager.

Here are the model associations in question. "user_groups_users" is a join table between "user_groups" and "users" - there are ony the "User", "UserGroup" and "Project" models here.

class User < AbstractModel
  has_many :projects_created, class_name: "Project"
  has_and_belongs_to_many :user_groups,
                          class_name: "UserGroup",
                          join_table: "user_groups_users"

class UserGroup < AbstractModel
  has_and_belongs_to_many :users
  has_one :project
  has_one :admin_project, class_name: "Project", foreign_key: "admin_group_id"

class Project < AbstractModel
  belongs_to :admin_group, class_name: "UserGroup",
                           foreign_key: "admin_group_id"
  belongs_to :user
  belongs_to :user_group

Answer 1

I belive that you're overlooking a much simpler solution which is to add an indirect assocation and do a LEFT JOIN:

class Project < AbstractModel
  belongs_to :admin_group, class_name: "UserGroup",
                           foreign_key: "admin_group_id"
  has_many :admin_group_users, 
    through: :admin_group,
    source: :users # the name of the assocation on UserGroup
  # ...
end

Project.joins(:admin_group_users)
       .where(users: { id: id })