Multiple Joins

[daninus14]

I would like to be able to do multiple joins with mito. Right now, includes only allows for one join, but for a regular m-n relationship, where we have tables A, A_TO_B, and B, I want to be able to include table B as well.

In SQL this would be just

SELECT *
FROM A
LEFT JOIN A_TO_B
ON A.ID = A_TO_B.A_ID
LEFT JOIN B
ON A_TO_B.B_ID = B.ID;

Right now, unless I misunderstood something, the includes only allows for one join. I think there should be a way to simply have another includes, within some expression to provide the context of which class is including which other class, to allow for the second join

[fukamachi]

The includes is intended to prevent the N+1 query problem, not for JOIN.

If you'd like to join multiple tables, build an SxQL query and pass it to select-by-sql.

[daninus14]

@fukamachi Eitaro, first: thank you so much for your open source contributions! Second: thank you for your reply!

Now, onto our issue, two things:

First, this is also an N+1 issue, for example say there are the following tables Book, BookOwnership, and Person, where BookOwnership indicates who owns a certain book, and many people can own a copy of the same Book.

That would require another N+1 issue, searching for Books, then getting the owners, then getting the owners name. Without includes it's (N+1)*(N+1), with includes we are just left with an N+1.

So this is really a case of a repeat eager load, which is the normal eager load for every M-N relationship, which is an extremely common case.

Second, in the docs for eager-loading https://github.com/fukamachi/mito#eager-loading I noticed that the queries are:

;-> ;; SELECT * FROM `tweet` WHERE (`status` LIKE ?) ("%Japan%") [3 row] | MITO.DB:RETRIEVE-BY-SQL
;-> ;; SELECT * FROM `user` WHERE (`id` IN (?, ?, ?)) (1, 3, 12) [3 row] | MITO.DB:RETRIEVE-BY-SQL

But really this is inefficient since it's causing two queries, it should really be handled as a LEFT JOIN to be one query instead of two and it should be:

SELECT * FROM `tweet` WHERE (`status` LIKE ?) ("%Japan%") LEFT JOIN `user` ON `user`.ID = `tweet`.ID;

I just realized this, so just to make it easier to keep track I'm going to go ahead an create another issue for this second comment.

Thanks for the suggestion of using select-by-sql, yes, that is currently what I'm doing, but I was hoping to make it into the main code since it's a very common multiple join case, to have eager loading twice, which is the definition of an eager load for every M-N relationship.