forked from timgws/QueryBuilderParser
-
Notifications
You must be signed in to change notification settings - Fork 0
/
QueryBuilderParser.php
294 lines (255 loc) · 8.6 KB
/
QueryBuilderParser.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
<?php
namespace timgws;
use \stdClass;
use \Illuminate\Database\Query\Builder;
use \timgws\QBParseException;
class QueryBuilderParser
{
use QBPFunctions;
protected $fields;
/**
* @param array $fields a list of all the fields that are allowed to be filtered by the QueryBuilder
*/
public function __construct(array $fields = null)
{
$this->fields = $fields;
}
/**
* QueryBuilderParser's parse function!
*
* Build a query based on JSON that has been passed into the function, onto the builder passed into the function.
*
* @param $json
* @param Builder $querybuilder
*
* @throws QBParseException
*
* @return Builder
*/
public function parse($json, Builder $querybuilder)
{
// do a JSON decode (throws exceptions if there is a JSON error...)
$query = $this->decodeJSON($json);
// This can happen if the querybuilder had no rules...
if (!isset($query->rules) || !is_array($query->rules)) {
return $querybuilder;
}
// This shouldn't ever cause an issue, but may as well not go through the rules.
if (count($query->rules) < 1) {
return $querybuilder;
}
return $this->loopThroughRules($query->rules, $querybuilder, $query->condition);
}
/**
* Called by parse, loops through all the rules to find out if nested or not.
*
* @param array $rules
* @param Builder $querybuilder
* @param string $queryCondition
*
* @throws QBParseException
*
* @return Builder
*/
protected function loopThroughRules(array $rules, Builder $querybuilder, $queryCondition = 'AND')
{
foreach ($rules as $rule) {
/*
* If makeQuery does not see the correct fields, it will return the QueryBuilder without modifications
*/
$querybuilder = $this->makeQuery($querybuilder, $rule, $queryCondition);
if ($this->isNested($rule)) {
$querybuilder = $this->createNestedQuery($querybuilder, $rule, $queryCondition);
}
}
return $querybuilder;
}
/**
* Determine if a particular rule is actually a group of other rules.
*
* @param $rule
*
* @return bool
*/
protected function isNested($rule)
{
if (isset($rule->rules) && is_array($rule->rules) && count($rule->rules) > 0) {
return true;
}
return false;
}
/**
* Create nested queries
*
* When a rule is actually a group of rules, we want to build a nested query with the specified condition (AND/OR)
*
* @param Builder $querybuilder
* @param stdClass $rule
* @param string|null $condition
* @return Builder
*/
protected function createNestedQuery(Builder $querybuilder, stdClass $rule, $condition = null)
{
if ($condition === null) {
$condition = $rule->condition;
}
$condition = $this->validateCondition($condition);
return $querybuilder->whereNested(function($query) use (&$rule, &$querybuilder, &$condition) {
foreach ($rule->rules as $loopRule) {
$function = 'makeQuery';
if ($this->isNested($loopRule)) {
$function = 'createNestedQuery';
}
$querybuilder = $this->{$function}($query, $loopRule, $rule->condition);
}
}, $condition);
}
/**
* Check if a given rule is correct.
*
* Just before making a query for a rule, we want to make sure that the field, operator and value are set
*
* @param stdClass $rule
*
* @return bool true if values are correct.
*/
protected function checkRuleCorrect(stdClass $rule)
{
if (!isset($rule->operator, $rule->id, $rule->field, $rule->type)) {
return false;
}
if (!isset($this->operators[$rule->operator])) {
return false;
}
return true;
}
/**
* Give back the correct value when we don't accept one.
*
* @param $rule
*
* @return null|string
*/
protected function operatorValueWhenNotAcceptingOne(stdClass $rule)
{
if ($rule->operator == 'is_empty' || $rule->operator == 'is_not_empty') {
return '';
}
return null;
}
/**
* Ensure that the value for a field is correct.
*
* Append/Prepend values for SQL statements, etc.
*
* @param $operator
* @param stdClass $rule
* @param $value
*
* @throws QBParseException
*
* @return string
*/
protected function getCorrectValue($operator, stdClass $rule, $value)
{
$field = $rule->field;
$sqlOperator = $this->operator_sql[$rule->operator];
$requireArray = $this->operatorRequiresArray($operator);
$value = $this->enforceArrayOrString($requireArray, $value, $field);
return $this->appendOperatorIfRequired($requireArray, $value, $sqlOperator);
}
/**
* makeQuery: The money maker!
*
* Take a particular rule and make build something that the QueryBuilder would be proud of.
*
* Make sure that all the correct fields are in the rule object then add the expression to
* the query that was given by the user to the QueryBuilder.
*
* @param Builder $query
* @param stdClass $rule
* @param string $queryCondition and/or...
*
* @throws QBParseException
*
* @return Builder
*/
protected function makeQuery(Builder $query, stdClass $rule, $queryCondition = 'AND')
{
/*
* Ensure that the value is correct for the rule, return query on exception
*/
try {
$value = $this->getValueForQueryFromRule($rule);
} catch (QBRuleException $e) {
return $query;
}
return $this->convertIncomingQBtoQuery($query, $rule, $value, $queryCondition);
}
/**
* Convert an incomming rule from jQuery QueryBuilder to the Eloquent Querybuilder
*
* (This used to be part of makeQuery, where the name made sense, but I pulled it
* out to reduce some duplicated code inside JoinSupportingQueryBuilder)
*
* @param Builder $query
* @param stdClass $rule
* @param mixed $value the value that needs to be queried in the database.
* @param string $queryCondition and/or...
* @return Builder
*/
protected function convertIncomingQBtoQuery(Builder $query, stdClass $rule, $value, $queryCondition = 'AND')
{
/*
* Convert the Operator (LIKE/NOT LIKE/GREATER THAN) given to us by QueryBuilder
* into on one that we can use inside the SQL query
*/
$sqlOperator = $this->operator_sql[$rule->operator];
$operator = $sqlOperator['operator'];
$condition = strtolower($queryCondition);
if ($this->operatorRequiresArray($operator)) {
return $this->makeQueryWhenArray($query, $rule, $sqlOperator, $value, $condition);
} elseif($this->operatorIsNull($operator)) {
return $this->makeQueryWhenNull($query, $rule, $sqlOperator, $condition);
}
return $query->where($rule->field, $sqlOperator['operator'], $value, $condition);
}
/**
* Ensure that the value is correct for the rule, try and set it if it's not.
*
* @param stdClass $rule
*
* @throws QBRuleException
* @throws \timgws\QBParseException
*
* @return mixed
*/
protected function getValueForQueryFromRule(stdClass $rule)
{
/*
* Make sure most of the common fields from the QueryBuilder have been added.
*/
$value = $this->getRuleValue($rule);
/*
* The field must exist in our list.
*/
$this->ensureFieldIsAllowed($this->fields, $rule->field);
/*
* If the SQL Operator is set not to have a value, make sure that we set the value to null.
*/
if ($this->operators[$rule->operator]['accept_values'] === false) {
return $this->operatorValueWhenNotAcceptingOne($rule);
}
/*
* Convert the Operator (LIKE/NOT LIKE/GREATER THAN) given to us by QueryBuilder
* into on one that we can use inside the SQL query
*/
$sqlOperator = $this->operator_sql[$rule->operator];
$operator = $sqlOperator['operator'];
/*
* \o/ Ensure that the value is an array only if it should be.
*/
$value = $this->getCorrectValue($operator, $rule, $value);
return $value;
}
}