RelationPlus::hasManyJoin() - Code Metrics - blasttech/eloquent-related-plus - Measure and Improve Code Quality continuously with Scrutinizer

RelationPlus::hasManyJoin() A
last analyzed 2021-04-13 00:05 UTC

↳ Parent: RelationPlus

Complexity

Conditions	3
Paths	2

Size

Total Lines	15
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	1	Features	0

Metric	Value
cc	3
eloc	6
c	2
b	1
f	0
nc	2
nop	3
dl	0
loc	15
rs	10

<?php

namespace Blasttech\EloquentRelatedPlus;

use DB;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Relations\HasOneOrMany;
use Illuminate\Database\Query\Expression;
use Illuminate\Database\Query\JoinClause;

/**
 * Class RelationPlus
 *
 * @package Blasttech\EloquentRelatedPlus\Relations
 */
class RelationPlus
{
    use HelpersTrait;

    /**
     * @var string $tableName
     */
    public $tableName;

    /**
     * @var string $tableAlias
     */
    public $tableAlias;

    /**
     * Initialise $relation, $tableName and $tableAlias
     * If using a 'table' AS 'tableAlias' in a from statement, otherwise alias will be the table name
     *
     * @var BelongsTo|HasOneOrMany $relation
     */
    private $relation;

    public function __construct($relation)
    {
        $this->setRelation($relation);
        $this->tableName = $this->relation->getRelated()->getTable();
        $from = explode(' ', $this->relation->getQuery()->getQuery()->from);
        $this->tableAlias = array_pop($from);
    }

    /**
     * @return BelongsTo|HasOneOrMany
     */
    public function getRelation()
    {
        return $this->relation;
    }

    /**
     * @param BelongsTo|HasOneOrMany $relation
     */
    public function setRelation($relation)
    {
        $this->relation = $relation;
    }

    /**
     * Check relation type and get join
     *
     * @param JoinClause $join
     * @param string $operator
     * @param string|null $direction
     * @return Builder|JoinClause
     */
    public function getRelationJoin($join, $operator, $direction = null)
    {
        // If a HasOne relation and ordered - ie join to the latest/earliest
        if (class_basename($this->relation) === 'HasOne') {
            $this->relation = $this->removeGlobalScopes($this->relation->getRelated(), $this->relation, 'order');

            if (!empty($this->getOrders())) {
                return $this->hasOneJoin($join);
            }
        }

        return $this->hasManyJoin($join, $operator, $direction);
    }

    /**
     * Get the orders for the relation
     *
     * @return array
     */
    private function getOrders()
    {
        return $this->relation->toBase()->orders;
    }

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

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

    /**
     * Get join sql for a HasOne relation
     *
     * @param array $order
     * @return Expression
     */
    private function hasOneJoinSql($order)
    {
        // Build subquery for getting first/last record in related table
        $subQuery = $this
            ->joinOne(
                $this->relation->getRelated()->newQuery(),
                $order['column'],
                $order['direction']
            )
            ->setBindings($this->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 string $column
     * @param string $direction
     * @return Builder
     */
    private function joinOne($query, $column, $direction)
    {
        // Get join fields
        $joinColumns = $this->getJoinColumns();

        return $this->selectMinMax(

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

    /**
     * Get the join columns for a relation
     *
     * @return \stdClass
     */
    private function getJoinColumns()
    {
        // Get keys with table names
        if ($this->relation instanceof BelongsTo) {
            return $this->getBelongsToColumns();
        }

        return $this->getHasOneOrManyColumns();
    }

    /**
     * Get the join columns for a BelongsTo relation
     *
     * @return object
     */
    private function getBelongsToColumns()
    {
        // Use relation ownerKey if it contains table name, otherwise use getQualifiedOwnerKeyName
        $first = $this->relation->getOwnerKeyName();
        if (!strpos($first, '.')) {
            $first = $this->relation->getQualifiedOwnerKeyName();
        }

        // Use relation foreignKey if it contains table name, otherwise use getQualifiedForeignKey
        $second = $this->relation->getForeignKeyName();
        if (!strpos($second, '.')) {
            $second = $this->relation->getQualifiedForeignKeyName();
        }

        return (object)['first' => $first, 'second' => $second];
    }

    /**
     * Get the join columns for a HasOneOrMany relation
     *
     * @return object
     */
    private function getHasOneOrManyColumns()
    {
        $first = $this->relation->getQualifiedParentKeyName();
        $second = $this->relation->getQualifiedForeignKeyName();

        return (object)['first' => $first, 'second' => $second];
    }

    /**
     * Adds a select for a min or max on the given column, depending on direction given
     *
     * @param Builder|\Illuminate\Database\Query\Builder $query
     * @param string $column
     * @param string $direction
     * @return Builder|\Illuminate\Database\Query\Builder
     */
    private function selectMinMax($query, $column, $direction)
    {
        $sqlDirection = ($direction == 'asc' ? 'MIN' : 'MAX');

        return $query->select(DB::raw($sqlDirection . '(' . $this->addBackticks($column) . ')'));
    }

    /**
     * Add backticks to a table/column
     *
     * @param string $column
     * @return string
     */
    private function addBackticks($column)
    {
        return preg_match('/^[0-9a-zA-Z\.]*$/', $column) ?
            '`' . str_replace(['`', '.'], ['', '`.`'], $column) . '`' : $column;
    }

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

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

        // Add any where clauses from the relationship
        $join = $this->addRelatedWhereConstraints($join); // $table->alias

        if (!is_null($direction) && get_class($this->relation) === HasMany::class) {

            $join = $this->hasManyJoinWhere($join, $joinColumns->first, $direction); // $table->alias,
        }

        return $join;
    }

    /**
     * Replace column table names with aliases
     *
     * @param \stdClass $joinColumns
     * @return \stdClass
     */
    private function replaceColumnTables($joinColumns)
    {
        if ($this->tableName !== $this->tableAlias) {
            $joinColumns->first = str_replace($this->tableName, $this->tableAlias, $joinColumns->first);
            $joinColumns->second = str_replace($this->tableName, $this->tableAlias, $joinColumns->second);
        }

        return $joinColumns;
    }

    /**
     * Add wheres if they exist for a relation
     *
     * @param Builder|JoinClause $builder
     * @return Builder|JoinClause $builder
     */
    private function addRelatedWhereConstraints($builder)
    {
        // Get where clauses from the relationship
        $wheres = collect($this->relation->toBase()->wheres)
            ->whereIn('type', ['Basic', 'Nested'])
            ->map(function ($where) {
                return collect($where['type'] == 'Basic' ? [$where] : $where['query']->wheres)
                    ->map(function ($where) {
                        // Add table name to column if it is absent
                        return [
                            $this->columnWithTableName($where['column']),
                            $where['operator'],
                            $where['value']
                        ];
                    });
            })
            ->flatten(1)
            ->toArray();

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

        return $builder;
    }

    /**
     * Add table name to column name if table name not already included in column name
     *
     * @param string $column
     * @return string
     */
    private function columnWithTableName($column)
    {
        return (preg_match('/(' . $this->tableAlias . '\.|`' . $this->tableAlias . '`)/i', $column) > 0
                ? '' : $this->tableAlias . '.') . $column;
    }

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

                // Add any where statements with the relationship
                $subQuery = $this->addRelatedWhereConstraints($subQuery); // $this->tableAlias

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

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

        return $builder;
    }

    /**
     * Get table name with alias if different to table name
     *
     * @return string
     */
    public function getTableWithAlias()
    {
        if ($this->tableAlias !== '' && $this->tableName !== $this->tableAlias) {
            return $this->tableName . ' AS ' . $this->tableAlias;
        }

        // it means the connection DB name is not on the table alias yet
        if (strpos('.', $this->tableName) === null) {
            return $this->relation->getConnection()->getDatabaseName() . '.' . $this->tableName;
        }

        return $this->tableName;
    }
}


1			<?php
2
3			namespace Blasttech\EloquentRelatedPlus;
4
5			use DB;
6			use Illuminate\Database\Eloquent\Builder;
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\Query\Expression;
11			use Illuminate\Database\Query\JoinClause;
12
13			/**
14			* Class RelationPlus
15			*
16			* @package Blasttech\EloquentRelatedPlus\Relations
17			*/
18			class RelationPlus
19			{
20			use HelpersTrait;
21
22			/**
23			* @var string $tableName
24			*/
25			public $tableName;
26
27			/**
28			* @var string $tableAlias
29			*/
30			public $tableAlias;
31
32			/**
33			* Initialise $relation, $tableName and $tableAlias
34			* If using a 'table' AS 'tableAlias' in a from statement, otherwise alias will be the table name
35			*
36			* @var BelongsTo\|HasOneOrMany $relation
37			*/
38			private $relation;
39
40			public function __construct($relation)
41			{
42			$this->setRelation($relation);
43			$this->tableName = $this->relation->getRelated()->getTable();
44			$from = explode(' ', $this->relation->getQuery()->getQuery()->from);
45			$this->tableAlias = array_pop($from);
46			}
47
48			/**
49			* @return BelongsTo\|HasOneOrMany
50			*/
51			public function getRelation()
52			{
53			return $this->relation;
54			}
55
56			/**
57			* @param BelongsTo\|HasOneOrMany $relation
58			*/
59			public function setRelation($relation)
60			{
61			$this->relation = $relation;
62			}
63
64			/**
65			* Check relation type and get join
66			*
67			* @param JoinClause $join
68			* @param string $operator
69			* @param string\|null $direction
70			* @return Builder\|JoinClause
71			*/
72			public function getRelationJoin($join, $operator, $direction = null)
73			{
74			// If a HasOne relation and ordered - ie join to the latest/earliest
75			if (class_basename($this->relation) === 'HasOne') {
76			$this->relation = $this->removeGlobalScopes($this->relation->getRelated(), $this->relation, 'order');
77
78			if (!empty($this->getOrders())) {
79			return $this->hasOneJoin($join);
80			}
81			}
82
83			return $this->hasManyJoin($join, $operator, $direction);
84			}
85
86			/**
87			* Get the orders for the relation
88			*
89			* @return array
90			*/
91			private function getOrders()
92			{
93			return $this->relation->toBase()->orders;
94			}
95
96			/**
97			* Join a HasOne relation which is ordered
98			*
99			* @param JoinClause $join
100			* @return JoinClause
101			*/
102			private function hasOneJoin($join)
103			{
104			// Get first relation order (should only be one)
105			$order = $this->getOrders()[0];
106
107			return $join->on($order['column'], $this->hasOneJoinSql($order));
108			}
109
110			/**
111			* Get join sql for a HasOne relation
112			*
113			* @param array $order
114			* @return Expression
115			*/
116			private function hasOneJoinSql($order)
117			{
118			// Build subquery for getting first/last record in related table
119			$subQuery = $this
120			->joinOne(
121			$this->relation->getRelated()->newQuery(),
122			$order['column'],
123			$order['direction']
124			)
125			->setBindings($this->relation->getBindings());
126
127			return DB::raw('(' . $this->toSqlWithBindings($subQuery) . ')');
128			}
129
130			/**
131			* Adds a where for a relation's join columns and and min/max for a given column
132			*
133			* @param Builder $query
134			* @param string $column
135			* @param string $direction
136			* @return Builder
137			*/
138			private function joinOne($query, $column, $direction)
139			{
140			// Get join fields
141			$joinColumns = $this->getJoinColumns();
142
143			return $this->selectMinMax(
			0 ignored issues – show Bug Best Practice introduced 2018-02-14 06:29 UTC by Report Bug Copy Issue Report The expression `return $this->selectMinM...), $column, $direction)` also could return the type `Illuminate\Database\Query\Builder` which is incompatible with the documented return type `Illuminate\Database\Eloquent\Builder`. Loading history...
144			$query->whereColumn($joinColumns->first, '=', $joinColumns->second),
145			$column,
146			$direction
147			);
148			}
149
150			/**
151			* Get the join columns for a relation
152			*
153			* @return \stdClass
154			*/
155			private function getJoinColumns()
156			{
157			// Get keys with table names
158			if ($this->relation instanceof BelongsTo) {
159			return $this->getBelongsToColumns();
160			}
161
162			return $this->getHasOneOrManyColumns();
163			}
164
165			/**
166			* Get the join columns for a BelongsTo relation
167			*
168			* @return object
169			*/
170			private function getBelongsToColumns()
171			{
172			// Use relation ownerKey if it contains table name, otherwise use getQualifiedOwnerKeyName
173			$first = $this->relation->getOwnerKeyName();
174			if (!strpos($first, '.')) {
175			$first = $this->relation->getQualifiedOwnerKeyName();
176			}
177
178			// Use relation foreignKey if it contains table name, otherwise use getQualifiedForeignKey
179			$second = $this->relation->getForeignKeyName();
180			if (!strpos($second, '.')) {
181			$second = $this->relation->getQualifiedForeignKeyName();
182			}
183
184			return (object)['first' => $first, 'second' => $second];
185			}
186
187			/**
188			* Get the join columns for a HasOneOrMany relation
189			*
190			* @return object
191			*/
192			private function getHasOneOrManyColumns()
193			{
194			$first = $this->relation->getQualifiedParentKeyName();
195			$second = $this->relation->getQualifiedForeignKeyName();
196
197			return (object)['first' => $first, 'second' => $second];
198			}
199
200			/**
201			* Adds a select for a min or max on the given column, depending on direction given
202			*
203			* @param Builder\|\Illuminate\Database\Query\Builder $query
204			* @param string $column
205			* @param string $direction
206			* @return Builder\|\Illuminate\Database\Query\Builder
207			*/
208			private function selectMinMax($query, $column, $direction)
209			{
210			$sqlDirection = ($direction == 'asc' ? 'MIN' : 'MAX');
211
212			return $query->select(DB::raw($sqlDirection . '(' . $this->addBackticks($column) . ')'));
213			}
214
215			/**
216			* Add backticks to a table/column
217			*
218			* @param string $column
219			* @return string
220			*/
221			private function addBackticks($column)
222			{
223			return preg_match('/^[0-9a-zA-Z\.]*$/', $column) ?
224			'`' . str_replace(['`', '.'], ['', '`.`'], $column) . '`' : $column;
225			}
226
227			/**
228			* Join a HasMany Relation
229			*
230			* @param JoinClause $join
231			* @param string $operator
232			* @param string $direction
233			* @return Builder\|JoinClause
234			*/
235			private function hasManyJoin($join, $operator, $direction)
236			{
237			// Get relation join columns
238			$joinColumns = $this->replaceColumnTables($this->getJoinColumns());
239
240			$join->on($joinColumns->first, $operator, $joinColumns->second);
241
242			// Add any where clauses from the relationship
243			$join = $this->addRelatedWhereConstraints($join); // $table->alias
244
245			if (!is_null($direction) && get_class($this->relation) === HasMany::class) {
			0 ignored issues – show introduced 2018-10-24 04:22 UTC by Report Bug Copy Issue Report The condition `is_null($direction)` is always `false`. Loading history...
246			$join = $this->hasManyJoinWhere($join, $joinColumns->first, $direction); // $table->alias,
247			}
248
249			return $join;
250			}
251
252			/**
253			* Replace column table names with aliases
254			*
255			* @param \stdClass $joinColumns
256			* @return \stdClass
257			*/
258			private function replaceColumnTables($joinColumns)
259			{
260			if ($this->tableName !== $this->tableAlias) {
261			$joinColumns->first = str_replace($this->tableName, $this->tableAlias, $joinColumns->first);
262			$joinColumns->second = str_replace($this->tableName, $this->tableAlias, $joinColumns->second);
263			}
264
265			return $joinColumns;
266			}
267
268			/**
269			* Add wheres if they exist for a relation
270			*
271			* @param Builder\|JoinClause $builder
272			* @return Builder\|JoinClause $builder
273			*/
274			private function addRelatedWhereConstraints($builder)
275			{
276			// Get where clauses from the relationship
277			$wheres = collect($this->relation->toBase()->wheres)
278			->whereIn('type', ['Basic', 'Nested'])
279			->map(function ($where) {
280			return collect($where['type'] == 'Basic' ? [$where] : $where['query']->wheres)
281			->map(function ($where) {
282			// Add table name to column if it is absent
283			return [
284			$this->columnWithTableName($where['column']),
285			$where['operator'],
286			$where['value']
287			];
288			});
289			})
290			->flatten(1)
291			->toArray();
292
293			if (!empty($wheres)) {
294			$builder->where($wheres);
295			}
296
297			return $builder;
298			}
299
300			/**
301			* Add table name to column name if table name not already included in column name
302			*
303			* @param string $column
304			* @return string
305			*/
306			private function columnWithTableName($column)
307			{
308			return (preg_match('/(' . $this->tableAlias . '\.\|`' . $this->tableAlias . '`)/i', $column) > 0
309			? '' : $this->tableAlias . '.') . $column;
310			}
311
312			/**
313			* If the relation is one-to-many, just get the first related record
314			*
315			* @param JoinClause $joinClause
316			* @param string $column
317			* @param string $direction
318			*
319			* @return JoinClause
320			*/
321			private function hasManyJoinWhere(JoinClause $joinClause, $column, $direction)
322			{
323			return $joinClause->where(
324			$column,
325			function ($subQuery) use ($column, $direction) {
326			$subQuery = $this->joinOne(
327			$subQuery->from($this->tableAlias),
328			$column,
329			$direction
330			);
331
332			// Add any where statements with the relationship
333			$subQuery = $this->addRelatedWhereConstraints($subQuery); // $this->tableAlias
334
335			// Add any order statements with the relationship
336			return $this->addOrder($subQuery); // $this->tableAlias
337			}
338			);
339			}
340
341			/**
342			* Add orderBy if orders exist for a relation
343			*
344			* @param Builder\|JoinClause $builder
345			* @return Builder\|JoinClause $builder
346			*/
347			private function addOrder($builder)
348			{
349			if (!empty($this->getOrders())) {
350			// Get where clauses from the relationship
351			foreach ($this->getOrders() as $order) {
352			$builder->orderBy($this->columnWithTableName($order['column']), $order['direction']);
353			}
354			}
355
356			return $builder;
357			}
358
359			/**
360			* Get table name with alias if different to table name
361			*
362			* @return string
363			*/
364			public function getTableWithAlias()
365			{
366			if ($this->tableAlias !== '' && $this->tableName !== $this->tableAlias) {
367			return $this->tableName . ' AS ' . $this->tableAlias;
368			}
369
370			// it means the connection DB name is not on the table alias yet
371			if (strpos('.', $this->tableName) === null) {
372			return $this->relation->getConnection()->getDatabaseName() . '.' . $this->tableName;
373			}
374
375			return $this->tableName;
376			}
377			}
378

blasttech / eloquent-related-plus

RelationPlus::hasManyJoin() A last analyzed 2021-04-13 00:05 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

RelationPlus::hasManyJoin() A
last analyzed 2021-04-13 00:05 UTC