Load relations using joins instead multiple queries

[btxtiger]

This example executes 4 queries, whereas it could only be 1 query if the tables were combined using joins.

$query = Product::with('primaryCategory', 'dataOrigin', 'manufacturer')->whereId(1)->get();

In this common case, the loaded relations do not load any one-to-many relations. So in SQL all tables would be easily joinable using prefixes and building the collections afterwards.
Why was this data aggregation implemented differently? At some point, there would be noticable performance improvements by reducing the 4 queries to 1 query. And it takes much less effort, to create responses that include just specific columns from relations. E.g.

$query = Product::select([
   'products.id AS productId',
   'products.name AS productName',
   'manufacturer.name AS manufacturerName',
   'dataOrigin.name AS dataOriginName',
   'primaryCategory.path AS categoryPath',

   // Relation ids are required to execute queries 2-4, could be dropped when using joins
   'products.manufacturer_id',
   'products.data_origin_id',
   'products.primary_category_id',

])->with('primaryCategory', 'dataOrigin', 'oemManufacturer');

[Sophist-UK]

Personally I have no idea why Eloquent does not use joins - but most Laravel design decision have been made consciously and with good reasons, so unless there is evidence to the contrary I assume that it is going to be the case with this decision.

My guess is that it does this so that data is loaded as models. So User->with('Posts')->get() loads some User models first, and then loads some Post models, using the Posts method in the User model class.

Of course, you could still do this as a single joined SQL query and then split out the results into appropriate models, but this is not the option chosen by Laravel.

There are, however, several Laravel extension packages that aim to use Joins instead. A quick search came up with the following:

• https://github.com/kirschbaum-development/eloquent-power-joins
• https://github.com/fico7489/laravel-eloquent-join
• https://github.com/tylernathanreed/laravel-relation-joins
• https://github.com/topclaudy/compoships with https://github.com/kitloong/eloquent-power-joins-with-compoships
• https://github.com/railken/eloquent-mapper

Disclaimers: I have not used any of the above packages and have no opinions on their absolute or relative merits. But as usual, you should consider what their long-term future is before using any of them.

[indykoning]

I think one of the large reasons behind this which i have personally profited from is the fact that this means you can have relations across different databases and even different database engines!
Like table A on Mysql having a relationship to table B on Postgres.

However i would like it if Laravel acts smart about this and uses subselects when the database connection is the same to reduce response time and database load.

[btxtiger]

This also works with joins in MySQL but not in PgSQL. If you prefix the MySQL table with the database name, you can join different databases together. However this is a very rear usecase and often goes along with heavy performance losses.

I agree being smart and aware of capabilities of different DB types would be essential.

[jasondeewright]

I'm a bit late here, but I have found that separating the queries gives more reliable performance for larger datasets. If you are pulling 3 nested objects all with a single query and no group-by, you're going to introduce network throughput issues by duplicating the amount of data you're returning.

Separating the queries might be marginally slower for small datasets, but it's much more resistant to n+1 issues. Consider pulling posts, comments, and comment likes all in one query. Since post_content is at the top level, you may have thousands of instances of it duplicated. That adds up quick. You could be pulling MB or GB of textual data.

[macropay-solutions]

We solved the issue of returning only the needed columns from resource and its relations in a decorator library.

This question seems more like a raw sql type of approach vs the builder approach.

Of course it can be replicated via builder, but after our experience of building an API query language and translating it into sql using the builder (over a period of 4 years), we are in a position to say that it would be more complicated to use joins vs separate queries to load the relations.

Also the join will not avoid the N+1 issue in the sense that the same related data will be fetched from DB for N rows instead of it being fetched once and then just duplicated in the response.

Also the joins will be slower because they would introduce the index selection problem.