CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
mannykary
mannykary

Reputation: 832

ActiveRecord query on many-to-many self join table

I have a many-to-many self join table called people that uses the following model:

class Person < ApplicationRecord
  has_and_belongs_to_many :children,
    class_name: "Person",
    join_table: "children_parents",
    foreign_key: "parent_id",
    association_foreign_key: "child_id",
    optional: true

  has_and_belongs_to_many :parents,
    class_name: "Person",
    join_table: "children_parents",
    foreign_key: "child_id",
    association_foreign_key: "parent_id",
    optional: true
end

If it isn't apparent in the above model - in addition to the people table in the database, there is also a children_parents join table with two foreign key index fields child_id and parent_id. This allows us to represent the many-to-many relationship between children and parents.

I want to query for siblings of a person, so I added the following method to the Person model:

def siblings
  self.parents.map do |parent|
    parent.children.reject { |child| child.id == self.id }
  end.flatten.uniq
end

However, this makes three SQL queries:

  Person Load (1.0ms)  SELECT "people".* FROM "people" INNER JOIN "children_parents" ON "people"."id" = "children_parents"."parent_id" WHERE "children_parents"."child_id" = $1  [["child_id", 3]]
  Person Load (0.4ms)  SELECT "people".* FROM "people" INNER JOIN "children_parents" ON "people"."id" = "children_parents"."child_id" WHERE "children_parents"."parent_id" = $1  [["parent_id", 1]]
  Person Load (0.4ms)  SELECT "people".* FROM "people" INNER JOIN "children_parents" ON "people"."id" = "children_parents"."child_id" WHERE "children_parents"."parent_id" = $1  [["parent_id", 2]]

I know that it is possible to make this a single SQL query like so:

SELECT DISTINCT(p.*) FROM people p
INNER JOIN children_parents cp ON p.id = cp.child_id
WHERE cp.parent_id IN ($1, $2)
AND cp.child_id != $3

$1 and $2 are the parent ids of the person, and $3 is the person id.

Is there a way to do this query using ActiveRecord?

Upvotes: 1

Views: 400

Answers (1)

Roman Kushin
Roman Kushin

Reputation: 65

You can use something like this:

def siblings
  Person.select('siblings.*').from('people AS siblings').where.not(id: id)
    .where(
      parents.joins(
        'JOIN children_parents ON parent_id = people.id AND child_id = siblings.id'
      ).exists
    )
end

Here you can see few strange things:

from to set table alias. And you should avoid this, because after such table aliasing active record will not help any more with column names from ruby: where(column: value).order(:column) - will not work, only plain sql strings are left

exists - I use it very often instead of joins. When you are joining many records to one, you are receiving duplicates, then comes distinct or group and new problems with them. Exists also gives isolation of query: table and columns in EXISTS expression are invisible for other parts of query. Bad part of using it in rails: at least 1 plain SQL is needed.

One weakness of this method: if you will call it for each record somewhere, then you will have 1 query for each record - N+1 problem.

Now few words about The Rails Way. Rails guide suggests to always use has_many :through instead of habtm, I seen it here: https://github.com/rubocop-hq/rails-style-guide.

Rails ideology as I understood it stands for speed of development and simplicity of maintenance. First means that performance does not matter (just imagine how many users you need to start have issues with it), second says that flexibility of plain SQL is good, but not in rails, in rails please make code as simple as possible (see rubocop defaults: 10 loc in method, 100 loc in class, and 4 complexity metrics always saying that your code is too complex). I mean, many real world rails projects are making queries with N+1, making ineffective queries, and this rarely becomes a problem

So, in such cases I would recommend to try includes, preload, eager_load.

Upvotes: 1

Related Questions