QueryBuilderTrait::rightJoin() - Code Metrics - Inspection of "intermediate for 1.1" - canax/database - Measure and Improve Code Quality continuously with Scrutinizer

Test Failed

Push — master ( ba1fe5...03a684 )

by Mikael

created 2017-08-30 06:05 UTC

QueryBuilderTrait::rightJoin() A

↳ Parent: QueryBuilderTrait

Complexity

Conditions	2
Paths	2

Size

Total Lines	8
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	8
rs	9.4285
c	0
b	0
f	0
cc	2
eloc	4
nc	2
nop	2

<?php

namespace Anax\Database;

use \Anax\Database\Exception\BuildException;

/**
 * Build SQL queries by method calling.
 */
trait QueryBuilderTrait
{
    /**
     * @var $dialect current database dialect used
     * @var $sql     the query built
     * @var $prefix  prefix to attach to all table names
     */
    private $dialect;
    private $sql;
    private $prefix;

    /**
     * @var $columns columns to select
     * @var $from    from part
     * @var $join    join part
     * @var $where   where part
     * @var $groupby group part
     * @var $orderby order part
     * @var $limit   limit part
     * @var $offset  offset part
     */
    private $columns;
    private $from;
    private $join;
    private $where;
    private $groupby;
    private $orderby;
    private $limit;
    private $offset;



    /**
     * Get SQL.
     *
     * @return string with the built sql-query
     */
    public function getSQL()
    {
        if ($this->sql) {
            return $this->sql;
        }
        return $this->build();
    }



    /**
     * Build the SQL query from its parts.
     *
     * @return string as SQL query
     */
    protected function build()
    {
        $sql = "SELECT\n\t"
            . $this->columns . "\n"
            . $this->from . "\n"
            . ($this->join    ? $this->join           : null)
            . ($this->where   ? $this->where . "\n"   : null)
            . ($this->groupby ? $this->groupby . "\n" : null)
            . ($this->orderby ? $this->orderby . "\n" : null)
            . ($this->limit   ? $this->limit . "\n"   : null)
            . ($this->offset  ? $this->offset . "\n"  : null)
            . ";";

        return $sql;
    }



    /**
     * Clear all previous sql-code.
     *
     * @return void
     */
    protected function clear()
    {
        $this->sql      = null;
        $this->columns  = null;
        $this->from     = null;
        $this->join     = null;
        $this->where    = null;
        $this->groupby  = null;
        $this->orderby  = null;
        $this->limit    = null;
        $this->offset   = null;
    }



    /**
     * Set database type/dialect to consider when generating SQL.
     *
     * @param string $dialect representing database type.
     *
     * @return self
     */
    public function setSQLDialect($dialect)
    {
        $this->dialect = $dialect;
        return $this;
    }



    /**
     * Set a table prefix.
     *
     * @param string $prefix to use in front of all tables.
     *
     * @return self
     */
    public function setTablePrefix($prefix)
    {
        $this->prefix = $prefix;
        return $this;
    }



    /**
     * Create a table.
     *
     * @param string $name    the table name.
     * @param array  $columns the columns in the table.
     *
     * @return $this
     */
    public function createTable($name, $columns)
    {
        $cols = null;

        foreach ($columns as $col => $options) {
            $cols .= "\t" . $col . ' ' . implode(' ', $options) . ",\n";
        }
        $cols = substr($cols, 0, -2);

        $this->sql = "CREATE TABLE "
            . $this->prefix
            . $name
            . "\n(\n"
            . $cols
            . "\n);\n";

        if ($this->dialect == 'sqlite') {
            $this->sql = str_replace('auto_increment', '', $this->sql);
        }

        return $this;
    }



    /**
     * Drop a table.
     *
     * @param string $name the table name.
     *
     * @return $this
     */
    public function dropTable($name)
    {
        $this->sql = "DROP TABLE "
            . $this->prefix
            . $name
            . ";\n";

        return $this;
    }



    /**
     * Drop a table if it exists.
     *
     * @param string $name the table name.
     *
     * @return $this
     */
    public function dropTableIfExists($name)
    {
        $this->sql = "DROP TABLE IF EXISTS "
            . $this->prefix
            . $name
            . ";\n";

        return $this;
    }



    /**
     * Build a insert-query.
     *
     * @param string $table   the table name.
     * @param array  $columns to insert och key=>value with columns and values.
     * @param array  $values  to insert or empty if $columns has both
     *                        columns and values.
     *
     * @throws \Anax\Database\BuildException
     *
     * @return self for chaining
     */
    public function insert($table, $columns, $values = null)
    {
        list($columns, $values) = $this->mapColumnsWithValues($columns, $values);

        if (count($columns) !== count($values)) {
            throw new BuildException("Columns does not match values, not equal items.");
        }

        $cols = null;
        $vals = null;

        $max = count($columns);
        for ($i = 0; $i < $max; $i++) {

            $cols .= $columns[$i] . ', ';

            $val = $values[$i];

            if ($val == '?') {
                $vals .= $val . ', ';
            } else {
                $vals .= (is_string($val)
                    ? "'$val'"
                    : $val)
                    . ', ';
            }
        }

        $cols = substr($cols, 0, -2);
        $vals = substr($vals, 0, -2);

        $this->sql = "INSERT INTO "
            . $this->prefix
            . $table
            . "\n\t("
            . $cols
            . ")\n"
            . "\tVALUES\n\t("
            . $vals
            . ");\n";

        return $this;
    }



    /**
     * Build an update-query.
     *
     * @param string $table   the table name.
     * @param array  $columns to update or key=>value with columns and values.
     * @param array  $values  to update or empty if $columns has bot columns and values.
     * @param array  $where   limit which rows are updated.
     *
     * @throws \Anax\Database\BuildException
     *
     * @return void
     */
    public function update($table, $columns, $values = null, $where = null)
    {
        // If $values is string, then move that to $where
        if (is_string($values)) {
            $where = $values;
            $values = null;
        }

        list($columns, $values) = $this->mapColumnsWithValues($columns, $values);

        if (count($columns) != count($values)) {
            throw new BuildException("Columns does not match values, not equal items.");
        }

        $cols = null;
        $max = count($columns);
        
        for ($i = 0; $i < $max; $i++) {

            $cols .= "\t" . $columns[$i] . ' = ';

            $val = $values[$i];
            if ($val == '?') {
                $cols .= $val . ",\n";
            } else {
                $cols .= (is_string($val)
                    ? "'$val'"
                    : $val)
                    . ",\n";
            }
        }

        $cols = substr($cols, 0, -2);

        $this->sql = "UPDATE "
            . $this->prefix
            . $table
            . "\nSET\n"
            . $cols
            . "\nWHERE "
            . $where
            . "\n;\n";
    }



    /**
     * Build a delete-query.
     *
     * @param string $table the table name.
     * @param array  $where limit which rows are updated.
     *
     * @return void
     */
    public function deleteFrom($table, $where = null)
    {
        if (isset($where)) {
            $where = " WHERE " . $where;
        }

        $this->sql = "DELETE FROM "
            . $this->prefix
            . $table
            . $where
            . ";\n";
    }



    /**
     * Build a select-query.
     *
     * @param string $columns which columns to select.
     *
     * @return $this
     */
    public function select($columns = '*')
    {
        $this->clear();
        $this->columns = $columns;

        return $this;
    }



    /**
     * Build the from part.
     *
     * @param string $table name of table.
     *
     * @return $this
     */
    public function from($table)
    {
        $this->from = "FROM " . $this->prefix . $table;

        return $this;
    }



    /**
     * Build the inner join part.
     *
     * @param string $table     name of table.
     * @param string $condition to join.
     *
     * @return $this
     */
    public function join($table, $condition)
    {

        return $this->createJoin($table, $condition, 'INNER');
    }



    /**
     * Build the right join part.
     *
     * @param string $table     name of table.
     * @param string $condition to join.
     *
     * @throws \Anax\Database\BuildException when dialect does not support.
     *
     * @return $this
     */
    public function rightJoin($table, $condition)
    {
        if ($this->dialect == 'sqlite') {
            throw new BuildException("SQLite does not support RIGHT JOIN");
        }

        return $this->createJoin($table, $condition, 'RIGHT OUTER');
    }



    /**
     * Build the left join part.
     *
     * @param string $table     name of table.
     * @param string $condition to join.
     *
     * @return $this
     */
    public function leftJoin($table, $condition)
    {
        return $this->createJoin($table, $condition, 'LEFT OUTER');
    }



    /**
     * Create a inner or outer join.
     *
     * @param string $table     name of table.
     * @param string $condition to join.
     * @param string $type      what type of join to create.
     *
     * @return void
     */
    private function createJoin($table, $condition, $type)
    {
        $this->join .= $type
            . " JOIN " . $this->prefix . $table
            . "\n\tON " . $condition . "\n";

        return $this;
    }



    /**
     * Build the where part.
     *
     * @param string $condition for building the where part of the query.
     *
     * @return $this
     */
    public function where($condition)
    {
        $this->where = "WHERE \n\t(" . $condition . ")";

        return $this;
    }



    /**
     * Build the where part with conditions.
     *
     * @param string $condition for building the where part of the query.
     *
     * @return $this
     */
    public function andWhere($condition)
    {
        $this->where .= "\n\tAND (" . $condition . ")";

        return $this;
    }



    /**
    * Build the group by part.
    *
    * @param string $condition for building the group by part of the query.
    *
    * @return $this
    */
    public function groupBy($condition)
    {
        $this->groupby = "GROUP BY " . $condition;

        return $this;
    }



    /**
    * Build the order by part.
    *
    * @param string $condition for building the where part of the query.
    *
    * @return $this
    */
    public function orderBy($condition)
    {
        $this->orderby = "ORDER BY " . $condition;

        return $this;
    }



    /**
     * Build the LIMIT by part.
     *
     * @param string $condition for building the LIMIT part of the query.
     *
     * @return $this
     */
    public function limit($condition)
    {
        $this->limit = "LIMIT \n\t" . intval($condition);

        return $this;
    }



    /**
     * Build the OFFSET by part.
     *
     * @param string $condition for building the OFFSET part of the query.
     *
     * @return $this
     */
    public function offset($condition)
    {
        $this->offset = "OFFSET \n\t" . intval($condition);

        return $this;
    }



    /**
     * Create a proper column value arrays from incoming $columns and $values.
     *
     * @param array       $columns
     * @param array|null  $values
     *
     * @return array that can be parsed with list($columns, $values)
     */
    public function mapColumnsWithValues($columns, $values)
    {
        // If $values is null, then use $columns to build it up
        if (is_null($values)) {

            if ($this->isAssoc($columns)) {

                // Incoming is associative array, split it up in two
                $values = array_values($columns);
                $columns = array_keys($columns);

            } else {

                // Create an array of '?' to match number of columns
                $max = count($columns);
                for ($i = 0; $i < $max; $i++) {
                    $values[] = '?';
                }
            }
        }

        return [$columns, $values];
    }



    /**
     * Utility to check if array is associative array.
     *
     * http://stackoverflow.com/questions/173400/php-arrays-a-good-way-to-check-if-an-array-is-associative-or-sequential/4254008#4254008
     *
     * @param array $array input array to check.
     *
     * @return boolean true if array is associative array with at least
     *                      one key, else false.
     *
     */
    private function isAssoc($array)
    {
        return (bool) count(array_filter(array_keys($array), 'is_string'));
    }
}


1		<?php
2
3		namespace Anax\Database;
4
5		use \Anax\Database\Exception\BuildException;
6
7		/**
8		* Build SQL queries by method calling.
9		*/
10		trait QueryBuilderTrait
11		{
12		/**
13		* @var $dialect current database dialect used
14		* @var $sql the query built
15		* @var $prefix prefix to attach to all table names
16		*/
17		private $dialect;
18		private $sql;
19		private $prefix;
20
21		/**
22		* @var $columns columns to select
23		* @var $from from part
24		* @var $join join part
25		* @var $where where part
26		* @var $groupby group part
27		* @var $orderby order part
28		* @var $limit limit part
29		* @var $offset offset part
30		*/
31		private $columns;
32		private $from;
33		private $join;
34		private $where;
35		private $groupby;
36		private $orderby;
37		private $limit;
38		private $offset;
39
40
41
42		/**
43		* Get SQL.
44		*
45		* @return string with the built sql-query
46		*/
47		public function getSQL()
48		{
49		if ($this->sql) {
50		return $this->sql;
51		}
52		return $this->build();
53		}
54
55
56
57		/**
58		* Build the SQL query from its parts.
59		*
60		* @return string as SQL query
61		*/
62		protected function build()
63		{
64		$sql = "SELECT\n\t"
65		. $this->columns . "\n"
66		. $this->from . "\n"
67		. ($this->join ? $this->join : null)
68		. ($this->where ? $this->where . "\n" : null)
69		. ($this->groupby ? $this->groupby . "\n" : null)
70		. ($this->orderby ? $this->orderby . "\n" : null)
71		. ($this->limit ? $this->limit . "\n" : null)
72		. ($this->offset ? $this->offset . "\n" : null)
73		. ";";
74
75		return $sql;
76		}
77
78
79
80		/**
81		* Clear all previous sql-code.
82		*
83		* @return void
84		*/
85		protected function clear()
86		{
87		$this->sql = null;
88		$this->columns = null;
89		$this->from = null;
90		$this->join = null;
91		$this->where = null;
92		$this->groupby = null;
93		$this->orderby = null;
94		$this->limit = null;
95		$this->offset = null;
96		}
97
98
99
100		/**
101		* Set database type/dialect to consider when generating SQL.
102		*
103		* @param string $dialect representing database type.
104		*
105		* @return self
106		*/
107		public function setSQLDialect($dialect)
108		{
109		$this->dialect = $dialect;
110		return $this;
111		}
112
113
114
115		/**
116		* Set a table prefix.
117		*
118		* @param string $prefix to use in front of all tables.
119		*
120		* @return self
121		*/
122		public function setTablePrefix($prefix)
123		{
124		$this->prefix = $prefix;
125		return $this;
126		}
127
128
129
130		/**
131		* Create a table.
132		*
133		* @param string $name the table name.
134		* @param array $columns the columns in the table.
135		*
136		* @return $this
137		*/
138		public function createTable($name, $columns)
139		{
140		$cols = null;
141
142		foreach ($columns as $col => $options) {
143		$cols .= "\t" . $col . ' ' . implode(' ', $options) . ",\n";
144		}
145		$cols = substr($cols, 0, -2);
146
147		$this->sql = "CREATE TABLE "
148		. $this->prefix
149		. $name
150		. "\n(\n"
151		. $cols
152		. "\n);\n";
153
154		if ($this->dialect == 'sqlite') {
155		$this->sql = str_replace('auto_increment', '', $this->sql);
156		}
157
158		return $this;
159		}
160
161
162
163		/**
164		* Drop a table.
165		*
166		* @param string $name the table name.
167		*
168		* @return $this
169		*/
170		public function dropTable($name)
171		{
172		$this->sql = "DROP TABLE "
173		. $this->prefix
174		. $name
175		. ";\n";
176
177		return $this;
178		}
179
180
181
182		/**
183		* Drop a table if it exists.
184		*
185		* @param string $name the table name.
186		*
187		* @return $this
188		*/
189		public function dropTableIfExists($name)
190		{
191		$this->sql = "DROP TABLE IF EXISTS "
192		. $this->prefix
193		. $name
194		. ";\n";
195
196		return $this;
197		}
198
199
200
201		/**
202		* Build a insert-query.
203		*
204		* @param string $table the table name.
205		* @param array $columns to insert och key=>value with columns and values.
206		* @param array $values to insert or empty if $columns has both
207		* columns and values.
208		*
209		* @throws \Anax\Database\BuildException
210		*
211		* @return self for chaining
212		*/
213		public function insert($table, $columns, $values = null)
214		{
215		list($columns, $values) = $this->mapColumnsWithValues($columns, $values);
216
217		if (count($columns) !== count($values)) {
218		throw new BuildException("Columns does not match values, not equal items.");
219		}
220
221		$cols = null;
222		$vals = null;
223
224		$max = count($columns);
225	View Code Duplication	for ($i = 0; $i < $max; $i++) {
		0 ignored issues – show Duplication introduced 2017-08-30 06:06 UTC by Report Bug Copy Issue Report This code seems to be duplicated across your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
226		$cols .= $columns[$i] . ', ';
227
228		$val = $values[$i];
229
230		if ($val == '?') {
231		$vals .= $val . ', ';
232		} else {
233		$vals .= (is_string($val)
234		? "'$val'"
235		: $val)
236		. ', ';
237		}
238		}
239
240		$cols = substr($cols, 0, -2);
241		$vals = substr($vals, 0, -2);
242
243		$this->sql = "INSERT INTO "
244		. $this->prefix
245		. $table
246		. "\n\t("
247		. $cols
248		. ")\n"
249		. "\tVALUES\n\t("
250		. $vals
251		. ");\n";
252
253		return $this;
254		}
255
256
257
258		/**
259		* Build an update-query.
260		*
261		* @param string $table the table name.
262		* @param array $columns to update or key=>value with columns and values.
263		* @param array $values to update or empty if $columns has bot columns and values.
264		* @param array $where limit which rows are updated.
265		*
266		* @throws \Anax\Database\BuildException
267		*
268		* @return void
269		*/
270		public function update($table, $columns, $values = null, $where = null)
271		{
272		// If $values is string, then move that to $where
273		if (is_string($values)) {
274		$where = $values;
275		$values = null;
276		}
277
278		list($columns, $values) = $this->mapColumnsWithValues($columns, $values);
279
280		if (count($columns) != count($values)) {
281		throw new BuildException("Columns does not match values, not equal items.");
282		}
283
284		$cols = null;
285		$max = count($columns);
286
287	View Code Duplication	for ($i = 0; $i < $max; $i++) {
		0 ignored issues – show Duplication introduced 2017-08-30 06:06 UTC by Report Bug Copy Issue Report This code seems to be duplicated across your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
288		$cols .= "\t" . $columns[$i] . ' = ';
289
290		$val = $values[$i];
291		if ($val == '?') {
292		$cols .= $val . ",\n";
293		} else {
294		$cols .= (is_string($val)
295		? "'$val'"
296		: $val)
297		. ",\n";
298		}
299		}
300
301		$cols = substr($cols, 0, -2);
302
303		$this->sql = "UPDATE "
304		. $this->prefix
305		. $table
306		. "\nSET\n"
307		. $cols
308		. "\nWHERE "
309		. $where
310		. "\n;\n";
311		}
312
313
314
315		/**
316		* Build a delete-query.
317		*
318		* @param string $table the table name.
319		* @param array $where limit which rows are updated.
320		*
321		* @return void
322		*/
323		public function deleteFrom($table, $where = null)
324		{
325		if (isset($where)) {
326		$where = " WHERE " . $where;
327		}
328
329		$this->sql = "DELETE FROM "
330		. $this->prefix
331		. $table
332		. $where
333		. ";\n";
334		}
335
336
337
338		/**
339		* Build a select-query.
340		*
341		* @param string $columns which columns to select.
342		*
343		* @return $this
344		*/
345		public function select($columns = '*')
346		{
347		$this->clear();
348		$this->columns = $columns;
349
350		return $this;
351		}
352
353
354
355		/**
356		* Build the from part.
357		*
358		* @param string $table name of table.
359		*
360		* @return $this
361		*/
362		public function from($table)
363		{
364		$this->from = "FROM " . $this->prefix . $table;
365
366		return $this;
367		}
368
369
370
371		/**
372		* Build the inner join part.
373		*
374		* @param string $table name of table.
375		* @param string $condition to join.
376		*
377		* @return $this
378		*/
379		public function join($table, $condition)
380		{
381
382		return $this->createJoin($table, $condition, 'INNER');
383		}
384
385
386
387		/**
388		* Build the right join part.
389		*
390		* @param string $table name of table.
391		* @param string $condition to join.
392		*
393		* @throws \Anax\Database\BuildException when dialect does not support.
394		*
395		* @return $this
396		*/
397		public function rightJoin($table, $condition)
398		{
399		if ($this->dialect == 'sqlite') {
400		throw new BuildException("SQLite does not support RIGHT JOIN");
401		}
402
403		return $this->createJoin($table, $condition, 'RIGHT OUTER');
404		}
405
406
407
408		/**
409		* Build the left join part.
410		*
411		* @param string $table name of table.
412		* @param string $condition to join.
413		*
414		* @return $this
415		*/
416		public function leftJoin($table, $condition)
417		{
418		return $this->createJoin($table, $condition, 'LEFT OUTER');
419		}
420
421
422
423		/**
424		* Create a inner or outer join.
425		*
426		* @param string $table name of table.
427		* @param string $condition to join.
428		* @param string $type what type of join to create.
429		*
430		* @return void
431		*/
432		private function createJoin($table, $condition, $type)
433		{
434		$this->join .= $type
435		. " JOIN " . $this->prefix . $table
436		. "\n\tON " . $condition . "\n";
437
438		return $this;
439		}
440
441
442
443		/**
444		* Build the where part.
445		*
446		* @param string $condition for building the where part of the query.
447		*
448		* @return $this
449		*/
450		public function where($condition)
451		{
452		$this->where = "WHERE \n\t(" . $condition . ")";
453
454		return $this;
455		}
456
457
458
459		/**
460		* Build the where part with conditions.
461		*
462		* @param string $condition for building the where part of the query.
463		*
464		* @return $this
465		*/
466		public function andWhere($condition)
467		{
468		$this->where .= "\n\tAND (" . $condition . ")";
469
470		return $this;
471		}
472
473
474
475		/**
476		* Build the group by part.
477		*
478		* @param string $condition for building the group by part of the query.
479		*
480		* @return $this
481		*/
482		public function groupBy($condition)
483		{
484		$this->groupby = "GROUP BY " . $condition;
485
486		return $this;
487		}
488
489
490
491		/**
492		* Build the order by part.
493		*
494		* @param string $condition for building the where part of the query.
495		*
496		* @return $this
497		*/
498		public function orderBy($condition)
499		{
500		$this->orderby = "ORDER BY " . $condition;
501
502		return $this;
503		}
504
505
506
507		/**
508		* Build the LIMIT by part.
509		*
510		* @param string $condition for building the LIMIT part of the query.
511		*
512		* @return $this
513		*/
514		public function limit($condition)
515		{
516		$this->limit = "LIMIT \n\t" . intval($condition);
517
518		return $this;
519		}
520
521
522
523		/**
524		* Build the OFFSET by part.
525		*
526		* @param string $condition for building the OFFSET part of the query.
527		*
528		* @return $this
529		*/
530		public function offset($condition)
531		{
532		$this->offset = "OFFSET \n\t" . intval($condition);
533
534		return $this;
535		}
536
537
538
539		/**
540		* Create a proper column value arrays from incoming $columns and $values.
541		*
542		* @param array $columns
543		* @param array\|null $values
544		*
545		* @return array that can be parsed with list($columns, $values)
546		*/
547		public function mapColumnsWithValues($columns, $values)
548		{
549		// If $values is null, then use $columns to build it up
550		if (is_null($values)) {
551
552		if ($this->isAssoc($columns)) {
553
554		// Incoming is associative array, split it up in two
555		$values = array_values($columns);
556		$columns = array_keys($columns);
557
558		} else {
559
560		// Create an array of '?' to match number of columns
561		$max = count($columns);
562		for ($i = 0; $i < $max; $i++) {
563		$values[] = '?';
564		}
565		}
566		}
567
568		return [$columns, $values];
569		}
570
571
572
573		/**
574		* Utility to check if array is associative array.
575		*
576		* http://stackoverflow.com/questions/173400/php-arrays-a-good-way-to-check-if-an-array-is-associative-or-sequential/4254008#4254008
577		*
578		* @param array $array input array to check.
579		*
580		* @return boolean true if array is associative array with at least
581		* one key, else false.
582		*
583		*/
584		private function isAssoc($array)
585		{
586		return (bool) count(array_filter(array_keys($array), 'is_string'));
587		}
588		}
589

canax / database

Push — master ( ba1fe5...03a684 )

QueryBuilderTrait::rightJoin() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like