Last active
October 14, 2023 08:45
-
-
Save MatteoOreficeIT/d3f66e90436dd5b9c90fbe144118e667 to your computer and use it in GitHub Desktop.
Laravel Query Join through Relations
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| /** | |
| * User: matteo.orefice | |
| * Date: 16/02/2018 | |
| * Time: 16:57 | |
| */ | |
| namespace MatteoOrefice\Illuminate\Database\Eloquent\Concerns; | |
| use Bitia\Illuminate\Support\Str; | |
| use Illuminate\Database\Eloquent\Builder; | |
| use Illuminate\Database\Eloquent\Relations\BelongsTo; | |
| use Illuminate\Database\Eloquent\Relations\HasOneOrMany; | |
| trait JoinRelationShip | |
| { | |
| /** | |
| * @param Builder $builder | |
| * @param $relationSegments | |
| * @param string|string[]|null $rightAlias se non fornito si genera a caso e vendono appesi progressivi | |
| * se fornito come stringa diventa alias di quella piu a dx e le precedenti avranno un suffisso numerico N+1 | |
| * se fornito come array, elemento zero viene usato per la relazione piu lontana e cosi via | |
| * @param string $operator | |
| * @return $this | |
| * @throws \Exception | |
| */ | |
| public function scopeJoinWith(Builder $builder,$relationSegments,$rightAlias=null,$operator='=',$join='join') | |
| { | |
| $currentModel = $this; | |
| $aliasSegments = preg_split('/\s+/i', $previousTableAlias = $builder->getQuery()->from ?: $builder->getModel()->getTable()); | |
| // il terzo conterrebbe l'alias | |
| if(is_array($aliasSegments) && isset($aliasSegments[2])) { | |
| $previousTableAlias = $aliasSegments[2]; | |
| } | |
| $relatedModel = null; | |
| $relatedTableAlias = null; | |
| $relatedTableAndAlias = null; | |
| $relationSegments = array_wrap($relationSegments); | |
| if(($relationIndex=count($relationSegments)-1)<0) { | |
| throw new \Exception('Relation path cannot be empty'); | |
| } | |
| /** | |
| * Il prefisso per le tabelle unite in JOIN viene generato a caso se non fornito | |
| */ | |
| $rightAlias = $rightAlias ?: Str::randomStringAlpha(3); | |
| /** | |
| * Per ogni segmento aggiungo una join | |
| */ | |
| foreach ($relationSegments as $segment) { | |
| if (!method_exists($currentModel,$segment)) { | |
| throw new BadMethodCallException("Relationship $segment does not exist, cannot join."); | |
| } | |
| $relation = $currentModel->$segment(); | |
| $relatedModel = $relation->getRelated(); | |
| $relatedTableAlias = $this->makeTableAlias($rightAlias,$relationIndex); | |
| $relatedTableAndAlias = $relatedModel->getTable() . ' AS ' . $relatedTableAlias; | |
| /** | |
| * Nelle BelongsTo definiamo : | |
| * - CHILD TABLE(SX) : quella dal lato con cardinalita N | |
| * - FOREIGN KEY : la colonna chiave esterna sulla tabella CHILD ovvero il lato con cardinalita (N) | |
| * - PARENT TABLE(DX) : quella dal lato con cardinalita 1 | |
| * - OWNER KEY : la colonna chiave sulla tabella PARENT ovvero il lato con cardinalita (1) | |
| */ | |
| if ($relation instanceof BelongsTo) { | |
| $builder | |
| ->$join( | |
| $relatedTableAndAlias, | |
| $previousTableAlias ? $previousTableAlias.'.'. $relation->getForeignKey() : $relation->getQualifiedForeignKey(), | |
| $operator , | |
| $relatedTableAlias . '.' . $relation->getOwnerKey() | |
| ); | |
| } // endif | |
| /** | |
| * Nelle HasOneOrMany definiamo : | |
| * - PARENT TABLE(SX) : quella dal lato con cardinalita 1 | |
| * - CHILD TABLE(DX) : quella dal lato con cardinalita N | |
| */ | |
| elseif ($relation instanceof HasOneOrMany) { | |
| $builder | |
| ->$join( | |
| $relatedTableAndAlias, | |
| $previousTableAlias ? $previousTableAlias.'.'. $relation->getParentKey() : $relation->getQualifiedParentKeyName(), | |
| $operator , | |
| $relatedTableAlias . '.' . $relation->getForeignKeyName() | |
| ); | |
| } // endif | |
| else { | |
| throw new \InvalidArgumentException( | |
| sprintf("Relation $segment of type %s is not supported" , get_class($relation)) | |
| ); | |
| } // else | |
| /** | |
| * Avanza i puntatori | |
| */ | |
| $currentModel = $relatedModel; | |
| $previousTableAlias = $relatedTableAlias; | |
| $relationIndex--; | |
| } // end foreach <RELATIONs> | |
| return $builder; | |
| } | |
| /** | |
| * Crea un alias con un prefisso numerico oppure lo recupera da un array | |
| * | |
| * Se indice non e' presente prende elemento piu prossimo | |
| * | |
| * @param string|array $alias | |
| * @param $string | |
| */ | |
| public function makeTableAlias($alias,$index) | |
| { | |
| if(is_array($alias)) { | |
| if(isset($alias[$index])) | |
| return $alias[$index]; | |
| return array_last($alias).'_'.($index); | |
| } | |
| if($index==0) | |
| return $alias; | |
| return $alias.'_'.($index); | |
| } | |
| } |
Hi @koxu1996 , if you mean fetching nested/consecutive relations from a model through its and other model relations the answer is YES
For example if we have :
class A extends Model {
public function relatesB() { return $this->belongsTo( B::class ); }
}
class B extends Model {
public function relatesC() { return $this->belongsTo( C::class ); }
}
class C extends Model {
public function relatesD() { return $this->belongsTo( D::class ); }
}
class C extends Model { }you can do this :
\App\A::query()
// include every available aliases from different join clause
->select('A.*','B.*','C.*','D.*')
->joinWith(
// specify relations join order/path for consecutive / nested relations
['relatesB','relatesC','relatesD'],
// place an alias for every related model, remember in reverse order
// you can also use only ['D'] and so the other aliases will be generated appendind a number for example D_1,D_2, ..., D_N
['D','C','B','A']
);Keep in mind that to simulate the eager loading behaviour you can place in the select clause many alias containing in the name a dot , for example :
$query->select('A.field1 as relationB.field1') /// you will find in result a key [ 'relationB.field1' => 'valueOfField1OnModelB' ]and after you can group these values with a code like the following :
$relationValues = [];
collect($model->getAttributes())->each(function($value,$name)use(&$relationValues,$model){
if(Str::contains($name,'.')) {
Arr::set($relationValues,$name,$value);
unset($model[$name]);
}
});
collect($relationValues)->each(function($value, $name)use(&$model){
$model[$name] = $value;
});The resulting model will have the related model fields grouped by relation name of your choice.
UPDATE: now you can also use withSelect()
Thanks for the snippet. Just small things I found while inspecting:
->joinWith(
// specify relations join order/path for consecutive / nested relations
['relatesB','relatesC','relatesD'],
// place an alias for every related model, remember in reverse order
// you can also use only ['D'] and so the other aliases will be generated appendind a number for example D_1,D_2, ..., D_N
['D','C','B','A']
);
should be
->withJoin(...
and $randomPrefix = Str::randomStringAlpha(3); is not available, it's just Str::random(3)
https://laravel.com/api/5.6/Illuminate/Support/Str.html#method_random
Thanks @mludi , it was a custom function we added.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Does this support nested relations?