RelatedPlusTrait::getTable()

Completed

Push — master ( c4bcc6...c033be )

by Michael

created 2018-02-06 05:51 UTC

↳ Parent: RelatedPlusTrait

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	1
c	0
b	0
f	0
nc	1

<?php

namespace Blasttech\EloquentRelatedPlus;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Relations\HasOneOrMany;
use Illuminate\Database\Eloquent\Relations\Relation;
use Illuminate\Database\Query\Expression;
use Illuminate\Database\Query\JoinClause;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use InvalidArgumentException;

/**
 * Trait RelatedPlusTrait
 *
 * @property array order_fields
 * @property array order_defaults
 * @property array order_relations
 * @property array order_with
 * @property array search_fields
 * @property string connection
 */
trait RelatedPlusTrait
{
    use CustomOrderTrait, HelperMethodTrait, SearchTrait;

    /**
     * Add orderBy if orders exist for a relation
     *
     * @param Builder|JoinClause $builder
     * @param Relation|BelongsTo|HasOneOrMany $relation
     * @param string $table
     * @return Builder|JoinClause $builder
     */
    protected function addOrder($builder, $relation, $table)
    {
        /** @var Model $builder */
        if (!empty($relation->toBase()->orders)) {
            // Get where clauses from the relationship
            foreach ($relation->toBase()->orders as $order) {
                $builder->orderBy($this->columnWithTableName($table, $order['column']), $order['direction']);
            }
        }

        return $builder;
    }

    /**
     * Add wheres if they exist for a relation
     *
     * @param Builder|JoinClause $builder
     * @param Relation|BelongsTo|HasOneOrMany $relation
     * @param string $table
     * @return Builder|JoinClause $builder
     */
    protected function addWhereConstraints($builder, $relation, $table)
    {
        // Get where clauses from the relationship
        $wheres = collect($relation->toBase()->wheres)
            ->where('type', 'Basic')
            ->map(function ($where) use ($table) {
                // Add table name to column if it is absent
                return [$this->columnWithTableName($table, $where['column']), $where['operator'], $where['value']];
            })->toArray();

        if (!empty($wheres)) {
            $builder->where($wheres);
        }

        return $builder;
    }

    /**
     * Boot method for trait
     *
     */
    public static function bootRelatedPlusTrait()
    {
        static::saving(function ($model) {
            if (!empty($model->nullable)) {
                foreach ($model->attributes as $key => $value) {
                    if (isset($model->nullable[$key])) {
                        $model->{$key} = empty(trim($value)) ? null : $value;
                    }
                }
            }
        });
    }

    /**
     * Get the table associated with the model.
     *
     * @return string
     */
    abstract public function getTable();

    /**
     * If the relation is one-to-many, just get the first related record
     *
     * @param JoinClause $joinClause
     * @param string $column
     * @param HasMany|Relation $relation
     * @param string $table
     * @param string $direction
     *
     * @return JoinClause
     */
    public function hasManyJoinWhere(JoinClause $joinClause, $column, $relation, $table, $direction)
    {
        return $joinClause->where(
            $column,
            function ($subQuery) use ($table, $direction, $relation, $column) {
                $subQuery = $this->joinOne(
                    $subQuery->from($table),
                    $relation,
                    $column,
                    $direction
                );

                // Add any where statements with the relationship
                $subQuery = $this->addWhereConstraints($subQuery, $relation, $table);

                // Add any order statements with the relationship
                return $this->addOrder($subQuery, $relation, $table);
            }
        );
    }

    /**
     * Get join sql for a HasOne relation
     *
     * @param Relation $relation
     * @param array $order
     * @return Expression
     */
    public function hasOneJoinSql($relation, $order)
    {
        // Build subquery for getting first/last record in related table
        $subQuery = $this
            ->joinOne(
                $relation->getRelated()->newQuery(),
                $relation,
                $order['column'],
                $order['direction']
            )
            ->setBindings($relation->getBindings());

        return DB::raw('(' . $this->toSqlWithBindings($subQuery) . ')');
    }

    /**
     * Adds a where for a relation's join columns and and min/max for a given column
     *
     * @param Builder $query
     * @param Relation $relation
     * @param string $column
     * @param string $direction
     * @return Builder
     */
    public function joinOne($query, $relation, $column, $direction)
    {
        // Get join fields
        $joinColumns = $this->getJoinColumns($relation);

        return $this->selectMinMax(
            $query->whereColumn($joinColumns->first, '=', $joinColumns->second),
            $column,
            $direction
        );
    }

    /**
     * Add joins for one or more relations
     * This determines the foreign key relations automatically to prevent the need to figure out the columns.
     * Usages:
     * $query->modelJoin('customers')
     * $query->modelJoin('customer.client')
     *
     * @param Builder $query
     * @param string $relationName
     * @param string $operator
     * @param string $type
     * @param bool $where
     * @param bool $relatedSelect
     * @param string|null $direction
     *
     * @return Builder
     */
    public function scopeModelJoin(
        Builder $query,
        $relationName,
        $operator = '=',
        $type = 'left',
        $where = false,
        $relatedSelect = true,
        $direction = null
    ) {
        foreach ($this->parseRelationNames($relationName) as $relation) {
            $table = $this->getRelationTables($relation);

            /** @var Model $query */
            if (empty($query->getQuery()->columns)) {
                $query->select($this->getTable() . ".*");
            }
            if ($relatedSelect) {
                $query = $this->selectRelated($query, $table);
            }
            $query->relationJoin($table, $relation, $operator, $type, $where, $direction);
        }

        return $query;
    }

    /**
     * Set the order of a model
     *
     * @param Builder $query
     * @param string $orderField
     * @param string $direction
     * @return Builder
     */
    public function scopeOrderByCustom(Builder $query, $orderField, $direction)
    {
        if ($this->hasOrderFieldsAndDefaults($orderField, $direction)) {
            $query = $this->removeGlobalScope($query, 'order');
        }

        return $query->setCustomOrder($orderField, $direction);
    }

    /**
     * Use a model method to add columns or joins if in the order options
     *
     * @param Builder $query
     * @param string $order
     * @return Builder
     */
    public function scopeOrderByWith(Builder $query, $order)
    {
        if (isset($this->order_with[$order])) {
            $with = 'with' . $this->order_with[$order];

            $query->$with();
        }

        if (isset($this->order_fields[$order])) {
            $orderOption = (explode('.', $this->order_fields[$order]))[0];

            if (isset($this->order_relations[$orderOption])) {
                $query->modelJoin(
                    $this->order_relations[$orderOption],
                    '=',
                    'left',
                    false,
                    false
                );
            }
        }

        return $query;
    }

    /**
     * Join a model
     *
     * @param Builder $query
     * @param \stdClass $table
     * @param Relation $relation
     * @param string $operator
     * @param string $type
     * @param boolean $where
     * @param null $direction
     * @return Builder
     */
    public function scopeRelationJoin(
        Builder $query,
        $table,
        $relation,
        $operator,
        $type,
        $where,
        $direction = null
    ) {
        if ($table->alias !== '' && $table->name !== $table->alias) {
            $fullTableName = $table->name . ' AS ' . $table->alias;
        } else {
            $fullTableName = $table->name;
        }

        return $query->join($fullTableName, function (JoinClause $join) use (
            $table,
            $relation,
            $operator,
            $direction
        ) {
            // If a HasOne relation and ordered - ie join to the latest/earliest
            if (class_basename($relation) === 'HasOne' && !empty($relation->toBase()->orders)) {
                return $this->hasOneJoin($relation, $join);
            } else {
                return $this->hasManyJoin($relation, $join, $table, $operator, $direction);
            }
        }, null, null, $type, $where);
    }

    /**
     * Add where statements for the model search fields
     *
     * @param Builder $query
     * @param string $searchText
     * @return Builder
     */
    public function scopeSearch(Builder $query, $searchText = '')
    {
        $searchText = trim($searchText);

        // If search is set
        if ($searchText != "") {
            if (!isset($this->search_fields) || !is_array($this->search_fields) || empty($this->search_fields)) {
                throw new InvalidArgumentException(get_class($this) . ' search properties not set correctly.');
            } else {
                $query = $this->checkSearchFields($query, $searchText);
            }
        }

        return $query;
    }

    /**
     * Switch a query to be a subquery of a model
     *
     * @param Builder $query
     * @param Builder $model
     * @return Builder
     */
    public function scopeSetSubquery(Builder $query, $model)
    {
        $sql = $this->toSqlWithBindings($model);
        $table = $model->getQuery()->from;

        return $query
            ->from(DB::raw("({$sql}) as " . $table))
            ->select($table . '.*');
    }

    /**
     * Adds a select for a min or max on the given column, depending on direction given
     *
     * @param Builder $query
     * @param string $column
     * @param string $direction
     * @return Builder
     */
    public function selectMinMax($query, $column, $direction)
    {
        $column = $this->addBackticks($column);

        /** @var Model $query */
        if ($direction == 'asc') {
            return $query->select(DB::raw('MIN(' . $column . ')'));
        } else {
            return $query->select(DB::raw('MAX(' . $column . ')'));
        }
    }

    /**
     * Add select for related table fields
     *
     * @param Builder $query
     * @param $table
     * @return Builder
     */
    public function selectRelated(Builder $query, $table)
    {
        $connection = $this->connection;

        foreach (Schema::connection($connection)->getColumnListing($table->name) as $relatedColumn) {
            $query->addSelect(
                new Expression("`$table->alias`.`$relatedColumn` AS `$table->alias.$relatedColumn`")
            );
        }

        return $query;
    }

    /**
     * Join a HasOne relation which is ordered
     *
     * @param Relation $relation
     * @param JoinClause $join
     * @return JoinClause
     */
    private function hasOneJoin($relation, $join)
    {
        // Get first relation order (should only be one)
        $order = $relation->toBase()->orders[0];

        return $join->on($order['column'], $this->hasOneJoinSql($relation, $order));
    }

    /**
     * Join a HasMany Relation
     *
     * @param Relation $relation
     * @param JoinClause $join
     * @param \stdClass $table
     * @param string $operator
     * @param string $direction
     * @return Builder|JoinClause
     */
    private function hasManyJoin($relation, $join, $table, $operator, $direction)
    {
        // Get relation join columns
        $joinColumns = $this->getJoinColumns($relation);

        $first = $joinColumns->first;
        $second = $joinColumns->second;
        if ($table->name !== $table->alias) {
            $first = str_replace($table->name, $table->alias, $first);
            $second = str_replace($table->name, $table->alias, $second);
        }

        $join->on($first, $operator, $second);

        // Add any where clauses from the relationship
        $join = $this->addWhereConstraints($join, $relation, $tableAlias);


        if (!is_null($direction) && get_class($relation) === HasMany::class) {
            $join = $this->hasManyJoinWhere($join, $first, $relation, $tableAlias, $direction);
        }

        return $join;
    }

}


1			<?php
2
3			namespace Blasttech\EloquentRelatedPlus;
4
5			use Illuminate\Database\Eloquent\Builder;
6			use Illuminate\Database\Eloquent\Model;
7			use Illuminate\Database\Eloquent\Relations\BelongsTo;
8			use Illuminate\Database\Eloquent\Relations\HasMany;
9			use Illuminate\Database\Eloquent\Relations\HasOneOrMany;
10			use Illuminate\Database\Eloquent\Relations\Relation;
11			use Illuminate\Database\Query\Expression;
12			use Illuminate\Database\Query\JoinClause;
13			use Illuminate\Support\Facades\DB;
14			use Illuminate\Support\Facades\Schema;
15			use InvalidArgumentException;
16
17			/**
18			* Trait RelatedPlusTrait
19			*
20			* @property array order_fields
21			* @property array order_defaults
22			* @property array order_relations
23			* @property array order_with
24			* @property array search_fields
25			* @property string connection
26			*/
27			trait RelatedPlusTrait
28			{
29			use CustomOrderTrait, HelperMethodTrait, SearchTrait;
30
31			/**
32			* Add orderBy if orders exist for a relation
33			*
34			* @param Builder\|JoinClause $builder
35			* @param Relation\|BelongsTo\|HasOneOrMany $relation
36			* @param string $table
37			* @return Builder\|JoinClause $builder
38			*/
39			protected function addOrder($builder, $relation, $table)
40			{
41			/** @var Model $builder */
42			if (!empty($relation->toBase()->orders)) {
43			// Get where clauses from the relationship
44			foreach ($relation->toBase()->orders as $order) {
45			$builder->orderBy($this->columnWithTableName($table, $order['column']), $order['direction']);
46			}
47			}
48
49			return $builder;
50			}
51
52			/**
53			* Add wheres if they exist for a relation
54			*
55			* @param Builder\|JoinClause $builder
56			* @param Relation\|BelongsTo\|HasOneOrMany $relation
57			* @param string $table
58			* @return Builder\|JoinClause $builder
59			*/
60			protected function addWhereConstraints($builder, $relation, $table)
61			{
62			// Get where clauses from the relationship
63			$wheres = collect($relation->toBase()->wheres)
64			->where('type', 'Basic')
65			->map(function ($where) use ($table) {
66			// Add table name to column if it is absent
67			return [$this->columnWithTableName($table, $where['column']), $where['operator'], $where['value']];
68			})->toArray();
69
70			if (!empty($wheres)) {
71			$builder->where($wheres);
72			}
73
74			return $builder;
75			}
76
77			/**
78			* Boot method for trait
79			*
80			*/
81			public static function bootRelatedPlusTrait()
82			{
83			static::saving(function ($model) {
84			if (!empty($model->nullable)) {
85			foreach ($model->attributes as $key => $value) {
86			if (isset($model->nullable[$key])) {
87			$model->{$key} = empty(trim($value)) ? null : $value;
88			}
89			}
90			}
91			});
92			}
93
94			/**
95			* Get the table associated with the model.
96			*
97			* @return string
98			*/
99			abstract public function getTable();
100
101			/**
102			* If the relation is one-to-many, just get the first related record
103			*
104			* @param JoinClause $joinClause
105			* @param string $column
106			* @param HasMany\|Relation $relation
107			* @param string $table
108			* @param string $direction
109			*
110			* @return JoinClause
111			*/
112			public function hasManyJoinWhere(JoinClause $joinClause, $column, $relation, $table, $direction)
113			{
114			return $joinClause->where(
115			$column,
116			function ($subQuery) use ($table, $direction, $relation, $column) {
117			$subQuery = $this->joinOne(
118			$subQuery->from($table),
119			$relation,
120			$column,
121			$direction
122			);
123
124			// Add any where statements with the relationship
125			$subQuery = $this->addWhereConstraints($subQuery, $relation, $table);
126
127			// Add any order statements with the relationship
128			return $this->addOrder($subQuery, $relation, $table);
129			}
130			);
131			}
132
133			/**
134			* Get join sql for a HasOne relation
135			*
136			* @param Relation $relation
137			* @param array $order
138			* @return Expression
139			*/
140			public function hasOneJoinSql($relation, $order)
141			{
142			// Build subquery for getting first/last record in related table
143			$subQuery = $this
144			->joinOne(
145			$relation->getRelated()->newQuery(),
146			$relation,
147			$order['column'],
148			$order['direction']
149			)
150			->setBindings($relation->getBindings());
151
152			return DB::raw('(' . $this->toSqlWithBindings($subQuery) . ')');
153			}
154
155			/**
156			* Adds a where for a relation's join columns and and min/max for a given column
157			*
158			* @param Builder $query
159			* @param Relation $relation
160			* @param string $column
161			* @param string $direction
162			* @return Builder
163			*/
164			public function joinOne($query, $relation, $column, $direction)
165			{
166			// Get join fields
167			$joinColumns = $this->getJoinColumns($relation);
168
169			return $this->selectMinMax(
170			$query->whereColumn($joinColumns->first, '=', $joinColumns->second),
171			$column,
172			$direction
173			);
174			}
175
176			/**
177			* Add joins for one or more relations
178			* This determines the foreign key relations automatically to prevent the need to figure out the columns.
179			* Usages:
180			* $query->modelJoin('customers')
181			* $query->modelJoin('customer.client')
182			*
183			* @param Builder $query
184			* @param string $relationName
185			* @param string $operator
186			* @param string $type
187			* @param bool $where
188			* @param bool $relatedSelect
189			* @param string\|null $direction
190			*
191			* @return Builder
192			*/
193			public function scopeModelJoin(
194			Builder $query,
195			$relationName,
196			$operator = '=',
197			$type = 'left',
198			$where = false,
199			$relatedSelect = true,
200			$direction = null
201			) {
202			foreach ($this->parseRelationNames($relationName) as $relation) {
203			$table = $this->getRelationTables($relation);
204
205			/** @var Model $query */
206			if (empty($query->getQuery()->columns)) {
207			$query->select($this->getTable() . ".*");
208			}
209			if ($relatedSelect) {
210			$query = $this->selectRelated($query, $table);
211			}
212			$query->relationJoin($table, $relation, $operator, $type, $where, $direction);
213			}
214
215			return $query;
216			}
217
218			/**
219			* Set the order of a model
220			*
221			* @param Builder $query
222			* @param string $orderField
223			* @param string $direction
224			* @return Builder
225			*/
226			public function scopeOrderByCustom(Builder $query, $orderField, $direction)
227			{
228			if ($this->hasOrderFieldsAndDefaults($orderField, $direction)) {
229			$query = $this->removeGlobalScope($query, 'order');
230			}
231
232			return $query->setCustomOrder($orderField, $direction);
233			}
234
235			/**
236			* Use a model method to add columns or joins if in the order options
237			*
238			* @param Builder $query
239			* @param string $order
240			* @return Builder
241			*/
242			public function scopeOrderByWith(Builder $query, $order)
243			{
244			if (isset($this->order_with[$order])) {
245			$with = 'with' . $this->order_with[$order];
246
247			$query->$with();
248			}
249
250			if (isset($this->order_fields[$order])) {
251			$orderOption = (explode('.', $this->order_fields[$order]))[0];
252
253			if (isset($this->order_relations[$orderOption])) {
254			$query->modelJoin(
255			$this->order_relations[$orderOption],
256			'=',
257			'left',
258			false,
259			false
260			);
261			}
262			}
263
264			return $query;
265			}
266
267			/**
268			* Join a model
269			*
270			* @param Builder $query
271			* @param \stdClass $table
272			* @param Relation $relation
273			* @param string $operator
274			* @param string $type
275			* @param boolean $where
276			* @param null $direction
277			* @return Builder
278			*/
279			public function scopeRelationJoin(
280			Builder $query,
281			$table,
282			$relation,
283			$operator,
284			$type,
285			$where,
286			$direction = null
287			) {
288			if ($table->alias !== '' && $table->name !== $table->alias) {
289			$fullTableName = $table->name . ' AS ' . $table->alias;
290			} else {
291			$fullTableName = $table->name;
292			}
293
294			return $query->join($fullTableName, function (JoinClause $join) use (
295			$table,
296			$relation,
297			$operator,
298			$direction
299			) {
300			// If a HasOne relation and ordered - ie join to the latest/earliest
301			if (class_basename($relation) === 'HasOne' && !empty($relation->toBase()->orders)) {
302			return $this->hasOneJoin($relation, $join);
303			} else {
304			return $this->hasManyJoin($relation, $join, $table, $operator, $direction);
305			}
306			}, null, null, $type, $where);
307			}
308
309			/**
310			* Add where statements for the model search fields
311			*
312			* @param Builder $query
313			* @param string $searchText
314			* @return Builder
315			*/
316			public function scopeSearch(Builder $query, $searchText = '')
317			{
318			$searchText = trim($searchText);
319
320			// If search is set
321			if ($searchText != "") {
322			if (!isset($this->search_fields) \|\| !is_array($this->search_fields) \|\| empty($this->search_fields)) {
323			throw new InvalidArgumentException(get_class($this) . ' search properties not set correctly.');
324			} else {
325			$query = $this->checkSearchFields($query, $searchText);
326			}
327			}
328
329			return $query;
330			}
331
332			/**
333			* Switch a query to be a subquery of a model
334			*
335			* @param Builder $query
336			* @param Builder $model
337			* @return Builder
338			*/
339			public function scopeSetSubquery(Builder $query, $model)
340			{
341			$sql = $this->toSqlWithBindings($model);
342			$table = $model->getQuery()->from;
343
344			return $query
345			->from(DB::raw("({$sql}) as " . $table))
346			->select($table . '.*');
347			}
348
349			/**
350			* Adds a select for a min or max on the given column, depending on direction given
351			*
352			* @param Builder $query
353			* @param string $column
354			* @param string $direction
355			* @return Builder
356			*/
357			public function selectMinMax($query, $column, $direction)
358			{
359			$column = $this->addBackticks($column);
360
361			/** @var Model $query */
362			if ($direction == 'asc') {
363			return $query->select(DB::raw('MIN(' . $column . ')'));
364			} else {
365			return $query->select(DB::raw('MAX(' . $column . ')'));
366			}
367			}
368
369			/**
370			* Add select for related table fields
371			*
372			* @param Builder $query
373			* @param $table
374			* @return Builder
375			*/
376			public function selectRelated(Builder $query, $table)
377			{
378			$connection = $this->connection;
379
380			foreach (Schema::connection($connection)->getColumnListing($table->name) as $relatedColumn) {
381			$query->addSelect(
382			new Expression("`$table->alias`.`$relatedColumn` AS `$table->alias.$relatedColumn`")
383			);
384			}
385
386			return $query;
387			}
388
389			/**
390			* Join a HasOne relation which is ordered
391			*
392			* @param Relation $relation
393			* @param JoinClause $join
394			* @return JoinClause
395			*/
396			private function hasOneJoin($relation, $join)
397			{
398			// Get first relation order (should only be one)
399			$order = $relation->toBase()->orders[0];
400
401			return $join->on($order['column'], $this->hasOneJoinSql($relation, $order));
402			}
403
404			/**
405			* Join a HasMany Relation
406			*
407			* @param Relation $relation
408			* @param JoinClause $join
409			* @param \stdClass $table
410			* @param string $operator
411			* @param string $direction
412			* @return Builder\|JoinClause
413			*/
414			private function hasManyJoin($relation, $join, $table, $operator, $direction)
415			{
416			// Get relation join columns
417			$joinColumns = $this->getJoinColumns($relation);
418
419			$first = $joinColumns->first;
420			$second = $joinColumns->second;
421			if ($table->name !== $table->alias) {
422			$first = str_replace($table->name, $table->alias, $first);
423			$second = str_replace($table->name, $table->alias, $second);
424			}
425
426			$join->on($first, $operator, $second);
427
428			// Add any where clauses from the relationship
429			$join = $this->addWhereConstraints($join, $relation, $tableAlias);
			0 ignored issues – show Bug introduced 2018-02-06 05:56 UTC by Report Bug Copy Issue Report The variable `$tableAlias` does not exist. Did you forget to declare it? This check marks access to variables or properties that have not been declared yet. While PHP has no explicit notion of declaring a variable, accessing it before a value is assigned to it is most likely a bug. Loading history...
430
431			if (!is_null($direction) && get_class($relation) === HasMany::class) {
432			$join = $this->hasManyJoinWhere($join, $first, $relation, $tableAlias, $direction);
433			}
434
435			return $join;
436			}
437
438			}
439

blasttech / eloquent-related-plus

Push — master ( c4bcc6...c033be )

RelatedPlusTrait::getTable()

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like