Select::join() - Code Metrics - Inspection of "Bluz 7.0.0" - bluzphp/framework - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#363)

by Anton

created 2016-06-13 09:18 UTC

Select::join() A

↳ Parent: Select

Complexity

Conditions	1
Paths	1

Size

Total Lines	4
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	2
CRAP Score	1

Importance

Changes

Metric	Value
cc	1
eloc	2
nc	1
nop	4
dl	0
loc	4
ccs	2
cts	2
cp	1
crap	1
rs	10
c	0
b	0
f	0

<?php
/**
 * Bluz Framework Component
 *
 * @copyright Bluz PHP Team
 * @link https://github.com/bluzphp/framework
 */

/**
 * @namespace
 */
namespace Bluz\Db\Query;

use Bluz\Db\Exception\DbException;
use Bluz\Proxy\Db;

/**
 * Builder of SELECT queries
 *
 * @package Bluz\Db\Query
 */
class Select extends AbstractBuilder
{
    use Traits\From;
    use Traits\Where;
    use Traits\Order;
    use Traits\Limit;

    /**
     * @var mixed PDO fetch types or object class
     */
    protected $fetchType = \PDO::FETCH_ASSOC;

    /**
     * {@inheritdoc}
     *
     * @param  integer|string|object $fetchType
     * @return integer|string|array
     */
    public function execute($fetchType = null)
    {
        if (!$fetchType) {
            $fetchType = $this->fetchType;
        }

        switch ($fetchType) {
            case (!is_int($fetchType)):
                return Db::fetchObjects($this->getSQL(), $this->params, $fetchType);
            case \PDO::FETCH_CLASS:
                return Db::fetchObjects($this->getSQL(), $this->params);
            case \PDO::FETCH_ASSOC:
            default:
                return Db::fetchAll($this->getSQL(), $this->params);
        }
    }

    /**
     * Setup fetch type, any of PDO, or any Class
     *
     * @param  string $fetchType
     * @return Select instance
     */
    public function setFetchType($fetchType)
    {
        $this->fetchType = $fetchType;
        return $this;
    }

    /**
     * {@inheritdoc}
     *
     * @return string
     */
    public function getSql()
    {
        $query = "SELECT " . implode(', ', $this->sqlParts['select']) . " FROM ";

        $fromClauses = array();

        // Loop through all FROM clauses
        foreach ($this->sqlParts['from'] as $from) {
            $fromClause = $from['table'] . ' ' . $from['alias']
                . $this->getSQLForJoins($from['alias']);

            $fromClauses[$from['alias']] = $fromClause;
        }

        $query .= join(', ', $fromClauses)
            . ($this->sqlParts['where'] !== null ? " WHERE " . ((string) $this->sqlParts['where']) : "")
            . ($this->sqlParts['groupBy'] ? " GROUP BY " . join(", ", $this->sqlParts['groupBy']) : "")
            . ($this->sqlParts['having'] !== null ? " HAVING " . ((string) $this->sqlParts['having']) : "")
            . ($this->sqlParts['orderBy'] ? " ORDER BY " . join(", ", $this->sqlParts['orderBy']) : "")
            . ($this->limit ? " LIMIT ". $this->limit ." OFFSET ". $this->offset : "")
        ;

        return $query;
    }

    /**
     * Specifies an item that is to be returned in the query result
     * Replaces any previously specified selections, if any
     *
     * Example
     * <code>
     *     $sb = new Select();
     *     $sb
     *         ->select('u.id', 'p.id')
     *         ->from('users', 'u')
     *         ->leftJoin('u', 'phone', 'p', 'u.id = p.user_id');
     * </code>
     *
     * @param  string[] $select the selection expressions
     * @return Select instance
     */
    public function select(...$select)
    {
        return $this->addQueryPart('select', $select, false);
    }

    /**
     * Adds an item that is to be returned in the query result.
     *
     * Example
     * <code>
     *     $sb = new Select();
     *     $sb
     *         ->select('u.id')
     *         ->addSelect('p.id')
     *         ->from('users', 'u')
     *         ->leftJoin('u', 'phone', 'u.id = p.user_id');
     * </code>
     *
     * @param  string $select the selection expression
     * @return Select instance
     */
    public function addSelect($select)
    {
        return $this->addQueryPart('select', $select, true);
    }

    /**
     * Creates and adds a join to the query
     *
     * Example
     * <code>
     *     $sb = new Select();
     *     $sb
     *         ->select('u.name')
     *         ->from('users', 'u')
     *         ->join('u', 'phone', 'p', 'p.is_primary = 1');
     * </code>
     *
     * @param  string $fromAlias the alias that points to a from clause
     * @param  string $join      the table name to join
     * @param  string $alias     the alias of the join table
     * @param  string $condition the condition for the join
     * @return Select instance
     */
    public function join($fromAlias, $join, $alias, $condition = null)
    {
        return $this->innerJoin($fromAlias, $join, $alias, $condition);
    }

    /**
     * Creates and adds a join to the query
     *
     * Example
     * <code>
     *     $sb = new Select();
     *     $sb
     *         ->select('u.name')
     *         ->from('users', 'u')
     *         ->innerJoin('u', 'phone', 'p', 'p.is_primary = 1');
     * </code>
     *
     * @param  string $fromAlias the alias that points to a from clause
     * @param  string $join      the table name to join
     * @param  string $alias     the alias of the join table
     * @param  string $condition the condition for the join
     * @return Select instance
     */
    public function innerJoin($fromAlias, $join, $alias, $condition = null)
    {
        $this->aliases[] = $alias;

        return $this->addQueryPart(
            'join',
            array(
                $fromAlias => array(
                    'joinType'      => 'inner',
                    'joinTable'     => $join,
                    'joinAlias'     => $alias,
                    'joinCondition' => $condition
                )
            ),
            true
        );
    }

    /**
     * Creates and adds a left join to the query.
     *
     * Example
     * <code>
     *     $sb = new Select();
     *     $sb
     *         ->select('u.name')
     *         ->from('users', 'u')
     *         ->leftJoin('u', 'phone', 'p', 'p.is_primary = 1');
     * </code>
     *
     * @param  string $fromAlias the alias that points to a from clause
     * @param  string $join      the table name to join
     * @param  string $alias     the alias of the join table
     * @param  string $condition the condition for the join
     * @return Select instance
     */
    public function leftJoin($fromAlias, $join, $alias, $condition = null)
    {
        $this->aliases[] = $alias;

        return $this->addQueryPart(
            'join',
            array(
                $fromAlias => array(
                    'joinType'      => 'left',
                    'joinTable'     => $join,
                    'joinAlias'     => $alias,
                    'joinCondition' => $condition
                )
            ),
            true
        );
    }

    /**
     * Creates and adds a right join to the query.
     *
     * Example
     * <code>
     *     $sb = new Select();
     *     $sb
     *         ->select('u.name')
     *         ->from('users', 'u')
     *         ->rightJoin('u', 'phone', 'p', 'p.is_primary = 1');
     * </code>
     *
     * @param  string $fromAlias the alias that points to a from clause
     * @param  string $join      the table name to join
     * @param  string $alias     the alias of the join table
     * @param  string $condition the condition for the join
     * @return Select instance
     */
    public function rightJoin($fromAlias, $join, $alias, $condition = null)
    {
        $this->aliases[] = $alias;

        return $this->addQueryPart(
            'join',
            array(
                $fromAlias => array(
                    'joinType'      => 'right',
                    'joinTable'     => $join,
                    'joinAlias'     => $alias,
                    'joinCondition' => $condition
                )
            ),
            true
        );
    }

    /**
     * Specifies a grouping over the results of the query.
     * Replaces any previously specified groupings, if any.
     *
     * Example
     * <code>
     *     $sb = new Select();
     *     $sb
     *         ->select('u.name')
     *         ->from('users', 'u')
     *         ->groupBy('u.id');
     * </code>
     *
     * @param  string[] $groupBy the grouping expression
     * @return Select instance
     */
    public function groupBy(...$groupBy)
    {
        if (empty($groupBy)) {
            return $this;
        }

        return $this->addQueryPart('groupBy', $groupBy, false);
    }

    /**
     * Adds a grouping expression to the query.
     *
     * Example
     * <code>
     *     $sb = new Select();
     *     $sb
     *         ->select('u.name')
     *         ->from('users', 'u')
     *         ->groupBy('u.lastLogin');
     *         ->addGroupBy('u.createdAt')
     * </code>
     *
     * @param  string[] $groupBy the grouping expression
     * @return Select instance
     */
    public function addGroupBy(...$groupBy)
    {
        if (empty($groupBy)) {
            return $this;
        }

        return $this->addQueryPart('groupBy', $groupBy, true);
    }

    /**
     * Specifies a restriction over the groups of the query.
     * Replaces any previous having restrictions, if any
     *
     * @param  string[] $condition the query restriction predicates
     * @return Select
     */
    public function having(...$condition)
    {
        $condition = $this->prepareCondition($condition);
        return $this->addQueryPart('having', $condition, false);
    }

    /**
     * Adds a restriction over the groups of the query, forming a logical
     * conjunction with any existing having restrictions
     *
     * @param  string[] $condition the query restriction predicates
     * @return Select
     */
    public function andHaving(...$condition)
    {
        $condition = $this->prepareCondition($condition);
        $having = $this->getQueryPart('having');

        if ($having instanceof CompositeBuilder && $having->getType() == 'AND') {
            $having->add($condition);
        } else {
            $having = new CompositeBuilder([$having, $condition]);
        }

        return $this->addQueryPart('having', $having, false);
    }

    /**
     * Adds a restriction over the groups of the query, forming a logical
     * disjunction with any existing having restrictions
     *
     * @param  string[] $condition the query restriction predicates
     * @return Select
     */
    public function orHaving(...$condition)
    {
        $condition = $this->prepareCondition($condition);
        $having = $this->getQueryPart('having');

        if ($having instanceof CompositeBuilder && $having->getType() == 'OR') {
            $having->add($condition);
        } else {
            $having = new CompositeBuilder([$having, $condition], 'OR');
        }

        return $this->addQueryPart('having', $having, false);
    }

    /**
     * Setup offset like a page number, start from 1
     *
     * @param  integer $page
     * @return Select
     * @throws DbException
     */
    public function setPage($page = 1)
    {
        if (!$this->limit) {
            throw new DbException("Please setup limit for use method `setPage`");
        }
        $this->offset = $this->limit * ($page - 1);
        return $this;
    }

    /**
     * Generate SQL string for JOINs
     *
     * @internal
     * @param  string $fromAlias alias of table
     * @return string
     */
    protected function getSQLForJoins($fromAlias)
    {
        $sql = '';

        if (isset($this->sqlParts['join'][$fromAlias])) {
            foreach ($this->sqlParts['join'][$fromAlias] as $join) {
                $sql .= ' ' . strtoupper($join['joinType'])
                    . " JOIN " . $join['joinTable'] . ' ' . $join['joinAlias']
                    . " ON " . ((string) $join['joinCondition']);
                $sql .= $this->getSQLForJoins($join['joinAlias']);
            }
        }

        return $sql;
    }
}


1		<?php
2		/**
3		* Bluz Framework Component
4		*
5		* @copyright Bluz PHP Team
6		* @link https://github.com/bluzphp/framework
7		*/
8
9		/**
10		* @namespace
11		*/
12		namespace Bluz\Db\Query;
13
14		use Bluz\Db\Exception\DbException;
15		use Bluz\Proxy\Db;
16
17		/**
18		* Builder of SELECT queries
19		*
20		* @package Bluz\Db\Query
21		*/
22		class Select extends AbstractBuilder
23		{
24		use Traits\From;
25		use Traits\Where;
26		use Traits\Order;
27		use Traits\Limit;
28
29		/**
30		* @var mixed PDO fetch types or object class
31		*/
32		protected $fetchType = \PDO::FETCH_ASSOC;
33
34		/**
35		* {@inheritdoc}
36		*
37		* @param integer\|string\|object $fetchType
38		* @return integer\|string\|array
39		*/
40	1	public function execute($fetchType = null)
41		{
42	1	if (!$fetchType) {
43	1	$fetchType = $this->fetchType;
44		}
45
46		switch ($fetchType) {
47	1	case (!is_int($fetchType)):
48		return Db::fetchObjects($this->getSQL(), $this->params, $fetchType);
49	1	case \PDO::FETCH_CLASS:
50		return Db::fetchObjects($this->getSQL(), $this->params);
51	1	case \PDO::FETCH_ASSOC:
52		default:
53	1	return Db::fetchAll($this->getSQL(), $this->params);
54		}
55		}
56
57		/**
58		* Setup fetch type, any of PDO, or any Class
59		*
60		* @param string $fetchType
61		* @return Select instance
62		*/
63		public function setFetchType($fetchType)
64		{
65		$this->fetchType = $fetchType;
66		return $this;
67		}
68
69		/**
70		* {@inheritdoc}
71		*
72		* @return string
73		*/
74	6	public function getSql()
75		{
76	6	$query = "SELECT " . implode(', ', $this->sqlParts['select']) . " FROM ";
77
78	6	$fromClauses = array();
79
80		// Loop through all FROM clauses
81	6	foreach ($this->sqlParts['from'] as $from) {
82	6	$fromClause = $from['table'] . ' ' . $from['alias']
83	6	. $this->getSQLForJoins($from['alias']);
84
85	6	$fromClauses[$from['alias']] = $fromClause;
86		}
87
88	6	$query .= join(', ', $fromClauses)
89	6	. ($this->sqlParts['where'] !== null ? " WHERE " . ((string) $this->sqlParts['where']) : "")
90	6	. ($this->sqlParts['groupBy'] ? " GROUP BY " . join(", ", $this->sqlParts['groupBy']) : "")
91	6	. ($this->sqlParts['having'] !== null ? " HAVING " . ((string) $this->sqlParts['having']) : "")
92	6	. ($this->sqlParts['orderBy'] ? " ORDER BY " . join(", ", $this->sqlParts['orderBy']) : "")
93	6	. ($this->limit ? " LIMIT ". $this->limit ." OFFSET ". $this->offset : "")
94		;
95
96	6	return $query;
97		}
98
99		/**
100		* Specifies an item that is to be returned in the query result
101		* Replaces any previously specified selections, if any
102		*
103		* Example
104		* <code>
105		* $sb = new Select();
106		* $sb
107		* ->select('u.id', 'p.id')
108		* ->from('users', 'u')
109		* ->leftJoin('u', 'phone', 'p', 'u.id = p.user_id');
110		* </code>
111		*
112		* @param string[] $select the selection expressions
113		* @return Select instance
114		*/
115	7	public function select(...$select)
116		{
117	7	return $this->addQueryPart('select', $select, false);
118		}
119
120		/**
121		* Adds an item that is to be returned in the query result.
122		*
123		* Example
124		* <code>
125		* $sb = new Select();
126		* $sb
127		* ->select('u.id')
128		* ->addSelect('p.id')
129		* ->from('users', 'u')
130		* ->leftJoin('u', 'phone', 'u.id = p.user_id');
131		* </code>
132		*
133		* @param string $select the selection expression
134		* @return Select instance
135		*/
136	1	public function addSelect($select)
137		{
138	1	return $this->addQueryPart('select', $select, true);
139		}
140
141		/**
142		* Creates and adds a join to the query
143		*
144		* Example
145		* <code>
146		* $sb = new Select();
147		* $sb
148		* ->select('u.name')
149		* ->from('users', 'u')
150		* ->join('u', 'phone', 'p', 'p.is_primary = 1');
151		* </code>
152		*
153		* @param string $fromAlias the alias that points to a from clause
154		* @param string $join the table name to join
155		* @param string $alias the alias of the join table
156		* @param string $condition the condition for the join
157		* @return Select instance
158		*/
159	2	public function join($fromAlias, $join, $alias, $condition = null)
160		{
161	2	return $this->innerJoin($fromAlias, $join, $alias, $condition);
162		}
163
164		/**
165		* Creates and adds a join to the query
166		*
167		* Example
168		* <code>
169		* $sb = new Select();
170		* $sb
171		* ->select('u.name')
172		* ->from('users', 'u')
173		* ->innerJoin('u', 'phone', 'p', 'p.is_primary = 1');
174		* </code>
175		*
176		* @param string $fromAlias the alias that points to a from clause
177		* @param string $join the table name to join
178		* @param string $alias the alias of the join table
179		* @param string $condition the condition for the join
180		* @return Select instance
181		*/
182	2	public function innerJoin($fromAlias, $join, $alias, $condition = null)
183		{
184	2	$this->aliases[] = $alias;
185
186	2	return $this->addQueryPart(
187	2	'join',
188		array(
189		$fromAlias => array(
190	2	'joinType' => 'inner',
191	2	'joinTable' => $join,
192	2	'joinAlias' => $alias,
193	2	'joinCondition' => $condition
194		)
195		),
196	2	true
197		);
198		}
199
200		/**
201		* Creates and adds a left join to the query.
202		*
203		* Example
204		* <code>
205		* $sb = new Select();
206		* $sb
207		* ->select('u.name')
208		* ->from('users', 'u')
209		* ->leftJoin('u', 'phone', 'p', 'p.is_primary = 1');
210		* </code>
211		*
212		* @param string $fromAlias the alias that points to a from clause
213		* @param string $join the table name to join
214		* @param string $alias the alias of the join table
215		* @param string $condition the condition for the join
216		* @return Select instance
217		*/
218	1	public function leftJoin($fromAlias, $join, $alias, $condition = null)
219		{
220	1	$this->aliases[] = $alias;
221
222	1	return $this->addQueryPart(
223	1	'join',
224		array(
225		$fromAlias => array(
226	1	'joinType' => 'left',
227	1	'joinTable' => $join,
228	1	'joinAlias' => $alias,
229	1	'joinCondition' => $condition
230		)
231		),
232	1	true
233		);
234		}
235
236		/**
237		* Creates and adds a right join to the query.
238		*
239		* Example
240		* <code>
241		* $sb = new Select();
242		* $sb
243		* ->select('u.name')
244		* ->from('users', 'u')
245		* ->rightJoin('u', 'phone', 'p', 'p.is_primary = 1');
246		* </code>
247		*
248		* @param string $fromAlias the alias that points to a from clause
249		* @param string $join the table name to join
250		* @param string $alias the alias of the join table
251		* @param string $condition the condition for the join
252		* @return Select instance
253		*/
254	1	public function rightJoin($fromAlias, $join, $alias, $condition = null)
255		{
256	1	$this->aliases[] = $alias;
257
258	1	return $this->addQueryPart(
259	1	'join',
260		array(
261		$fromAlias => array(
262	1	'joinType' => 'right',
263	1	'joinTable' => $join,
264	1	'joinAlias' => $alias,
265	1	'joinCondition' => $condition
266		)
267		),
268	1	true
269		);
270		}
271
272		/**
273		* Specifies a grouping over the results of the query.
274		* Replaces any previously specified groupings, if any.
275		*
276		* Example
277		* <code>
278		* $sb = new Select();
279		* $sb
280		* ->select('u.name')
281		* ->from('users', 'u')
282		* ->groupBy('u.id');
283		* </code>
284		*
285		* @param string[] $groupBy the grouping expression
286		* @return Select instance
287		*/
288	1	public function groupBy(...$groupBy)
289		{
290	1	if (empty($groupBy)) {
291		return $this;
292		}
293
294	1	return $this->addQueryPart('groupBy', $groupBy, false);
295		}
296
297		/**
298		* Adds a grouping expression to the query.
299		*
300		* Example
301		* <code>
302		* $sb = new Select();
303		* $sb
304		* ->select('u.name')
305		* ->from('users', 'u')
306		* ->groupBy('u.lastLogin');
307		* ->addGroupBy('u.createdAt')
308		* </code>
309		*
310		* @param string[] $groupBy the grouping expression
311		* @return Select instance
312		*/
313	1	public function addGroupBy(...$groupBy)
314		{
315	1	if (empty($groupBy)) {
316		return $this;
317		}
318
319	1	return $this->addQueryPart('groupBy', $groupBy, true);
320		}
321
322		/**
323		* Specifies a restriction over the groups of the query.
324		* Replaces any previous having restrictions, if any
325		*
326		* @param string[] $condition the query restriction predicates
327		* @return Select
328		*/
329	1	public function having(...$condition)
330		{
331	1	$condition = $this->prepareCondition($condition);
332	1	return $this->addQueryPart('having', $condition, false);
333		}
334
335		/**
336		* Adds a restriction over the groups of the query, forming a logical
337		* conjunction with any existing having restrictions
338		*
339		* @param string[] $condition the query restriction predicates
340		* @return Select
341		*/
342	1	public function andHaving(...$condition)
343		{
344	1	$condition = $this->prepareCondition($condition);
345	1	$having = $this->getQueryPart('having');
346
347	1	if ($having instanceof CompositeBuilder && $having->getType() == 'AND') {
348		$having->add($condition);
349		} else {
350	1	$having = new CompositeBuilder([$having, $condition]);
351		}
352
353	1	return $this->addQueryPart('having', $having, false);
354		}
355
356		/**
357		* Adds a restriction over the groups of the query, forming a logical
358		* disjunction with any existing having restrictions
359		*
360		* @param string[] $condition the query restriction predicates
361		* @return Select
362		*/
363	1	public function orHaving(...$condition)
364		{
365	1	$condition = $this->prepareCondition($condition);
366	1	$having = $this->getQueryPart('having');
367
368	1	if ($having instanceof CompositeBuilder && $having->getType() == 'OR') {
369		$having->add($condition);
370		} else {
371	1	$having = new CompositeBuilder([$having, $condition], 'OR');
372		}
373
374	1	return $this->addQueryPart('having', $having, false);
375		}
376
377		/**
378		* Setup offset like a page number, start from 1
379		*
380		* @param integer $page
381		* @return Select
382		* @throws DbException
383		*/
384	1	public function setPage($page = 1)
385		{
386	1	if (!$this->limit) {
387		throw new DbException("Please setup limit for use method `setPage`");
388		}
389	1	$this->offset = $this->limit * ($page - 1);
390	1	return $this;
391		}
392
393		/**
394		* Generate SQL string for JOINs
395		*
396		* @internal
397		* @param string $fromAlias alias of table
398		* @return string
399		*/
400	6	protected function getSQLForJoins($fromAlias)
401		{
402	6	$sql = '';
403
404	6	if (isset($this->sqlParts['join'][$fromAlias])) {
405	4	foreach ($this->sqlParts['join'][$fromAlias] as $join) {
406	4	$sql .= ' ' . strtoupper($join['joinType'])
407	4	. " JOIN " . $join['joinTable'] . ' ' . $join['joinAlias']
408	4	. " ON " . ((string) $join['joinCondition']);
409	4	$sql .= $this->getSQLForJoins($join['joinAlias']);
410		}
411		}
412
413	6	return $sql;
414		}
415		}
416

bluzphp / framework

Pull Request — master (#363)

Select::join() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like