File Toolkit/Db/SqlQuery.php | salient/toolkit API

1:	<?php declare(strict_types=1);
2:
3:	namespace Salient\Db;
4:
5:	use Salient\Contract\Core\Entity\Readable;
6:	use Salient\Contract\Core\Chainable;
7:	use Salient\Core\Concern\ChainableTrait;
8:	use Salient\Core\Concern\ReadableTrait;
9:	use Salient\Utility\Get;
10:	use LogicException;
11:
12:	/**
13:	* A simple representation of a SQL query
14:	*
15:	* @property-read array<string,mixed> $Values Parameter name => value
16:	*/
17:	final class SqlQuery implements Chainable, Readable
18:	{
19:	use ChainableTrait;
20:	use ReadableTrait;
21:
22:	public const AND = 'AND';
23:	public const OR = 'OR';
24:
25:	/**
26:	* A list of optionally nested WHERE conditions
27:	*
28:	* To join a list of conditions with an explicit operator:
29:	*
30:	* ```php
31:	* <?php
32:	* [
33:	* '__' => SqlQuery::AND,
34:	* 'Id = ?',
35:	* 'Deleted IS NULL',
36:	* ]
37:	* ```
38:	*
39:	* To use nested conditions:
40:	*
41:	* ```php
42:	* <?php
43:	* [
44:	* '__' => SqlQuery::AND,
45:	* 'ItemKey = ?',
46:	* [
47:	* '__' => SqlQuery::OR,
48:	* 'Expiry IS NULL',
49:	* 'Expiry > ?',
50:	* ],
51:	* ]
52:	* ```
53:	*
54:	* @var array<string\|mixed[]>
55:	*/
56:	public $Where = [];
57:
58:	/**
59:	* Parameter name => value
60:	*
61:	* @var array<string,mixed>
62:	*/
63:	protected $Values = [];
64:
65:	/** @var callable(string): string */
66:	protected $ParamCallback;
67:
68:	/**
69:	* @inheritDoc
70:	*/
71:	public static function getReadableProperties(): array
72:	{
73:	return ['Values'];
74:	}
75:
76:	/**
77:	* @param callable(string): string $paramCallback Applied to the name of
78:	* each parameter added to the query.
79:	*/
80:	public function __construct(callable $paramCallback)
81:	{
82:	$this->ParamCallback = $paramCallback;
83:	}
84:
85:	/**
86:	* Add a parameter and assign its query placeholder to a variable
87:	*
88:	* @param mixed $value
89:	* @param-out string $placeholder
90:	* @return $this
91:	*/
92:	public function addParam(string $name, $value, ?string &$placeholder)
93:	{
94:	if (array_key_exists($name, $this->Values)) {
95:	throw new LogicException(sprintf('Parameter already added: %s', $name));
96:	}
97:
98:	$placeholder = ($this->ParamCallback)($name);
99:	$this->Values[$name] = $value;
100:
101:	return $this;
102:	}
103:
104:	/**
105:	* Add a WHERE condition
106:	*
107:	* @see SqlQuery::$Where
108:	*
109:	* @param (callable(): (string\|mixed[]))\|string\|mixed[] $condition
110:	* @return $this
111:	*/
112:	public function where($condition)
113:	{
114:	$this->Where[] = is_callable($condition) ? $condition() : $condition;
115:
116:	return $this;
117:	}
118:
119:	/**
120:	* Add a list of values as a WHERE condition ("<name> IN (<value>...)")
121:	* unless the list is empty
122:	*
123:	* @param mixed ...$value
124:	* @return $this
125:	*/
126:	public function whereValueInList(string $name, ...$value)
127:	{
128:	if (!$value) {
129:	return $this;
130:	}
131:
132:	foreach ($value as $val) {
133:	$expr[] = $this->addNextParam($val);
134:	}
135:	$this->Where[] = "$name IN (" . implode(',', $expr) . ')';
136:
137:	return $this;
138:	}
139:
140:	/**
141:	* Prepare a WHERE condition for use in a SQL statement
142:	*
143:	* @param array<string,mixed>\|null $values
144:	* @param-out array<string,mixed> $values
145:	*/
146:	public function getWhere(?array &$values = null): ?string
147:	{
148:	$values = $this->Values;
149:	$where = $this->buildWhere($this->Where);
150:
151:	return $where === ''
152:	? null
153:	: $where;
154:	}
155:
156:	/**
157:	* @param mixed $value
158:	*/
159:	private function addNextParam($value): string
160:	{
161:	$this->addParam('param_' . count($this->Values), $value, $param);
162:
163:	return $param;
164:	}
165:
166:	/**
167:	* @param array<string\|mixed[]> $where
168:	*/
169:	private function buildWhere(array $where): string
170:	{
171:	$glue = $where['__'] ?? self::AND;
172:	if (!is_string($glue)) {
173:	throw new LogicException(sprintf(
174:	'Invalid operator in WHERE condition: %s',
175:	Get::code($glue),
176:	));
177:	}
178:	unset($where['__']);
179:	/** @var string\|array<string\|mixed[]> $condition */
180:	foreach ($where as $condition) {
181:	if (is_array($condition)) {
182:	$condition = $this->buildWhere($condition);
183:	if ($condition === '') {
184:	continue;
185:	}
186:	$sql[] = "($condition)";
187:	} else {
188:	$sql[] = $condition;
189:	}
190:	}
191:
192:	return implode(" $glue ", $sql ?? []);
193:	}
194:	}
195:

Namespaces

Classes