joinRelation

[decadence]

As we know Eloquent uses separate query for relationships but sometimes we need JOIN relationship (for sorting by relationship column or for aggregate functions like sum, count etc.)
Then we have to use join method on Builder and we need to specify joining columns with hardcoded names:
User::join("posts", "user.id", "=", "posts.user_id")

It would be cool to have method joinRelation which could determine needed parameters automatically from relation definition:.

For example:

public function posts()
{
   return $this->hasMany(Posts::class, "user_id");
}

and then (just an example):

User::joinRelation("posts")->selectRaw("SUM('posts.views') as total_views")

without need for check column names.

Just ping @browner12 and @sileence in case they are interested in this and could implement this.

[decadence]

Existing solutions
https://github.com/tjbp/eloquent-joins
http://laravel-tricks.com/tricks/automatic-join-on-eloquent-models-with-relations-setup

[decadence]

Another one:
https://github.com/jarektkaczyk/eloquence/wiki/Builder-searchable-and-more#joining-relations

[decadence]

@browner12 @sileence any thoughts guys? Looks like people are interested in this feature.

[browner12]

i was actually just thinking about this yesterday as I was looking into some query optimization stuff. I would love to utilize Join queries in our relationships, to decrease the number of queries we actually make.

Let's take an example. A City belongs to a State belongs to a Country. If I select a City:

$city = City::first();

and then try to output it's full name

echo $city->name . ', ' . $city->state->name . ' ' . $city->state->country->name;

We end up making a query for each relationship.

It would be great if these belongsTo relationships could use JOINS when they are eager loaded.

SELECT *
FROM cities as c
JOIN states as s
ON c.state_id = s.id
JOIN country as co
ON s.country_id = co.id

and then auto-populate these into related models on the base class.

I'm not sure that I have the bandwidth for this right now given my workload but I will continue to look into it.

[decadence]

Also we will can sort models by relationship field.
Thanks for your attention.

[sisve]

This would need both automatic aliasing of the columns to avoid conflicts between models that have identical column names (like id).

Your examples so far has been many-to-one/belongs-to relations. What about the original question, a one-to-many/has-many? This would need for a hydrator that understands that several rows returned does not mean that several root entities should be returned.

Assuming the original imagined code; User::join("posts", "user.id", "=", "posts.user_id") would return a result set where the user data is returned several times;

users.id	users.name	posts.id	posts.title
1	sisve	1	Zomg
1	sisve	2	Meep
2	lurker	null	null

I would expect the query to return two User models, where the posts relation is eager loaded. User 1 (sisve) should have a Collection with two Posts, User 2 (lurker) should have an empty Collection since he has never written anything.

[sileence]

Hello, there are a few things to consider here:

• First of all 2 simple SELECT queries can actually be faster than 1 SELECT query with a JOIN. So let's not see this feature as a performance optimisation because it might or might not be.

• Second: in order to do this, as @sisve just pointed out, we need to specify the columns and their aliases like:

posts.title AS posts___title, categories.title AS categories___title in order to get unique column names and be able to split the results in a multidimensional array, for example: posts['categories']['title']

With that information it will be possible to hydrate the models if we cycle through the relationships and we process them similar to what I've been working on here: laravel/framework#19793

BUT the problem is that the current version of Eloquent does not specify the column names, and therefore there is no way to build the SELECT part of the query unless we get the column names from the database (and we cache them somewhere).

I hope this helps.

[decadence]

@sileence Of course this proposal is not about optimisation but about Laravel users convenience.

Initially I just want wrapper around join for relationships without hydrating (and automatic column aliasing) because now join works right this way if you don't use groupBy.

Later I saw existing solutions grouped rows by parent model and I think it will be superflexible if we can control both situations.

[brynj-digital]

This would need both automatic aliasing of the columns to avoid conflicts between models that have identical column names (like id).

@sisve I'm not sure if this is in the pipeline for next Laravel release, but I'd like to see the default select behaviour when joining to Eloquent models be changed. This is obviously a breaking change.

What I think makes sense is that only the model's fields should be selected - if you need to retrieve data from the joined tables, either use addSelect or rewrite select. Quite frequently, joins are only used as a means to perhaps filter the model records returned - no actual data is needed from these tables.

The current behaviour is IMHO both confusing and dangerous - leading to things like identifiers being overwritten. Of course, if the relationship is one to many, multiple model instances will be created - however, that's up to the dev to know their data structure.

I'd be happy to raise this as another issue, if this isn't in the pipeline.

[sisve]

Quite frequently, joins are only used as a means to perhaps filter the model records returned - no actual data is needed from these tables.

The proper api for filtering is User::has('posts')->get() (or whereHas). There's also a doesntHave/whereDoesntHave. This is documented at https://laravel.com/docs/5.5/eloquent-relationships#querying-relationship-existence.

We wouldn't need to worry about identifiers being overwritten if we're automatically aliasing all model attributes.

[akalongman]

+1

[Inkognitoo]

I really want this feature!
I was very surprised when I found out that laravel cannot join models by relation like yii2, it's make me upset

[sisve]

@Inkognitoo Could you tell us which specific feature you want, and how yii2 implemented that feature? Everything I find under http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#joining-with-relations matches what Laravel already supports.

I see nothing in their docs about any support for populating relationships via joins, or automatic aliasing of columns.

Maybe you're just easily upset?

[mfn]

CakePHP2 supports this too and, yes I'm guilty, I found it useful when I needed it. More information can be found here: https://book.cakephp.org/2.0/en/models/retrieving-your-data.html

When using its ORM, it always aliases the columns you retrieve and thus when it combines them with other queries, there won't be a clash. But you can run into other problems, like the column name aliases being to long and getting cut off (Problem with postgres).

The thing is:

• I found it useful in CakePHP2 when needed
• Most of time I didn't need it
• I was glad Laravel didn't do the magic
• The few times I needed something similar in Laravel, I had to do a bit extra work

The outcome for me was fine enough not pursuing any kind of solution do that.

[Inkognitoo]

@sisve I'm not sure I understand you right, but...

For example: I need to sort table by sum of aggregate row another table.
Say I have User and he has Books with rating. Virtual rating of user is sum of rating his Books.
If want sort Users by they rating so I need use join. But I also have softdelted Books, scopes etc...
It's very unconvinient to write this all by hands but not automatic by withJoin()

In Yii2 if I want alias for join table, I write something as $query->joinWith('books b') it create b alias for books table.

[brynj-digital]

@Inkognitoo - one of my real world examples was similar... I wanted to order the results of a collection by a value in a joined table, so has() or whereHas() aren't helpful here.

[MatteoOreficeIT]

Hi Guys

I wrote a gist with an useful Trait about query join through relations

https://gist.github.com/MatteoOreficeIT/d3f66e90436dd5b9c90fbe144118e667

Example

public function scopeJoinWith(Builder $builder,$relationSegments,$rightAlias=null,$operator='=',$join='join')
// some usage examples
\App\Terminali::query()->select('S.*')->joinWith('settori','S');
\App\Terminali::query()->select('P.*')->joinWith(['settori','prodotto'],'P');
\App\Terminali::query()->select('P.*','S.*')->joinWith(['settori','prodotto'],['P','S']);
\App\Terminali::query()->from(\App\User::query()->getModel()->getTable().' as U')->select('U.*','S.*')->joinWith(['settori','prodotto'],['P','S']);

It works on BelongsTo and HasOneOrMany

I hope it could be integrated in the future

[jpleau]

What was the reason for closing this? Is it implemented somewhere?

[decadence]

@jpleau no

[iwasherefirst2]

Too bad. This would have been really useful.

[fico7489]

I created a package that solves above problems : https://github.com/fico7489/laravel-eloquent-join