Laravel Query Join through Relations

LaravelQueryJoinRelations.php

<?php

/**
 * User: matteo.orefice
 * Date: 16/02/2018
 * Time: 16:57
 */

namespace MatteoOrefice\Illuminate\Database\Eloquent\Concerns;

use Illuminate\Support\Str;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasOneOrMany;
use Illuminate\Database\Query\JoinClause;
use Illuminate\Support\Facades\DB;

/**
 * \App\MyModel::query()->select('S.*')->withJoin('relationShip1','S');
 * \App\MyModel::query()->select('P.*')->withJoin(['relationShip1','relationShip2'],'P');
 * \App\MyModel::query()->select('P.*','S.*')->withJoin(['relationShip1','relationShip2'],['P','S']);
 * \App\MyModel::query()->from(\App\MyModel::query()->getModel()->getTable().' as U')->select('U.*','S.*')->withJoin(['relationShip1','relationShip2'],['P','S']);
 * \App\MyModel::query()->from(\App\MyModel::query()->getModel()->getTable().' as U')->select('U.*','S.*')->withJoin('relationShip1.relationShip2',['P','S']);
 * \App\MyModel::query()->select('P.*','S.*')->withSelect('relationShip1.'relationShip2',['alias'=>'farcolumn']);
 * \App\MyModel::query()->select('P.*','S.*')->withSelect('relationShip1.'relationShip2',['alias'=>DB::Expr('farcolumn_expression')]);
 * \App\MyModel::query()->select('P.*','S.*')->withSelect('relationShip1.'relationShip2',[DB::Expr('farcolumn_expression')]);
 **/
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 scopeWithJoin(Builder $builder, $relationSegments, $rightAlias=null, $decorators=null, $join='join', $operator='=')
    {
        if(is_string($decorators)) {
            if($join === 'join') {
                $operator = '=';
            } else {
                $operator = $join;
            }
            $join = $decorators;
            $decorators = null;
        }
        else if($decorators) {
            $decorators = array_wrap($decorators);
        }

        // recupera il nome della tabella con eventuale alias dal from oppure dal nome della table corrispondente nel model
        $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];
        }

        $this->getJoinRelationShipSubQuery($this,$builder,$relationSegments,$previousTableAlias,$rightAlias,false,$decorators,$join,$operator);

        return $builder;
    }

    public function scopeWithSelect(Builder $builder, $relationSegments, $columns, $rightAlias=null, $decorators=null, $join='join', $operator='=')
    {
        if(is_string($decorators)) {
            if($join === 'join') {
                $operator = '=';
            } else {
                $operator = $join;
            }
            $join = $decorators;
            $decorators = null;
        }
        else if($decorators) {
            $decorators = array_wrap($decorators);
        }

        // recupera il nome della tabella con eventuale alias dal from oppure dal nome della table corrispondente nel model
        $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];
        }

        $this->getJoinRelationShipSubQuery($this,$subQuery=DB::query(),$relationSegments,$previousTableAlias,$rightAlias,true,$decorators,$join,$operator);

        foreach ($columns as $alias => $column) {
            $subQuery->addSelect($column);
            $builder->selectSub($subQuery , is_string($alias) ? $alias : $this->wrapColumnDefinition($column));
        }
        return $builder;
    }

    protected function wrapColumnDefinition($columnDefinition)
    {
        return urldecode(
            $this->getQuery()->getGrammar()->wrap(
                preg_replace_callback('/[^a-zA-Z]+/',function($value) {
                    $value = $value[0];
                    $out = '';
                    for ($i = 0; isset($value[$i]); $i++) {
                        $c = $value[$i];
                        if (!ctype_alnum($c)) $c = '%' . sprintf('%02X', ord($c));
                        $out .= $c;
                    }
                    return $out;
                },$columnDefinition)
            )
        );
    }


    /**
     * A partire da un model crea join multiple sfruttando le relazioni
     *
     *
     *
     * @param Model $model
     * @param $relationSegments
     * @param null $builder
     * @param null $previousTableAlias
     * @return null
     * @throws \Exception
     */
    protected function getJoinRelationShipSubQuery(
        Model $model,
        $builder,
        $relationSegments,
        $previousTableAlias=null,
        $rightAlias=null,
        $sub=false,
        $decorators=null,
        $join='join',
        $operator='=') {
        
        $currentModel = $model;
        $relatedModel = null;
        $relatedTableAlias = null;
        $tableAliases = [];
        $relatedTableAndAlias = null;
        $relationSegments = array_wrap($relationSegments);
        if(count($relationSegments)==1 && Str::contains($relationSegments[0],'.')) {
            $relationSegments = preg_split('/\./',$relationSegments[0]);
        }
        if(($relationIndex=count($relationSegments))==0) {
            throw new \Exception('Relation path cannot be empty');
        }
        /**
         * Il prefisso per le tabelle unite in JOIN viene generato a caso se non fornito
         */
        $randomPrefix = 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.");
            }
            $decorator = $this->getDecorator($decorators,$relationIndex);
            $relation = $currentModel->$segment();
            $relatedModel = $relation->getRelated();
            $relatedTableAlias = $this->makeTableAlias($randomPrefix,$rightAlias,$relationIndex);
            if(!is_null($relatedTableAlias)) {
                $tableAlias =  ' AS ' . $relatedTableAlias;
                $relatedTableAndAlias = $relatedModel->getTable() . $tableAlias;
            }
            else {
                $relatedTableAndAlias = $relatedTableAlias = $relatedModel->getTable();
            }
            $tableAliases []= $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) {

                if($sub) {
                    if(!$previousTableAlias) {
                        throw new \RuntimeException('$previousTableAlias is required for sub');
                    }
                    $sub = false;
                    $builder
                        ->from($relatedTableAndAlias)
                        ->whereColumn(
                            $previousTableAlias.'.'. $relation->getForeignKey(),
                            $operator ,
                            $relatedTableAlias . '.' . $relation->getOwnerKey()
                        );
                } else {
                    $builder
                        ->$join(
                            $relatedTableAndAlias,
                            function (JoinClause $joinClause) use($decorator,$previousTableAlias,$relation,$operator,$relatedTableAlias) {
                                $joinClause->on(
                                    $previousTableAlias ? $previousTableAlias.'.'. $relation->getForeignKey() : $relation->getQualifiedForeignKey(),
                                    $operator ,
                                    $relatedTableAlias . '.' . $relation->getOwnerKey()
                                );
                                if($decorator instanceof  \Closure) {
                                    $decorator($joinClause);
                                }
                            }
                        );
                }
            }
            // 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) {
                if($sub) {
                    if(!$previousTableAlias) {
                        throw new \RuntimeException('$previousTableAlias is required for sub');
                    }
                    $sub = false;
                    $builder
                        ->from($relatedTableAndAlias)
                        ->whereColumn(
                            $previousTableAlias.'.'. $relation->getParent()->getKeyName(),
                            $operator ,
                            $relatedTableAlias . '.' . $relation->getForeignKeyName()
                        );
                } else {
                    $builder
                        ->$join(
                            $relatedTableAndAlias,
                            function(JoinClause $joinClause) use($decorator,$previousTableAlias,$relation,$operator,$relatedTableAlias)  {
                                $joinClause->on(
                                    $previousTableAlias ? $previousTableAlias.'.'. $relation->getParent()->getKeyName() : $relation->getQualifiedParentKeyName(),
                                    $operator ,
                                    $relatedTableAlias . '.' . $relation->getForeignKeyName()
                                );
                                if($decorator instanceof  \Closure) {
                                    $decorator($joinClause);
                                }
                            }
                        );
                }
            } // 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 $tableAliases;
    }

    public function scopeWithJoinLeft(Builder $builder, $relationSegments, $rightAlias=null, $decorators=null, $operator='=') {
        return $this->scopeWithJoin($builder,$relationSegments,$rightAlias,$decorators,'leftJoin',$operator);
    }

    /**
     * Crea un alias con un prefisso numerico oppure lo recupera da un array
     *
     * Se indice non e' presente prende elemento piu prossimo
     *
     * @param $string $prefix
     * @param string|array $alias
     * @return array|mixed|null|string
     */
    public function makeTableAlias($prefix,$alias,$index)
    {
        $index -=1 ;
        if(is_array($alias)) {
            if(isset($alias[$index]))
                return $alias[$index];
            if($index>0)
                return array_last($alias).'_'.($index);
            return array_last($alias);

        }
        else if($index==0) {
            if(is_null($alias))
                return null;
            return $alias;
        }
        return $prefix.'_'.($index);
    }

    public function getDecorator($decorators,$index)
    {
        $index -=1 ;
        if(!is_array($decorators)) {
            return null;
        }
        if(isset($decorators[$index]))
            return $decorators[$index];
        return null;
    }
}

Does this support nested relations?

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.