Select::fetchAll() - Code Metrics - Inspection of "Report change into bfw on Monolog" - bulton-fr/bfw-sql - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — 2.0 ( ada449...a2425b )

by Vermeulen

created 2018-07-28 09:29 UTC

Select::fetchAll() A

↳ Parent: Select

Complexity

Conditions	2
Paths	2

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	8
rs	10
c	0
b	0
f	0
cc	2
nc	2
nop	0

<?php

namespace BfwSql\Actions;

use \Exception;
use \PDO;

/**
 * Class to write SELECT queries
 * 
 * @package bfw-sql
 * @author Vermeulen Maxime <[email protected]>
 * @version 2.0
 */
class Select extends AbstractActions
{
    /**
     * @const ERR_TABLE_INFOS_BAD_FORMAT Exception code if table structure is
     * not correct.
     */
    const ERR_TABLE_INFOS_BAD_FORMAT = 2304001;
    
    /**
     * @const ERR_SUB_QUERY_FORMAT Exception code if the sub-query has not the
     * correct format (string or AbstractAction object).
     */
    const ERR_SUB_QUERY_FORMAT = 2304002;
    
    /**
     * @const ERR_GENERATE_FROM_MISSING_TABLE_NAME Exception code if the user
     * try to generate a request without table name.
     */
    const ERR_GENERATE_FROM_MISSING_TABLE_NAME = 2304003;
    
    /**
     * @var string $returnType PHP Type used for return result
     */
    protected $returnType = '';
    
    /**
     * @var object $mainTable Informations about main table. Used for FROM part
     */
    protected $mainTable;
    
    /**
     * @var array $subQueries All sub-queries
     */
    protected $subQueries = [];
    
    /**
     * @var array $join List of all INNER JOIN
     */
    protected $join = [];
    
    /**
     * @var array $joinLeft List of all LEFT JOIN
     */
    protected $joinLeft = [];
    
    /**
     * @var array $joinRight List of all RIGHT JOIN
     */
    protected $joinRight = [];
    
    /**
     * @var string[] $order All columns used for ORDER BY part
     */
    protected $order = [];
    
    /**
     * @var string $limit The LIMIT part
     */
    protected $limit = '';
    
    /**
     * @var string[] $group The GROUP BY part
     */
    protected $group = [];
    
    /**
     * Constructor
     * 
     * @param \BfwSql\SqlConnect $sqlConnect Instance of SGBD connexion
     * @param string             $returnType PHP type used for return result
     */
    public function __construct(\BfwSql\SqlConnect $sqlConnect, $returnType)
    {
        parent::__construct($sqlConnect);
        $this->returnType = $returnType;
    }
    
    /**
     * Getter accessor to returnType property
     * 
     * @return string
     */
    public function getReturnType()
    {
        return $this->returnType;
    }

    /**
     * Getter accessor to mainTable property
     * 
     * @return \stdClass
     */
    public function getMainTable()
    {
        return $this->mainTable;
    }

    /**
     * Getter accessor to subQueries property
     * 
     * @return array
     */
    public function getSubQueries()
    {
        return $this->subQueries;
    }

    /**
     * Getter accessor to join property
     * 
     * @return array
     */
    public function getJoin()
    {
        return $this->join;
    }

    /**
     * Getter accessor to joinLeft property
     * 
     * @return array
     */
    public function getJoinLeft()
    {
        return $this->joinLeft;
    }

    /**
     * Getter accessor to joinRight property
     * 
     * @return array
     */
    public function getJoinRight()
    {
        return $this->joinRight;
    }

    /**
     * Getter accessor to order property
     * 
     * @return string[]
     */
    public function getOrder()
    {
        return $this->order;
    }

    /**
     * Getter accessor to limit property
     * 
     * @return string
     */
    public function getLimit()
    {
        return $this->limit;
    }

    /**
     * Getter accessor to group property
     * 
     * @return string[]
     */
    public function getGroup()
    {
        return $this->group;
    }
    
    /**
     * Define object used to save informations about a table
     * This object will be used to write query
     * 
     * @param string|array $table Tables informations
     * 
     * @return \stdClass
     */
    protected function obtainTableInfos($table)
    {
        if (!is_array($table) && !is_string($table)) {
            throw new Exception(
                'Table information is not in the right format.',
                self::ERR_TABLE_INFOS_BAD_FORMAT
            );
        }
        
        if (is_array($table)) {
            $tableName = reset($table);
            $shortcut  = key($table);
        } else {
            $tableName = $table;
            $shortcut  = null;
        }
        
        $prefix = $this->sqlConnect->getConnectionInfos()->tablePrefix;
        
        return (object) [
            'tableName' => $prefix.$tableName,
            'shortcut'  => $shortcut
        ];
    }
    
    /**
     * Add columns for select
     * 
     * @param array|string $columns   Columns to add
     * @param string       $tableName Table name for will be columns added
     * 
     * @return void
     */
    protected function addColumnsForSelect($columns, $tableName)
    {
        if (!is_array($columns)) {
            $columns = (array) $columns;
        }
        
        foreach ($columns as $columnShortcut => $columnName) {
            //If value is a sql function or keyword, not add quote
            if (
                strpos($columnName, ' ') === false &&
                strpos($columnName, '(') === false
            ) {
                //Add quote only if a column has been declared
                if ($columnName !== '*') {
                    $columnName = '`'.$columnName.'`';
                }
                
                $columnName = '`'.$tableName.'`.'.$columnName;
            }
            
            //If a column shortcut is declared
            if (is_string($columnShortcut)) {
                $this->columns[] = (object) [
                    'column'   => $columnName,
                    'shortcut' => $columnShortcut
                ];
            } else {
                $this->columns[] = (object) [
                    'column'   => $columnName,
                    'shortcut' => null
                ];
            }
        }
    }
    
    /**
     * Declare information for FROM part and column will be get for main table
     * 
     * @param string|array $table   Table name.
     *  It can be an array if a table shortcut is declared.
     *  In array mode, the format is ['asValue' => 'tableName']
     * @param string|array $columns (default: "*") Columns will be get for
     *  the table declared in first argument
     * 
     * @return \BfwSql\SqlSelect
     */
    public function from($table, $columns = '*')
    {
        $this->mainTable = $this->obtainTableInfos($table);
        
        $tableName = $this->mainTable->tableName;
        if ($this->mainTable->shortcut !== null) {
            $tableName = $this->mainTable->shortcut;
        }
        
        $this->addColumnsForSelect($columns, $tableName);
        
        return $this;
    }
    
    /**
     * Add a sub-query in the SELECT part on the request
     * 
     * @param \BfwSql\Actions\AbstractActions|string $subRequest The sub-request
     * @param string  $shortcut The shortcut to use for
     *  this query in SELECT part
     * 
     * @return \BfwSql\SqlSelect
     */
    public function subQuery($subRequest, $shortcut)
    {
        if (
            is_object($subRequest)
            && $subRequest instanceof \BfwSql\Actions\AbstractActions
        ) {
            $subQuery = $subRequest->assemble();
        } elseif (is_string($subRequest)) {
            $subQuery = $subRequest;
        } else {
            throw new Exception(
                'subRequest passed in parameters must be an instance of '
                .'\BfwSql\Actions\AbstractActions or a string.',
                self::ERR_SUB_QUERY_FORMAT
            );
        }
        
        $this->subQueries[] = (object) [
            'query'    => $subQuery,
            'shortcut' => $shortcut
        ];
        
        return $this;
    }
    
    /**
     * Add a (inner|left|right) join to the request
     * 
     * @param string       $joinPropertyName The name of the property in this
     *  class where the join is add
     * @param string|array $table            Name of the table concerned by
     * the join. Or an array with the table shortcut in key.
     * @param string       $joinOn           SQL part "ON" for this join
     * @param string|array $joinColumns      Columns from the table joined to
     *  add in the SELECT part of the request
     * 
     * @return \BfwSql\SqlSelect
     */
    protected function createJoin(
        $joinPropertyName,
        $table,
        $joinOn,
        $joinColumns
    ) {
        $tableInfos     = $this->obtainTableInfos($table);
        $tableInfos->on = $joinOn;
        
        $tableName = $tableInfos->tableName;
        if ($tableInfos->shortcut !== null) {
            $tableName = $tableInfos->shortcut;
        }
        
        $this->{$joinPropertyName}[] = $tableInfos;
        $this->addColumnsForSelect($joinColumns, $tableName);
        
        return $this;
    }
    
    /**
     * Add a INNER JOIN to the request
     * 
     * @param string|array $table       Name of the table concerned by the
     *  join. Or an array with the table shortcut in key.
     * @param string       $joinOn      SQL part "ON" for this join
     * @param string|array $joinColumns Columns from the table joined to add
     *  in the SELECT part of the request
     * 
     * @return \BfwSql\SqlSelect
     */
    public function join($table, $joinOn, $joinColumns = '*')
    {
        return $this->createJoin('join', $table, $joinOn, $joinColumns);
    }
    
    /**
     * Add a LEFT JOIN to the request
     * 
     * @param string|array $table       Name of the table concerned by the
     *  join. Or an array with the table shortcut in key.
     * @param string       $joinOn      SQL part "ON" for this join
     * @param string|array $joinColumns Columns from the table joined to add
     *  in the SELECT part of the request
     * 
     * @return \BfwSql\SqlSelect
     */
    public function joinLeft($table, $joinOn, $joinColumns = '*')
    {
        return $this->createJoin('joinLeft', $table, $joinOn, $joinColumns);
    }
    
    /**
     * Add a RIGHT JOIN to the request
     * 
     * @param string|array $table       Name of the table concerned by the
     *  join. Or an array with the table shortcut in key.
     * @param string       $joinOn      SQL part "ON" for this join
     * @param string|array $joinColumns Columns from the table joined to add
     *  in the SELECT part of the request
     * 
     * @return \BfwSql\SqlSelect
     */
    public function joinRight($table, $joinOn, $joinColumns = '*')
    {
        return $this->createJoin('joinRight', $table, $joinOn, $joinColumns);
    }
    
    /**
     * Add a order condition to the request for the ORDER BY part
     * 
     * @param string $condition The new condition
     * 
     * @return \BfwSql\SqlSelect
     */
    public function order($condition)
    {
        $this->order[] = (string) $condition;
        return $this;
    }
    
    /**
     * Add information about the LIMIT part in request
     * 
     * @param array|integer $limit If it's a integer, the number of row to
     *  return. If an array, the format is [numberToStart, numberOfRowToReturn]
     * 
     * @return \BfwSql\SqlSelect
     */
    public function limit($limit)
    {
        if (!is_array($limit)) {
            $limit = (array) $limit;
        }
        
        if (isset($limit[1])) {
            $this->limit = $limit[0].', '.$limit[1];
        } else {
            $this->limit = (string) $limit[0];
        }
        
        return $this;
    }
    
    /**
     * Add a GROUP BY part to the request
     * 
     * @param string $condition The condition to use in GROUP BY
     * 
     * @return \BfwSql\SqlSelect
     */
    public function group($condition)
    {
        $this->group[] = $condition;
        return $this;
    }
    
    /**
     * Return the PDO constant for the returnType declared
     * 
     * @return integer
     */
    protected function obtainPdoFetchType()
    {
        if ($this->returnType === 'object') {
            return PDO::FETCH_OBJ;
        }
        
        return PDO::FETCH_ASSOC;
    }
    
    /**
     * Fetch one row of the result
     * 
     * @return mixed
     */
    public function fetchRow()
    {
        $req = $this->execute();
        return $req->fetch($this->obtainPdoFetchType());
    }
    
    /**
     * Fetch all rows returned by the request
     * 
     * @return generator
     */
    public function fetchAll()
    {
        $request = $this->execute(); //throw an Exception if error
        
        while ($row = $request->fetch($this->obtainPdoFetchType())) {
            yield $row;
        }
    }
    
    /**
     * {@inheritdoc}
     */
    protected function assembleRequest()
    {
        $this->assembledRequest = 'SELECT '.$this->generateSelect()
            .' FROM '.$this->generateFrom()
            .$this->generateJoin('join')
            .$this->generateJoin('joinLeft')
            .$this->generateJoin('joinRight')
            .$this->generateWhere()
            .$this->generateGroupBy()
            .$this->generateOrderBy()
            .$this->generateLimit();
    }
    
    /**
     * Write the SELECT part of the request
     * 
     * @return string
     */
    protected function generateSelect()
    {
        $select = '';
        foreach ($this->columns as $columnInfos) {
            if ($select != '') {
                $select .= ', ';
            }
            
            $select .= $columnInfos->column;
            if ($columnInfos->shortcut !== null) {
                $select .= ' AS `'.$columnInfos->shortcut.'`';
            }
        }
        
        foreach ($this->subQueries as $subQueryInfos) {
            if ($select != '') {
                $select .= ', ';
            }

            $select .= '('.$subQueryInfos->query.')'
                .' AS `'.$subQueryInfos->shortcut.'`';
        }
        
        return $select;
    }
    
    /**
     * Write the FROM part of the request
     * 
     * @return string
     */
    protected function generateFrom()
    {
        if ($this->mainTable === null) {
            throw new Exception(
                'The "FROM" part of the request can not be generate because'
                .' the table name as not been declared.',
                self::ERR_GENERATE_FROM_MISSING_TABLE_NAME
            );
        }
        
        $from = '`'.$this->mainTable->tableName.'`';
        
        if ($this->mainTable->shortcut !== null) {
            $from .= ' AS `'.$this->mainTable->shortcut.'`';
        }
        
        return $from;
    }
    
    /**
     * Write a (inner|left|right) join in the request
     * 
     * @param string $joinProperty The join property name
     * 
     * @return string
     */
    protected function generateJoin($joinProperty)
    {
        $join = '';
        if (count($this->{$joinProperty}) === 0) {
            return $join;
        }
        
        if ($joinProperty == 'join') {
            $joinSqlName = ' INNER JOIN ';
        } elseif ($joinProperty == 'joinLeft') {
            $joinSqlName = ' LEFT JOIN ';
        } elseif ($joinProperty == 'joinRight') {
            $joinSqlName = ' RIGHT JOIN ';
        }

        foreach ($this->{$joinProperty} as $joinInfos) {
            $join .= $joinSqlName.'`'.$joinInfos->tableName.'`';
            if ($joinInfos->shortcut !== null) {
                $join .= ' AS `'.$joinInfos->shortcut.'`';
            }

            $join .= ' ON '.$joinInfos->on;
        }
        
        return $join;
    }
    
    /**
     * Write the ORDER BY part for the request
     * 
     * @return string
     */
    protected function generateOrderBy()
    {
        if (count($this->order) === 0) {
            return '';
        }
        
        $order = ' ORDER BY ';
        foreach ($this->order as $orderCondition) {
            if ($order != ' ORDER BY ') {
                $order .= ', ';
            }
            
            $order .= $orderCondition;
        }
        
        return $order;
    }
    
    /**
     * Write the GRUOP BY part for the request
     * 
     * @return string
     */
    protected function generateGroupBy()
    {
        if (count($this->group) === 0) {
            return '';
        }
        
        $group = ' GROUP BY ';
        foreach ($this->group as $groupCondition) {
            if ($group != ' GROUP BY ') {
                $group .= ', ';
            }
            
            $group .= $groupCondition;
        }
        
        return $group;
    }
    
    /**
     * Write the LIMIT part for the request
     * 
     * @return string
     */
    protected function generateLimit()
    {
        $limit = '';
        if ($this->limit !== '') {
            $limit = ' LIMIT '.$this->limit;
        }
        
        return $limit;
    }
} 


1			<?php
2
3			namespace BfwSql\Actions;
4
5			use \Exception;
6			use \PDO;
7
8			/**
9			* Class to write SELECT queries
10			*
11			* @package bfw-sql
12			* @author Vermeulen Maxime <[email protected]>
13			* @version 2.0
14			*/
15			class Select extends AbstractActions
16			{
17			/**
18			* @const ERR_TABLE_INFOS_BAD_FORMAT Exception code if table structure is
19			* not correct.
20			*/
21			const ERR_TABLE_INFOS_BAD_FORMAT = 2304001;
22
23			/**
24			* @const ERR_SUB_QUERY_FORMAT Exception code if the sub-query has not the
25			* correct format (string or AbstractAction object).
26			*/
27			const ERR_SUB_QUERY_FORMAT = 2304002;
28
29			/**
30			* @const ERR_GENERATE_FROM_MISSING_TABLE_NAME Exception code if the user
31			* try to generate a request without table name.
32			*/
33			const ERR_GENERATE_FROM_MISSING_TABLE_NAME = 2304003;
34
35			/**
36			* @var string $returnType PHP Type used for return result
37			*/
38			protected $returnType = '';
39
40			/**
41			* @var object $mainTable Informations about main table. Used for FROM part
42			*/
43			protected $mainTable;
44
45			/**
46			* @var array $subQueries All sub-queries
47			*/
48			protected $subQueries = [];
49
50			/**
51			* @var array $join List of all INNER JOIN
52			*/
53			protected $join = [];
54
55			/**
56			* @var array $joinLeft List of all LEFT JOIN
57			*/
58			protected $joinLeft = [];
59
60			/**
61			* @var array $joinRight List of all RIGHT JOIN
62			*/
63			protected $joinRight = [];
64
65			/**
66			* @var string[] $order All columns used for ORDER BY part
67			*/
68			protected $order = [];
69
70			/**
71			* @var string $limit The LIMIT part
72			*/
73			protected $limit = '';
74
75			/**
76			* @var string[] $group The GROUP BY part
77			*/
78			protected $group = [];
79
80			/**
81			* Constructor
82			*
83			* @param \BfwSql\SqlConnect $sqlConnect Instance of SGBD connexion
84			* @param string $returnType PHP type used for return result
85			*/
86			public function __construct(\BfwSql\SqlConnect $sqlConnect, $returnType)
87			{
88			parent::__construct($sqlConnect);
89			$this->returnType = $returnType;
90			}
91
92			/**
93			* Getter accessor to returnType property
94			*
95			* @return string
96			*/
97			public function getReturnType()
98			{
99			return $this->returnType;
100			}
101
102			/**
103			* Getter accessor to mainTable property
104			*
105			* @return \stdClass
106			*/
107			public function getMainTable()
108			{
109			return $this->mainTable;
110			}
111
112			/**
113			* Getter accessor to subQueries property
114			*
115			* @return array
116			*/
117			public function getSubQueries()
118			{
119			return $this->subQueries;
120			}
121
122			/**
123			* Getter accessor to join property
124			*
125			* @return array
126			*/
127			public function getJoin()
128			{
129			return $this->join;
130			}
131
132			/**
133			* Getter accessor to joinLeft property
134			*
135			* @return array
136			*/
137			public function getJoinLeft()
138			{
139			return $this->joinLeft;
140			}
141
142			/**
143			* Getter accessor to joinRight property
144			*
145			* @return array
146			*/
147			public function getJoinRight()
148			{
149			return $this->joinRight;
150			}
151
152			/**
153			* Getter accessor to order property
154			*
155			* @return string[]
156			*/
157			public function getOrder()
158			{
159			return $this->order;
160			}
161
162			/**
163			* Getter accessor to limit property
164			*
165			* @return string
166			*/
167			public function getLimit()
168			{
169			return $this->limit;
170			}
171
172			/**
173			* Getter accessor to group property
174			*
175			* @return string[]
176			*/
177			public function getGroup()
178			{
179			return $this->group;
180			}
181
182			/**
183			* Define object used to save informations about a table
184			* This object will be used to write query
185			*
186			* @param string\|array $table Tables informations
187			*
188			* @return \stdClass
189			*/
190			protected function obtainTableInfos($table)
191			{
192			if (!is_array($table) && !is_string($table)) {
193			throw new Exception(
194			'Table information is not in the right format.',
195			self::ERR_TABLE_INFOS_BAD_FORMAT
196			);
197			}
198
199			if (is_array($table)) {
200			$tableName = reset($table);
201			$shortcut = key($table);
202			} else {
203			$tableName = $table;
204			$shortcut = null;
205			}
206
207			$prefix = $this->sqlConnect->getConnectionInfos()->tablePrefix;
208
209			return (object) [
210			'tableName' => $prefix.$tableName,
211			'shortcut' => $shortcut
212			];
213			}
214
215			/**
216			* Add columns for select
217			*
218			* @param array\|string $columns Columns to add
219			* @param string $tableName Table name for will be columns added
220			*
221			* @return void
222			*/
223			protected function addColumnsForSelect($columns, $tableName)
224			{
225			if (!is_array($columns)) {
226			$columns = (array) $columns;
227			}
228
229			foreach ($columns as $columnShortcut => $columnName) {
230			//If value is a sql function or keyword, not add quote
231			if (
232			strpos($columnName, ' ') === false &&
233			strpos($columnName, '(') === false
234			) {
235			//Add quote only if a column has been declared
236			if ($columnName !== '*') {
237			$columnName = '`'.$columnName.'`';
238			}
239
240			$columnName = '`'.$tableName.'`.'.$columnName;
241			}
242
243			//If a column shortcut is declared
244			if (is_string($columnShortcut)) {
245			$this->columns[] = (object) [
246			'column' => $columnName,
247			'shortcut' => $columnShortcut
248			];
249			} else {
250			$this->columns[] = (object) [
251			'column' => $columnName,
252			'shortcut' => null
253			];
254			}
255			}
256			}
257
258			/**
259			* Declare information for FROM part and column will be get for main table
260			*
261			* @param string\|array $table Table name.
262			* It can be an array if a table shortcut is declared.
263			* In array mode, the format is ['asValue' => 'tableName']
264			* @param string\|array $columns (default: "*") Columns will be get for
265			* the table declared in first argument
266			*
267			* @return \BfwSql\SqlSelect
268			*/
269			public function from($table, $columns = '*')
270			{
271			$this->mainTable = $this->obtainTableInfos($table);
272
273			$tableName = $this->mainTable->tableName;
274			if ($this->mainTable->shortcut !== null) {
275			$tableName = $this->mainTable->shortcut;
276			}
277
278			$this->addColumnsForSelect($columns, $tableName);
279
280			return $this;
281			}
282
283			/**
284			* Add a sub-query in the SELECT part on the request
285			*
286			* @param \BfwSql\Actions\AbstractActions\|string $subRequest The sub-request
287			* @param string $shortcut The shortcut to use for
288			* this query in SELECT part
289			*
290			* @return \BfwSql\SqlSelect
291			*/
292			public function subQuery($subRequest, $shortcut)
293			{
294			if (
295			is_object($subRequest)
296			&& $subRequest instanceof \BfwSql\Actions\AbstractActions
297			) {
298			$subQuery = $subRequest->assemble();
299			} elseif (is_string($subRequest)) {
300			$subQuery = $subRequest;
301			} else {
302			throw new Exception(
303			'subRequest passed in parameters must be an instance of '
304			.'\BfwSql\Actions\AbstractActions or a string.',
305			self::ERR_SUB_QUERY_FORMAT
306			);
307			}
308
309			$this->subQueries[] = (object) [
310			'query' => $subQuery,
311			'shortcut' => $shortcut
312			];
313
314			return $this;
315			}
316
317			/**
318			* Add a (inner\|left\|right) join to the request
319			*
320			* @param string $joinPropertyName The name of the property in this
321			* class where the join is add
322			* @param string\|array $table Name of the table concerned by
323			* the join. Or an array with the table shortcut in key.
324			* @param string $joinOn SQL part "ON" for this join
325			* @param string\|array $joinColumns Columns from the table joined to
326			* add in the SELECT part of the request
327			*
328			* @return \BfwSql\SqlSelect
329			*/
330			protected function createJoin(
331			$joinPropertyName,
332			$table,
333			$joinOn,
334			$joinColumns
335			) {
336			$tableInfos = $this->obtainTableInfos($table);
337			$tableInfos->on = $joinOn;
338
339			$tableName = $tableInfos->tableName;
340			if ($tableInfos->shortcut !== null) {
341			$tableName = $tableInfos->shortcut;
342			}
343
344			$this->{$joinPropertyName}[] = $tableInfos;
345			$this->addColumnsForSelect($joinColumns, $tableName);
346
347			return $this;
348			}
349
350			/**
351			* Add a INNER JOIN to the request
352			*
353			* @param string\|array $table Name of the table concerned by the
354			* join. Or an array with the table shortcut in key.
355			* @param string $joinOn SQL part "ON" for this join
356			* @param string\|array $joinColumns Columns from the table joined to add
357			* in the SELECT part of the request
358			*
359			* @return \BfwSql\SqlSelect
360			*/
361			public function join($table, $joinOn, $joinColumns = '*')
362			{
363			return $this->createJoin('join', $table, $joinOn, $joinColumns);
364			}
365
366			/**
367			* Add a LEFT JOIN to the request
368			*
369			* @param string\|array $table Name of the table concerned by the
370			* join. Or an array with the table shortcut in key.
371			* @param string $joinOn SQL part "ON" for this join
372			* @param string\|array $joinColumns Columns from the table joined to add
373			* in the SELECT part of the request
374			*
375			* @return \BfwSql\SqlSelect
376			*/
377			public function joinLeft($table, $joinOn, $joinColumns = '*')
378			{
379			return $this->createJoin('joinLeft', $table, $joinOn, $joinColumns);
380			}
381
382			/**
383			* Add a RIGHT JOIN to the request
384			*
385			* @param string\|array $table Name of the table concerned by the
386			* join. Or an array with the table shortcut in key.
387			* @param string $joinOn SQL part "ON" for this join
388			* @param string\|array $joinColumns Columns from the table joined to add
389			* in the SELECT part of the request
390			*
391			* @return \BfwSql\SqlSelect
392			*/
393			public function joinRight($table, $joinOn, $joinColumns = '*')
394			{
395			return $this->createJoin('joinRight', $table, $joinOn, $joinColumns);
396			}
397
398			/**
399			* Add a order condition to the request for the ORDER BY part
400			*
401			* @param string $condition The new condition
402			*
403			* @return \BfwSql\SqlSelect
404			*/
405			public function order($condition)
406			{
407			$this->order[] = (string) $condition;
408			return $this;
409			}
410
411			/**
412			* Add information about the LIMIT part in request
413			*
414			* @param array\|integer $limit If it's a integer, the number of row to
415			* return. If an array, the format is [numberToStart, numberOfRowToReturn]
416			*
417			* @return \BfwSql\SqlSelect
418			*/
419			public function limit($limit)
420			{
421			if (!is_array($limit)) {
422			$limit = (array) $limit;
423			}
424
425			if (isset($limit[1])) {
426			$this->limit = $limit[0].', '.$limit[1];
427			} else {
428			$this->limit = (string) $limit[0];
429			}
430
431			return $this;
432			}
433
434			/**
435			* Add a GROUP BY part to the request
436			*
437			* @param string $condition The condition to use in GROUP BY
438			*
439			* @return \BfwSql\SqlSelect
440			*/
441			public function group($condition)
442			{
443			$this->group[] = $condition;
444			return $this;
445			}
446
447			/**
448			* Return the PDO constant for the returnType declared
449			*
450			* @return integer
451			*/
452			protected function obtainPdoFetchType()
453			{
454			if ($this->returnType === 'object') {
455			return PDO::FETCH_OBJ;
456			}
457
458			return PDO::FETCH_ASSOC;
459			}
460
461			/**
462			* Fetch one row of the result
463			*
464			* @return mixed
465			*/
466			public function fetchRow()
467			{
468			$req = $this->execute();
469			return $req->fetch($this->obtainPdoFetchType());
470			}
471
472			/**
473			* Fetch all rows returned by the request
474			*
475			* @return generator
476			*/
477			public function fetchAll()
478			{
479			$request = $this->execute(); //throw an Exception if error
480
481			while ($row = $request->fetch($this->obtainPdoFetchType())) {
482			yield $row;
483			}
484			}
485
486			/**
487			* {@inheritdoc}
488			*/
489			protected function assembleRequest()
490			{
491			$this->assembledRequest = 'SELECT '.$this->generateSelect()
492			.' FROM '.$this->generateFrom()
493			.$this->generateJoin('join')
494			.$this->generateJoin('joinLeft')
495			.$this->generateJoin('joinRight')
496			.$this->generateWhere()
497			.$this->generateGroupBy()
498			.$this->generateOrderBy()
499			.$this->generateLimit();
500			}
501
502			/**
503			* Write the SELECT part of the request
504			*
505			* @return string
506			*/
507			protected function generateSelect()
508			{
509			$select = '';
510			foreach ($this->columns as $columnInfos) {
511			if ($select != '') {
512			$select .= ', ';
513			}
514
515			$select .= $columnInfos->column;
516			if ($columnInfos->shortcut !== null) {
517			$select .= ' AS `'.$columnInfos->shortcut.'`';
518			}
519			}
520
521			foreach ($this->subQueries as $subQueryInfos) {
522			if ($select != '') {
523			$select .= ', ';
524			}
525
526			$select .= '('.$subQueryInfos->query.')'
527			.' AS `'.$subQueryInfos->shortcut.'`';
528			}
529
530			return $select;
531			}
532
533			/**
534			* Write the FROM part of the request
535			*
536			* @return string
537			*/
538			protected function generateFrom()
539			{
540			if ($this->mainTable === null) {
541			throw new Exception(
542			'The "FROM" part of the request can not be generate because'
543			.' the table name as not been declared.',
544			self::ERR_GENERATE_FROM_MISSING_TABLE_NAME
545			);
546			}
547
548			$from = '`'.$this->mainTable->tableName.'`';
549
550			if ($this->mainTable->shortcut !== null) {
551			$from .= ' AS `'.$this->mainTable->shortcut.'`';
552			}
553
554			return $from;
555			}
556
557			/**
558			* Write a (inner\|left\|right) join in the request
559			*
560			* @param string $joinProperty The join property name
561			*
562			* @return string
563			*/
564			protected function generateJoin($joinProperty)
565			{
566			$join = '';
567			if (count($this->{$joinProperty}) === 0) {
568			return $join;
569			}
570
571			if ($joinProperty == 'join') {
572			$joinSqlName = ' INNER JOIN ';
573			} elseif ($joinProperty == 'joinLeft') {
574			$joinSqlName = ' LEFT JOIN ';
575			} elseif ($joinProperty == 'joinRight') {
576			$joinSqlName = ' RIGHT JOIN ';
577			}
578
579			foreach ($this->{$joinProperty} as $joinInfos) {
580			$join .= $joinSqlName.'`'.$joinInfos->tableName.'`';
581			if ($joinInfos->shortcut !== null) {
582			$join .= ' AS `'.$joinInfos->shortcut.'`';
583			}
584
585			$join .= ' ON '.$joinInfos->on;
586			}
587
588			return $join;
589			}
590
591			/**
592			* Write the ORDER BY part for the request
593			*
594			* @return string
595			*/
596			protected function generateOrderBy()
597			{
598			if (count($this->order) === 0) {
599			return '';
600			}
601
602			$order = ' ORDER BY ';
603			foreach ($this->order as $orderCondition) {
604			if ($order != ' ORDER BY ') {
605			$order .= ', ';
606			}
607
608			$order .= $orderCondition;
609			}
610
611			return $order;
612			}
613
614			/**
615			* Write the GRUOP BY part for the request
616			*
617			* @return string
618			*/
619			protected function generateGroupBy()
620			{
621			if (count($this->group) === 0) {
622			return '';
623			}
624
625			$group = ' GROUP BY ';
626			foreach ($this->group as $groupCondition) {
627			if ($group != ' GROUP BY ') {
628			$group .= ', ';
629			}
630
631			$group .= $groupCondition;
632			}
633
634			return $group;
635			}
636
637			/**
638			* Write the LIMIT part for the request
639			*
640			* @return string
641			*/
642			protected function generateLimit()
643			{
644			$limit = '';
645			if ($this->limit !== '') {
646			$limit = ' LIMIT '.$this->limit;
647			}
648
649			return $limit;
650			}
651			}
652

bulton-fr / bfw-sql

Push — 2.0 ( ada449...a2425b )

Select::fetchAll() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like