CODEWITHSUNDEEP
ruby-on-railsrubyactiverecordjoin
Stefan Kühn
Stefan Kühn

Reputation: 349

ActiveRecord multiple join?

I’m struggling with an Active Record join problem.

I have this database table structure:

A - B - C - D - E

and

A - F - E

I would like to get all the records from A if either of these paths:

A - B - C - D - E
A - F - E

are satisfied.

I tried this with Active Record:

A.joins(:B => {:C => {:D => :E}}, :F => :E)
.where("some constraints on E and …")

This, however doesn't work, as all are generated as inner joins like this:

SELECT  A.*
FROM A
INNER JOIN B ON A.b_id = B.id
INNER JOIN C ON B.c_id = C.id
INNER JOIN D ON C.d_id = D.id
INNER JOIN E ON D.e_id = E.id
INNER JOIN F ON A.f_id = F.id
INNER JOIN E E_F ON E_F.f_id = F.id
WHERE ("some constraints on E")

I hope I didn’t mis-type something here, but the core is, in the generated SQL statement both paths are found in the ON clauses.

The problem is that both paths have to be satisfied to return records from Table A, right?

I need a statement that returns records from A for both paths. In the end they are made unique with uniq().

I also have tried to merge() two separate selects/joins (path A - B - C - D - E" and "A - F - E") butmerge()does an intersection. The only solution that I have come up with, is to+` two separate result sets, but the problem is that I get an array as a result and hence I cannot use (kaminari) pagination. Second, that is not a good solution when the result set is getting large.

I’d appreciate any hints. Thanks!

Upvotes: 0

Views: 671

Answers (1)

David Aldridge
David Aldridge

Reputation: 52336

I have a feeling that you might end up with a skillfully hand-crafted join here:

SELECT A.*
FROM A
LEFT JOIN (B ON A.b_id = B.id
  INNER JOIN C ON B.c_id = C.id
  INNER JOIN D ON C.d_id = D.id
  INNER JOIN E ON D.e_id = E.id)
LEFT JOIN (F ON A.f_id = F.id
  INNER JOIN E E_F ON E_F.f_id = F.id)
WHERE E_F.id is not null or E.id is not null and
    („some constraints on E“)

Best to encapsulate it in a scope, of course.

You can get into trouble with your join via F because it appears to be the parent of both A and E -- if F has_many E then you will get multiple rows back for A.

You'd fix this by using a correlated subquery (a semi-join):

SELECT A.*
FROM A
LEFT JOIN (B ON A.b_id = B.id
  INNER JOIN C ON B.c_id = C.id
  INNER JOIN D ON C.d_id = D.id
  INNER JOIN E ON D.e_id = E.id)
WHERE E.id is not null and
EXISTS (SELECT NULL FROM E E_F WHERE E_F.F.id = A.F_ID)
    („some constraints on E“)

Notice that F can be eliminated from the query because F_ID is present in both A and E.

For tidiness sake you might make both joins semi-joins ...

SELECT A.*
FROM A
WHERE (EXISTS (SELECT NULL FROM E E_F WHERE E_F.F.id = A.F_ID) OR
       EXISTS (SELECT NULL FROM B 
               INNER JOIN C ON B.c_id = C.id
               INNER JOIN D ON C.d_id = D.id
               INNER JOIN E ON D.e_id = E.id
               WHERE A.b_id = B.id))
    („some constraints on E“)

Pop it into a scope ...

scope :relates_to_e, -> {where("(EXISTS (SELECT NULL
                                            FROM E E_F
                                           WHERE E_F.F.id = A.F_ID) OR
                                  EXISTS (SELECT NULL
                                          FROM B 
                                          INNER JOIN C ON B.c_id = C.id
                                          INNER JOIN D ON C.d_id = D.id
                                          INNER JOIN E ON D.e_id = E.id
                                          WHERE A.b_id = B.id))"}

It's not pretty, but it will work efficiently and keep the untidiness in a scope.

A.relates_to_e.where(„some constraints on E and …“)

Upvotes: 2

Related Questions