AbstractBuilder::getSql()

Completed

Push — master ( 6f5620...1a6f49 )

by Anton

created 2017-07-06 15:31 UTC

↳ Parent: AbstractBuilder

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
nc	1
dl	0
loc	1
ccs	0
cts	0
cp	0
c	0
b	0
f	0

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

declare(strict_types=1);

namespace Bluz\Db\Query;

use Bluz\Proxy\Db;

/**
 * Query Builders classes is responsible to dynamically create SQL queries
 * Based on Doctrine QueryBuilder code
 *
 * @package Bluz\Db\Query
 * @link    https://github.com/bluzphp/framework/wiki/Db-Query
 * @link    https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Query/QueryBuilder.php
 */
abstract class AbstractBuilder
{
    /**
     * @var array list of table aliases
     */
    protected $aliases = [];

    /**
     * @var string the complete SQL string for this query
     */
    protected $sql;

    /**
     * @var array the array of SQL parts collected
     */
    protected $sqlParts = [
        'select' => [],
        'from' => [],
        'join' => [],
        'set' => [],
        'where' => null,
        'groupBy' => [],
        'having' => null,
        'orderBy' => []
    ];

    /**
     * @var array the query parameters
     */
    protected $params = [];

    /**
     * @var array the parameter type map of this query
     */
    protected $types = [];

    /**
     * Execute this query using the bound parameters and their types
     *
     * @return integer|string|array
     */
    public function execute()
    {
        return Db::query($this->getSql(), $this->params, $this->types);
    }

    /**
     * Return the complete SQL string formed by the current specifications
     *
     * Example
     * <code>
     *     $sb = new SelectBuilder();
     *     $sb
     *         ->select('u')
     *         ->from('User', 'u');
     *     echo $qb->getSql(); // SELECT u FROM User u
     * </code>
     *
     * @return string The SQL query string
     */
    abstract public function getSql();

    /**
     * Return the complete SQL string formed for use
     *
     * Example
     * <code>
     *     $sb = new SelectBuilder();
     *     $sb
     *         ->select('u')
     *         ->from('User', 'u')
     *         ->where('id = ?', 42);
     *     echo $qb->getQuery(); // SELECT u FROM User u WHERE id = "42"
     * </code>
     *
     * @return string
     */
    public function getQuery()
    {
        $sql = $this->getSql();

        $sql = str_replace('%', '%%', $sql);
        $sql = str_replace('?', '"%s"', $sql);

        // replace mask by data
        return vsprintf($sql, $this->getParameters());
    }

    /**
     * Sets a query parameter for the query being constructed
     *
     * Example
     * <code>
     *     $sb = new SelectBuilder();
     *     $sb
     *         ->select('u')
     *         ->from('users', 'u')
     *         ->where('u.id = :user_id')
     *         ->setParameter(':user_id', 1);
     * </code>
     *
     * @param  string|int $key   The parameter position or name
     * @param  mixed      $value The parameter value
     * @param  integer    $type  PDO::PARAM_*
     *
     * @return $this
     */
    public function setParameter($key, $value, $type = \PDO::PARAM_STR)
    {
        if (null == $key) {
            $key = count($this->params);
        }

        $this->params[$key] = $value;
        $this->types[$key] = $type;

        return $this;
    }

    /**
     * Sets a collection of query parameters for the query being constructed
     *
     * Example
     * <code>
     *     $sb = new SelectBuilder();
     *     $sb
     *         ->select('u')
     *         ->from('users', 'u')
     *         ->where('u.id = :user_id1 OR u.id = :user_id2')
     *         ->setParameters([
     *             ':user_id1' => 1,
     *             ':user_id2' => 2
     *         ]);
     * </code>
     *
     * @param  array $params The query parameters to set
     * @param  array $types  The query parameters types to set
     *
     * @return $this
     */
    public function setParameters(array $params, array $types = [])
    {
        $this->types = $types;
        $this->params = $params;

        return $this;
    }

    /**
     * Gets a (previously set) query parameter of the query being constructed
     *
     * @param  mixed $key The key (index or name) of the bound parameter
     *
     * @return mixed The value of the bound parameter.
     */
    public function getParameter($key)
    {
        return $this->params[$key] ?? null;
    }

    /**
     * Gets all defined query parameters for the query being constructed
     *
     * @return array The currently defined query parameters
     */
    public function getParameters()
    {
        return $this->params;
    }

    /**
     * Either appends to or replaces a single, generic query part
     *
     * The available parts are: 'select', 'from', 'set', 'where',
     * 'groupBy', 'having' and 'orderBy'
     *
     * @param  string       $sqlPartName
     * @param  string|array $sqlPart
     * @param  bool         $append
     *
     * @return $this
     */
    protected function addQueryPart($sqlPartName, $sqlPart, $append = false)
    {
        $isArray = is_array($sqlPart);
        $isMultiple = is_array($this->sqlParts[$sqlPartName]);

        if ($isMultiple && !$isArray) {
            $sqlPart = [$sqlPart];
        }

        if ($append) {
            if ($sqlPartName == "orderBy" || $sqlPartName == "groupBy"
                || $sqlPartName == "select" || $sqlPartName == "set"
            ) {
                foreach ((array)$sqlPart as $part) {
                    $this->sqlParts[$sqlPartName][] = $part;
                }
            } elseif ($isArray && is_array($sqlPart[key($sqlPart)])) {
                $key = key($sqlPart);
                $this->sqlParts[$sqlPartName][$key][] = $sqlPart[$key];
            } elseif ($isMultiple) {
                $this->sqlParts[$sqlPartName][] = $sqlPart;
            } else {
                $this->sqlParts[$sqlPartName] = $sqlPart;
            }
        } else {
            $this->sqlParts[$sqlPartName] = $sqlPart;
        }
        return $this;
    }

    /**
     * Get a query part by its name
     *
     * @param  string $queryPartName
     *
     * @return mixed
     */
    public function getQueryPart($queryPartName)
    {
        return $this->sqlParts[$queryPartName];
    }

    /**
     * Reset single SQL part
     *
     * @param  string $queryPartName
     *
     * @return $this
     */
    protected function resetQueryPart($queryPartName)
    {
        $this->sqlParts[$queryPartName] = is_array($this->sqlParts[$queryPartName])
            ? [] : null;

        return $this;
    }

    /**
     * setFromQueryPart
     *
     * @param  string $table
     *
     * @return $this
     */
    protected function setFromQueryPart($table)
    {
        $table = Db::quoteIdentifier($table);
        return $this->addQueryPart('from', ['table' => $table], false);
    }

    /**
     * Prepare condition
     *
     * <code>
     *     $builder->prepareCondition("WHERE id IN (?)", [..,..]);
     * </code>
     *
     * @param  array $args
     *
     * @return string
     */
    protected function prepareCondition($args = [])
    {
        $condition = array_shift($args);
        foreach ($args as &$value) {
            if (is_array($value)) {
                $replace = implode(',', array_fill(0, count($value), ':REPLACE:'));
                $condition = preg_replace('/\?/', $replace, $condition, 1);
                foreach ($value as $part) {
                    $this->setParameter(null, $part);
                }
            } else {
                $this->setParameter(null, $value);
            }
        }

        $condition = preg_replace('/(\:REPLACE\:)/', '?', $condition);

        return $condition;
    }

    /**
     * Gets a string representation of this QueryBuilder which corresponds to
     * the final SQL query being constructed.
     *
     * @return string The string representation of this QueryBuilder.
     */
    public function __toString()
    {
        return $this->getSql();
    }
}


1		<?php
2		/**
3		* Bluz Framework Component
4		*
5		* @copyright Bluz PHP Team
6		* @link https://github.com/bluzphp/framework
7		*/
8
9		declare(strict_types=1);
10
11		namespace Bluz\Db\Query;
12
13		use Bluz\Proxy\Db;
14
15		/**
16		* Query Builders classes is responsible to dynamically create SQL queries
17		* Based on Doctrine QueryBuilder code
18		*
19		* @package Bluz\Db\Query
20		* @link https://github.com/bluzphp/framework/wiki/Db-Query
21		* @link https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Query/QueryBuilder.php
22		*/
23		abstract class AbstractBuilder
24		{
25		/**
26		* @var array list of table aliases
27		*/
28		protected $aliases = [];
29
30		/**
31		* @var string the complete SQL string for this query
32		*/
33		protected $sql;
34
35		/**
36		* @var array the array of SQL parts collected
37		*/
38		protected $sqlParts = [
39		'select' => [],
40		'from' => [],
41		'join' => [],
42		'set' => [],
43		'where' => null,
44		'groupBy' => [],
45		'having' => null,
46		'orderBy' => []
47		];
48
49		/**
50		* @var array the query parameters
51		*/
52		protected $params = [];
53
54		/**
55		* @var array the parameter type map of this query
56		*/
57		protected $types = [];
58
59		/**
60		* Execute this query using the bound parameters and their types
61		*
62		* @return integer\|string\|array
63		*/
64	8	public function execute()
65		{
66	8	return Db::query($this->getSql(), $this->params, $this->types);
67		}
68
69		/**
70		* Return the complete SQL string formed by the current specifications
71		*
72		* Example
73		* <code>
74		* $sb = new SelectBuilder();
75		* $sb
76		* ->select('u')
77		* ->from('User', 'u');
78		* echo $qb->getSql(); // SELECT u FROM User u
79		* </code>
80		*
81		* @return string The SQL query string
82		*/
83		abstract public function getSql();
84
85		/**
86		* Return the complete SQL string formed for use
87		*
88		* Example
89		* <code>
90		* $sb = new SelectBuilder();
91		* $sb
92		* ->select('u')
93		* ->from('User', 'u')
94		* ->where('id = ?', 42);
95		* echo $qb->getQuery(); // SELECT u FROM User u WHERE id = "42"
96		* </code>
97		*
98		* @return string
99		*/
100	7	public function getQuery()
101		{
102	7	$sql = $this->getSql();
103
104	7	$sql = str_replace('%', '%%', $sql);
105	7	$sql = str_replace('?', '"%s"', $sql);
106
107		// replace mask by data
108	7	return vsprintf($sql, $this->getParameters());
109		}
110
111		/**
112		* Sets a query parameter for the query being constructed
113		*
114		* Example
115		* <code>
116		* $sb = new SelectBuilder();
117		* $sb
118		* ->select('u')
119		* ->from('users', 'u')
120		* ->where('u.id = :user_id')
121		* ->setParameter(':user_id', 1);
122		* </code>
123		*
124		* @param string\|int $key The parameter position or name
125		* @param mixed $value The parameter value
126		* @param integer $type PDO::PARAM_*
127		*
128		* @return $this
129		*/
130	9	public function setParameter($key, $value, $type = \PDO::PARAM_STR)
131		{
132	9	if (null == $key) {
133	9	$key = count($this->params);
134		}
135
136	9	$this->params[$key] = $value;
137	9	$this->types[$key] = $type;
138
139	9	return $this;
140		}
141
142		/**
143		* Sets a collection of query parameters for the query being constructed
144		*
145		* Example
146		* <code>
147		* $sb = new SelectBuilder();
148		* $sb
149		* ->select('u')
150		* ->from('users', 'u')
151		* ->where('u.id = :user_id1 OR u.id = :user_id2')
152		* ->setParameters([
153		* ':user_id1' => 1,
154		* ':user_id2' => 2
155		* ]);
156		* </code>
157		*
158		* @param array $params The query parameters to set
159		* @param array $types The query parameters types to set
160		*
161		* @return $this
162		*/
163	1	public function setParameters(array $params, array $types = [])
164		{
165	1	$this->types = $types;
166	1	$this->params = $params;
167
168	1	return $this;
169		}
170
171		/**
172		* Gets a (previously set) query parameter of the query being constructed
173		*
174		* @param mixed $key The key (index or name) of the bound parameter
175		*
176		* @return mixed The value of the bound parameter.
177		*/
178	1	public function getParameter($key)
179		{
180	1	return $this->params[$key] ?? null;
181		}
182
183		/**
184		* Gets all defined query parameters for the query being constructed
185		*
186		* @return array The currently defined query parameters
187		*/
188	7	public function getParameters()
189		{
190	7	return $this->params;
191		}
192
193		/**
194		* Either appends to or replaces a single, generic query part
195		*
196		* The available parts are: 'select', 'from', 'set', 'where',
197		* 'groupBy', 'having' and 'orderBy'
198		*
199		* @param string $sqlPartName
200		* @param string\|array $sqlPart
201		* @param bool $append
202		*
203		* @return $this
204		*/
205	14	protected function addQueryPart($sqlPartName, $sqlPart, $append = false)
206		{
207	14	$isArray = is_array($sqlPart);
208	14	$isMultiple = is_array($this->sqlParts[$sqlPartName]);
209
210	14	if ($isMultiple && !$isArray) {
211	3	$sqlPart = [$sqlPart];
212		}
213
214	14	if ($append) {
215	9	if ($sqlPartName == "orderBy" \|\| $sqlPartName == "groupBy"
216	9	\|\| $sqlPartName == "select" \|\| $sqlPartName == "set"
217		) {
218	4	foreach ((array)$sqlPart as $part) {
219	4	$this->sqlParts[$sqlPartName][] = $part;
220		}
221	7	} elseif ($isArray && is_array($sqlPart[key($sqlPart)])) {
222	4	$key = key($sqlPart);
223	4	$this->sqlParts[$sqlPartName][$key][] = $sqlPart[$key];
224	7	} elseif ($isMultiple) {
225	7	$this->sqlParts[$sqlPartName][] = $sqlPart;
226		} else {
227	9	$this->sqlParts[$sqlPartName] = $sqlPart;
228		}
229		} else {
230	14	$this->sqlParts[$sqlPartName] = $sqlPart;
231		}
232	14	return $this;
233		}
234
235		/**
236		* Get a query part by its name
237		*
238		* @param string $queryPartName
239		*
240		* @return mixed
241		*/
242	4	public function getQueryPart($queryPartName)
243		{
244	4	return $this->sqlParts[$queryPartName];
245		}
246
247		/**
248		* Reset single SQL part
249		*
250		* @param string $queryPartName
251		*
252		* @return $this
253		*/
254		protected function resetQueryPart($queryPartName)
255		{
256		$this->sqlParts[$queryPartName] = is_array($this->sqlParts[$queryPartName])
257		? [] : null;
258
259		return $this;
260		}
261
262		/**
263		* setFromQueryPart
264		*
265		* @param string $table
266		*
267		* @return $this
268		*/
269	11	protected function setFromQueryPart($table)
270		{
271	11	$table = Db::quoteIdentifier($table);
272	11	return $this->addQueryPart('from', ['table' => $table], false);
273		}
274
275		/**
276		* Prepare condition
277		*
278		* <code>
279		* $builder->prepareCondition("WHERE id IN (?)", [..,..]);
280		* </code>
281		*
282		* @param array $args
283		*
284		* @return string
285		*/
286	9	protected function prepareCondition($args = [])
287		{
288	9	$condition = array_shift($args);
289	9	foreach ($args as &$value) {
290	9	if (is_array($value)) {
291	1	$replace = implode(',', array_fill(0, count($value), ':REPLACE:'));
292	1	$condition = preg_replace('/\?/', $replace, $condition, 1);
293	1	foreach ($value as $part) {
294	1	$this->setParameter(null, $part);
295		}
296		} else {
297	9	$this->setParameter(null, $value);
298		}
299		}
300
301	9	$condition = preg_replace('/(\:REPLACE\:)/', '?', $condition);
302
303	9	return $condition;
304		}
305
306		/**
307		* Gets a string representation of this QueryBuilder which corresponds to
308		* the final SQL query being constructed.
309		*
310		* @return string The string representation of this QueryBuilder.
311		*/
312	1	public function __toString()
313		{
314	1	return $this->getSql();
315		}
316		}
317

bluzphp / framework

Push — master ( 6f5620...1a6f49 )

AbstractBuilder::getSql()

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like