Query::setBindings() - Code Metrics - Inspection of "Correção do metodo having" - WillRy/querybuilder - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — main ( 4e824a...56b906 )

by William

created 2023-07-09 03:26 UTC

Query::setBindings() A

↳ Parent: Query

Complexity

Conditions	1
Paths	1

Size

Total Lines	5
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Importance

Changes

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

<?php

namespace Willry\QueryBuilder;

use Exception;
use PDOStatement;
use stdClass;

abstract class Query
{

    /**
     * @var bool
     */
    protected $silentErrors;

    /**
     * @var \Exception|null
     */
    protected $fail;

    /**
     * @var string $entity database table
     */
    protected $entity;

    /**
     * @var array
     */
    protected $columns = [];

    /**
     * @var string
     */
    protected $where;

    /**
     * @var string
     */
    protected $order;

    /**
     * @var string
     */
    protected $limit;

    /**
     * @var string
     */
    protected $offset;

    /**
     * @var string
     */
    protected $joins;

    /**
     * @var string
     */
    protected $query;

    /**
     * @var string
     */
    protected $groupBy;

    /**
     * @var string
     */
    protected $having;

    /**
     * @var \PDO
     */
    protected $db;

    public $bindings = [
        'select' => [],
        'from' => [],
        'join' => [],
        'where' => [],
        'groupBy' => [],
        'having' => [],
        'order' => [],
        'union' => [],
        'unionOrder' => [],
    ];

    /**
     * @var array|null
     */
    protected $connectionConfig;


    public function __construct(string $connectionName = 'default', bool $regenerateConnection = false)
    {
        $this->db = Connect::getInstance($connectionName, $regenerateConnection);
        $this->connectionConfig = Connect::getConfig($connectionName);
    }


    public function from(string $entity)
    {
        $this->entity = $entity;
        return $this;
    }

    public function setSilentErrors(bool $silentErrors = false)
    {
        $this->silentErrors = $silentErrors;
    }

    public function fromSubQuery(Query $query, string $alias = 'sub')
    {
        $this->entity = "(" . $query->toSQL() . ") as $alias";
        return $this;
    }


    /**
     * @param string $columns
     * @return $this
     */
    public function selectRaw(string $columns = "*", array $params = []): Query
    {
        $this->setBindings($params, 'select');
        $this->columns = array_merge($this->columns, explode(',', $columns));
        return $this;
    }

    public function select(array $columns = ['*']): Query
    {
        $this->columns = array_merge($this->columns, $columns);
        return $this;
    }

    /**
     * @param string $where
     * @return $this
     */
    public function where(string $where, array $params = []): Query
    {
        $this->setBindings($params, 'where');

        if (!empty($this->where)) {
            $this->where .= " AND {$where}";
            return $this;
        }

        $this->where = "WHERE $where";
        return $this;
    }

    /**
     * @param string $where
     * @return $this
     */
    public function orWhere(string $where, array $params = []): Query
    {
        $this->setBindings($params, 'where');
        if (!empty($this->where)) {
            $this->where .= " OR {$where}";
            return $this;
        }

        $this->where = "WHERE $where";
        return $this;
    }

    public function whereIn(string $column, array $data): Query
    {
        $inString = implode(',', array_fill(0, count($data), '?'));
        $this->setBindings($data, 'where');

        if (!empty($this->where)) {
            $this->where .= " AND {$column} IN ({$inString})";
            return $this;
        }

        $this->where = "WHERE {$column} IN ({$inString})";
        return $this;
    }

    public function orWhereIn(string $column, array $data): Query
    {

        $inString = implode(',', array_fill(0, count($data), '?'));
        $this->setBindings($data, 'where');

        if (!empty($this->where)) {
            $this->where .= " OR {$column} IN ($inString)";
            return $this;
        }

        $this->where = "WHERE {$column} IN ($inString)";
        return $this;
    }


    /**
     * @param string $columnOrder
     */
    public function order(string $columnOrder): Query
    {
        $this->order = "ORDER BY ?";
        $this->setBindings([$columnOrder], 'order');
        return $this;
    }

    /**
     * @param int $limit
     */
    public function limit(int $limit): Query
    {
        $this->limit = "LIMIT $limit";
        return $this;
    }

    /**
     * @param int $offset
     * @return DB
     */
    public function offset(int $offset): Query
    {
        $this->offset = "OFFSET $offset";
        return $this;
    }

    /**
     * @param array $group
     * @return DB
     */
    public function groupBy(string $group): Query
    {

        $this->groupBy = "GROUP BY {$group}";
        return $this;
    }

    /**
     * @param string $having
     * @return DB
     */
    public function having(string $having, array $params = []): Query
    {
        $this->having = "HAVING {$having}";
        $this->setBindings($params, 'having');
        return $this;
    }

    public function getBindings()
    {
        return $this->bindings;
    }

    /**
     * @param array $params
     * @return $this
     */
    public function setBindings(array $params = [], string $type = 'where'): Query
    {
        $this->bindings[$type] = array_merge($params, $this->bindings[$type]);

        return $this;
    }

    /**
     * @param string $join
     * @return $this
     */
    public function leftJoin(string $join, array $params = []): Query
    {
        $this->setBindings($params, 'join');

        $this->joins .= PHP_EOL;

        $this->joins .= "LEFT JOIN $join";

        return $this;
    }

    /**
     * @param string $join
     * @return $this
     */
    public function rightJoin(string $join, array $params = []): Query
    {
        $this->setBindings($params, 'join');

        $this->joins .= PHP_EOL;

        $this->joins .= "RIGHT JOIN $join";

        return $this;
    }

    /**
     * @param string $join
     * @return $this
     */
    public function join(string $join, array $params = []): Query
    {
        $this->setBindings($params, 'join');

        $this->joins .= PHP_EOL;

        $this->joins .= "INNER JOIN $join";
        return $this;
    }

    public function joinSub(Query $subquery, string $alias, $condition, array $params = [])
    {
        $this->setBindings($params, 'join');

        $this->joins .= PHP_EOL;

        $this->joins .= "INNER JOIN ({$subquery->toSQL()}) AS $alias ON $condition";

        $this->mergeBindFromAnotherQuery($subquery);

        return $this;
    }

    public function leftJoinSub(Query $subquery, string $alias, $condition, array $params = [])
    {
        $this->setBindings($params, 'join');

        $this->joins .= PHP_EOL;

        $this->joins .= "LEFT JOIN ({$subquery->toSQL()}) AS $alias ON $condition";

        $this->mergeBindFromAnotherQuery($subquery);

        return $this;
    }

    public function rightJoinSub(Query $subquery, string $alias, $condition, array $params = [])
    {
        $this->setBindings($params, 'join');

        $this->joins .= PHP_EOL;

        $this->joins .= "RIGHT JOIN ({$subquery->toSQL()}) AS $alias ON $condition";

        $this->mergeBindFromAnotherQuery($subquery);

        return $this;
    }

    public function get(): ?array
    {
        $this->mountQuery();

        try {
            $stmt = $this->db->prepare($this->query);
            QueryHelpers::bind($stmt, $this->flatBindings());
            $stmt->execute();

            return $stmt->fetchAll(\PDO::FETCH_CLASS);
        } catch (\PDOException $exception) {
            $this->handleError($exception);
        }
    }

    public function first(): ?stdClass
    {
        $this->mountQuery();

        try {
            $stmt = $this->db->prepare($this->query);
            QueryHelpers::bind($stmt, $this->flatBindings());
            $stmt->execute();

            if (!$stmt->rowCount()) {
                return null;
            }

            return $stmt->fetchObject();
        } catch (\PDOException $exception) {
            $this->handleError($exception);
        }
    }

    public function count(): ?int
    {
        $this->mountQuery();

        try {
            $stmt = $this->db->prepare($this->query);
            QueryHelpers::bind($stmt, $this->flatBindings());
            $stmt->execute();

            return $stmt->rowCount();
        } catch (\PDOException $exception) {
            $this->handleError($exception);
        }
    }


    /**
     * @param array $data
     * @return string|null
     */
    public function create(array $data)
    {
        try {
            $columns = implode(", ", array_keys($data));
            $values = implode(',', array_fill(0, count($data), '?'));

            $stmt = $this->db->prepare("INSERT INTO {$this->entity} ({$columns}) VALUES ({$values})");
            $this->setBindings(array_values($data));

            QueryHelpers::bind($stmt, $this->flatBindings());

            $stmt->execute();

            return $this->db->lastInsertId();
        } catch (\PDOException $exception) {
            $this->handleError($exception);
        }
    }

    /**
     * @param array $data
     */
    public function update(array $data): ?int
    {
        try {
            $dateSet = [];
            foreach ($data as $bind => $value) {
                $dateSet[] = "{$bind} = ?";
            }
            $dateSet = implode(", ", $dateSet);

            $stmt = $this->db->prepare("UPDATE {$this->entity} SET {$dateSet} {$this->where}");

            $this->setBindings(array_values($data));

            QueryHelpers::bind($stmt, $this->flatBindings());

            $stmt->execute();

            return $stmt->rowCount() ?? 1;
        } catch (\PDOException $exception) {
            $this->handleError($exception);
        }
    }

    public function delete(): ?int
    {
        try {
            $stmt = $this->db->prepare("DELETE FROM {$this->entity} {$this->where}");
            QueryHelpers::bind($stmt, $this->flatBindings());
            $stmt->execute();
            return $stmt->rowCount() ?? 1;
        } catch (\PDOException $exception) {
            $this->handleError($exception);
        }
    }


    public function beginTransaction(): bool
    {
        return $this->db->beginTransaction();
    }


    public function commit(): bool
    {
        return $this->db->commit();
    }


    public function rollback(): bool
    {
        return $this->db->rollBack();
    }

    /**
     * @param  $exception
     * @throws Exception
     */
    private function handleError(\Exception $exception)
    {
        if (!$this->silentErrors) {
            throw $exception;
        }

        $this->fail = $exception;
    }


    private function mountQuery(): void
    {

        $columns = !empty($this->columns) ? $this->columns : ['*'];
        $columns = implode(',', $columns);
        $this->query = "SELECT $columns FROM $this->entity $this->joins $this->where $this->groupBy $this->having $this->order $this->limit $this->offset";
    }


    public function dump(): array
    {
        $this->mountQuery();

        return [
            "query" => $this->query,
            "raw_params" => $this->bindings,
        ];
    }

    public function toSQL()
    {
        $this->mountQuery();
        return $this->query;
    }


    private function flatBindings()
    {
        $params = [];
        foreach ($this->bindings as $key => $binds) {
            $params = array_merge($params, $this->bindings[$key]);
        }

        return $params;
    }

    public function mergeBindFromAnotherQuery(Query $query)
    {
        $this->bindings = array_merge_recursive($this->getBindings(), $query->getBindings());
    }
}


1			<?php
2
3			namespace Willry\QueryBuilder;
4
5			use Exception;
6			use PDOStatement;
7			use stdClass;
8
9			abstract class Query
10			{
11
12			/**
13			* @var bool
14			*/
15			protected $silentErrors;
16
17			/**
18			* @var \Exception\|null
19			*/
20			protected $fail;
21
22			/**
23			* @var string $entity database table
24			*/
25			protected $entity;
26
27			/**
28			* @var array
29			*/
30			protected $columns = [];
31
32			/**
33			* @var string
34			*/
35			protected $where;
36
37			/**
38			* @var string
39			*/
40			protected $order;
41
42			/**
43			* @var string
44			*/
45			protected $limit;
46
47			/**
48			* @var string
49			*/
50			protected $offset;
51
52			/**
53			* @var string
54			*/
55			protected $joins;
56
57			/**
58			* @var string
59			*/
60			protected $query;
61
62			/**
63			* @var string
64			*/
65			protected $groupBy;
66
67			/**
68			* @var string
69			*/
70			protected $having;
71
72			/**
73			* @var \PDO
74			*/
75			protected $db;
76
77			public $bindings = [
78			'select' => [],
79			'from' => [],
80			'join' => [],
81			'where' => [],
82			'groupBy' => [],
83			'having' => [],
84			'order' => [],
85			'union' => [],
86			'unionOrder' => [],
87			];
88
89			/**
90			* @var array\|null
91			*/
92			protected $connectionConfig;
93
94
95			public function __construct(string $connectionName = 'default', bool $regenerateConnection = false)
96			{
97			$this->db = Connect::getInstance($connectionName, $regenerateConnection);
98			$this->connectionConfig = Connect::getConfig($connectionName);
99			}
100
101
102			public function from(string $entity)
103			{
104			$this->entity = $entity;
105			return $this;
106			}
107
108			public function setSilentErrors(bool $silentErrors = false)
109			{
110			$this->silentErrors = $silentErrors;
111			}
112
113			public function fromSubQuery(Query $query, string $alias = 'sub')
114			{
115			$this->entity = "(" . $query->toSQL() . ") as $alias";
116			return $this;
117			}
118
119
120			/**
121			* @param string $columns
122			* @return $this
123			*/
124			public function selectRaw(string $columns = "*", array $params = []): Query
125			{
126			$this->setBindings($params, 'select');
127			$this->columns = array_merge($this->columns, explode(',', $columns));
128			return $this;
129			}
130
131			public function select(array $columns = ['*']): Query
132			{
133			$this->columns = array_merge($this->columns, $columns);
134			return $this;
135			}
136
137			/**
138			* @param string $where
139			* @return $this
140			*/
141			public function where(string $where, array $params = []): Query
142			{
143			$this->setBindings($params, 'where');
144
145			if (!empty($this->where)) {
146			$this->where .= " AND {$where}";
147			return $this;
148			}
149
150			$this->where = "WHERE $where";
151			return $this;
152			}
153
154			/**
155			* @param string $where
156			* @return $this
157			*/
158			public function orWhere(string $where, array $params = []): Query
159			{
160			$this->setBindings($params, 'where');
161			if (!empty($this->where)) {
162			$this->where .= " OR {$where}";
163			return $this;
164			}
165
166			$this->where = "WHERE $where";
167			return $this;
168			}
169
170			public function whereIn(string $column, array $data): Query
171			{
172			$inString = implode(',', array_fill(0, count($data), '?'));
173			$this->setBindings($data, 'where');
174
175			if (!empty($this->where)) {
176			$this->where .= " AND {$column} IN ({$inString})";
177			return $this;
178			}
179
180			$this->where = "WHERE {$column} IN ({$inString})";
181			return $this;
182			}
183
184			public function orWhereIn(string $column, array $data): Query
185			{
186
187			$inString = implode(',', array_fill(0, count($data), '?'));
188			$this->setBindings($data, 'where');
189
190			if (!empty($this->where)) {
191			$this->where .= " OR {$column} IN ($inString)";
192			return $this;
193			}
194
195			$this->where = "WHERE {$column} IN ($inString)";
196			return $this;
197			}
198
199
200			/**
201			* @param string $columnOrder
202			*/
203			public function order(string $columnOrder): Query
204			{
205			$this->order = "ORDER BY ?";
206			$this->setBindings([$columnOrder], 'order');
207			return $this;
208			}
209
210			/**
211			* @param int $limit
212			*/
213			public function limit(int $limit): Query
214			{
215			$this->limit = "LIMIT $limit";
216			return $this;
217			}
218
219			/**
220			* @param int $offset
221			* @return DB
222			*/
223			public function offset(int $offset): Query
224			{
225			$this->offset = "OFFSET $offset";
226			return $this;
227			}
228
229			/**
230			* @param array $group
231			* @return DB
232			*/
233			public function groupBy(string $group): Query
234			{
235
236			$this->groupBy = "GROUP BY {$group}";
237			return $this;
238			}
239
240			/**
241			* @param string $having
242			* @return DB
243			*/
244			public function having(string $having, array $params = []): Query
245			{
246			$this->having = "HAVING {$having}";
247			$this->setBindings($params, 'having');
248			return $this;
249			}
250
251			public function getBindings()
252			{
253			return $this->bindings;
254			}
255
256			/**
257			* @param array $params
258			* @return $this
259			*/
260			public function setBindings(array $params = [], string $type = 'where'): Query
261			{
262			$this->bindings[$type] = array_merge($params, $this->bindings[$type]);
263
264			return $this;
265			}
266
267			/**
268			* @param string $join
269			* @return $this
270			*/
271			public function leftJoin(string $join, array $params = []): Query
272			{
273			$this->setBindings($params, 'join');
274
275			$this->joins .= PHP_EOL;
276
277			$this->joins .= "LEFT JOIN $join";
278
279			return $this;
280			}
281
282			/**
283			* @param string $join
284			* @return $this
285			*/
286			public function rightJoin(string $join, array $params = []): Query
287			{
288			$this->setBindings($params, 'join');
289
290			$this->joins .= PHP_EOL;
291
292			$this->joins .= "RIGHT JOIN $join";
293
294			return $this;
295			}
296
297			/**
298			* @param string $join
299			* @return $this
300			*/
301			public function join(string $join, array $params = []): Query
302			{
303			$this->setBindings($params, 'join');
304
305			$this->joins .= PHP_EOL;
306
307			$this->joins .= "INNER JOIN $join";
308			return $this;
309			}
310
311			public function joinSub(Query $subquery, string $alias, $condition, array $params = [])
312			{
313			$this->setBindings($params, 'join');
314
315			$this->joins .= PHP_EOL;
316
317			$this->joins .= "INNER JOIN ({$subquery->toSQL()}) AS $alias ON $condition";
318
319			$this->mergeBindFromAnotherQuery($subquery);
320
321			return $this;
322			}
323
324			public function leftJoinSub(Query $subquery, string $alias, $condition, array $params = [])
325			{
326			$this->setBindings($params, 'join');
327
328			$this->joins .= PHP_EOL;
329
330			$this->joins .= "LEFT JOIN ({$subquery->toSQL()}) AS $alias ON $condition";
331
332			$this->mergeBindFromAnotherQuery($subquery);
333
334			return $this;
335			}
336
337			public function rightJoinSub(Query $subquery, string $alias, $condition, array $params = [])
338			{
339			$this->setBindings($params, 'join');
340
341			$this->joins .= PHP_EOL;
342
343			$this->joins .= "RIGHT JOIN ({$subquery->toSQL()}) AS $alias ON $condition";
344
345			$this->mergeBindFromAnotherQuery($subquery);
346
347			return $this;
348			}
349
350			public function get(): ?array
351			{
352			$this->mountQuery();
353
354			try {
355			$stmt = $this->db->prepare($this->query);
356			QueryHelpers::bind($stmt, $this->flatBindings());
357			$stmt->execute();
358
359			return $stmt->fetchAll(\PDO::FETCH_CLASS);
360			} catch (\PDOException $exception) {
361			$this->handleError($exception);
362			}
363			}
364
365			public function first(): ?stdClass
366			{
367			$this->mountQuery();
368
369			try {
370			$stmt = $this->db->prepare($this->query);
371			QueryHelpers::bind($stmt, $this->flatBindings());
372			$stmt->execute();
373
374			if (!$stmt->rowCount()) {
375			return null;
376			}
377
378			return $stmt->fetchObject();
379			} catch (\PDOException $exception) {
380			$this->handleError($exception);
381			}
382			}
383
384			public function count(): ?int
385			{
386			$this->mountQuery();
387
388			try {
389			$stmt = $this->db->prepare($this->query);
390			QueryHelpers::bind($stmt, $this->flatBindings());
391			$stmt->execute();
392
393			return $stmt->rowCount();
394			} catch (\PDOException $exception) {
395			$this->handleError($exception);
396			}
397			}
398
399
400			/**
401			* @param array $data
402			* @return string\|null
403			*/
404			public function create(array $data)
405			{
406			try {
407			$columns = implode(", ", array_keys($data));
408			$values = implode(',', array_fill(0, count($data), '?'));
409
410			$stmt = $this->db->prepare("INSERT INTO {$this->entity} ({$columns}) VALUES ({$values})");
411			$this->setBindings(array_values($data));
412
413			QueryHelpers::bind($stmt, $this->flatBindings());
414
415			$stmt->execute();
416
417			return $this->db->lastInsertId();
418			} catch (\PDOException $exception) {
419			$this->handleError($exception);
420			}
421			}
422
423			/**
424			* @param array $data
425			*/
426			public function update(array $data): ?int
427			{
428			try {
429			$dateSet = [];
430			foreach ($data as $bind => $value) {
431			$dateSet[] = "{$bind} = ?";
432			}
433			$dateSet = implode(", ", $dateSet);
434
435			$stmt = $this->db->prepare("UPDATE {$this->entity} SET {$dateSet} {$this->where}");
436
437			$this->setBindings(array_values($data));
438
439			QueryHelpers::bind($stmt, $this->flatBindings());
440
441			$stmt->execute();
442
443			return $stmt->rowCount() ?? 1;
444			} catch (\PDOException $exception) {
445			$this->handleError($exception);
446			}
447			}
448
449			public function delete(): ?int
450			{
451			try {
452			$stmt = $this->db->prepare("DELETE FROM {$this->entity} {$this->where}");
453			QueryHelpers::bind($stmt, $this->flatBindings());
454			$stmt->execute();
455			return $stmt->rowCount() ?? 1;
456			} catch (\PDOException $exception) {
457			$this->handleError($exception);
458			}
459			}
460
461
462			public function beginTransaction(): bool
463			{
464			return $this->db->beginTransaction();
465			}
466
467
468			public function commit(): bool
469			{
470			return $this->db->commit();
471			}
472
473
474			public function rollback(): bool
475			{
476			return $this->db->rollBack();
477			}
478
479			/**
480			* @param $exception
481			* @throws Exception
482			*/
483			private function handleError(\Exception $exception)
484			{
485			if (!$this->silentErrors) {
486			throw $exception;
487			}
488
489			$this->fail = $exception;
490			}
491
492
493			private function mountQuery(): void
494			{
495
496			$columns = !empty($this->columns) ? $this->columns : ['*'];
497			$columns = implode(',', $columns);
498			$this->query = "SELECT $columns FROM $this->entity $this->joins $this->where $this->groupBy $this->having $this->order $this->limit $this->offset";
499			}
500
501
502			public function dump(): array
503			{
504			$this->mountQuery();
505
506			return [
507			"query" => $this->query,
508			"raw_params" => $this->bindings,
509			];
510			}
511
512			public function toSQL()
513			{
514			$this->mountQuery();
515			return $this->query;
516			}
517
518
519			private function flatBindings()
520			{
521			$params = [];
522			foreach ($this->bindings as $key => $binds) {
523			$params = array_merge($params, $this->bindings[$key]);
524			}
525
526			return $params;
527			}
528
529			public function mergeBindFromAnotherQuery(Query $query)
530			{
531			$this->bindings = array_merge_recursive($this->getBindings(), $query->getBindings());
532			}
533			}
534

WillRy / querybuilder

Push — main ( 4e824a...56b906 )

Query::setBindings() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like