-
Notifications
You must be signed in to change notification settings - Fork 20
/
SearchPaneOptions.php
467 lines (406 loc) · 12.6 KB
/
SearchPaneOptions.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
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
<?php
/**
* DataTables PHP libraries.
*
* PHP libraries for DataTables and DataTables Editor, utilising PHP 5.3+.
*
* @author SpryMedia
* @copyright 2016 SpryMedia ( http://sprymedia.co.uk )
* @license http://editor.datatables.net/license DataTables Editor
* @link http://editor.datatables.net
*/
namespace DataTables\Editor;
if (!defined('DATATABLES')) exit();
use DataTables;
/**
* The Options class provides a convenient method of specifying where Editor
* should get the list of options for a `select`, `radio` or `checkbox` field.
* This is normally from a table that is _left joined_ to the main table being
* edited, and a list of the values available from the joined table is shown to
* the end user to let them select from.
*
* `Options` instances are used with the {@see Field->options()} method.
*
* @example
* Get a list of options from the `sites` table
* ```php
* Field::inst( 'users.site' )
* ->options( Options::inst()
* ->table( 'sites' )
* ->value( 'id' )
* ->label( 'name' )
* )
* ```
*
* @example
* Get a list of options with custom ordering
* ```php
* Field::inst( 'users.site' )
* ->options( Options::inst()
* ->table( 'sites' )
* ->value( 'id' )
* ->label( 'name' )
* ->order( 'name DESC' )
* )
* ```
*
* @example
* Get a list of options showing the id and name in the label
* ```php
* Field::inst( 'users.site' )
* ->options( Options::inst()
* ->table( 'sites' )
* ->value( 'id' )
* ->label( [ 'name', 'id' ] )
* ->render( function ( $row ) {
* return $row['name'].' ('.$row['id'].')';
* } )
* )
* ```
*/
class SearchPaneOptions extends DataTables\Ext {
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Private parameters
*/
/** @var string Table to get the information from */
private $_table = null;
/** @var string Column name containing the value */
private $_value = null;
/** @var string[] Column names for the label(s) */
private $_label = array();
/** @var string[] Column names for left join */
private $_leftJoin = array();
/** @var callable Callback function to do rendering of labels */
private $_renderer = null;
/** @var callback Callback function to add where conditions */
private $_where = null;
/** @var string ORDER BY clause */
private $_order = null;
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Public methods
*/
/**
* Get / set the column(s) to use as the label value of the options
*
* @param null|string|string[] $_ null to get the current value, string or
* array to get.
* @return Options|string[] Self if setting for chaining, array of values if
* getting.
*/
public function label ( $_=null )
{
if ( $_ === null ) {
return $this;
}
else if ( is_string($_) ) {
$this->_label = array( $_ );
}
else {
$this->_label = $_;
}
return $this;
}
/**
* Get / set the ORDER BY clause to use in the SQL. If this option is not
* provided the ordering will be based on the rendered output, either
* numerically or alphabetically based on the data returned by the renderer.
*
* @param null|string $_ String to set, null to get current value
* @return Options|string Self if setting for chaining, string if getting.
*/
public function order ( $_=null )
{
return $this->_getSet( $this->_order, $_ );
}
/**
* Get / set the label renderer. The renderer can be used to combine
* multiple database columns into a single string that is shown as the label
* to the end user in the list of options.
*
* @param null|callable $_ Function to set, null to get current value
* @return Options|callable Self if setting for chaining, callable if
* getting.
*/
public function render ( $_=null )
{
return $this->_getSet( $this->_renderer, $_ );
}
/**
* Get / set the database table from which to gather the options for the
* list.
*
* @param null|string $_ String to set, null to get current value
* @return Options|string Self if setting for chaining, string if getting.
*/
public function table ( $_=null )
{
return $this->_getSet( $this->_table, $_ );
}
/**
* Get / set the column name to use for the value in the options list. This
* would normally be the primary key for the table.
*
* @param null|string $_ String to set, null to get current value
* @return Options|string Self if setting for chaining, string if getting.
*/
public function value ( $_=null )
{
return $this->_getSet( $this->_value, $_ );
}
/**
* Get / set the method to use for a WHERE condition if it is to be
* applied to the query to get the options.
*
* @param null|callable $_ Function to set, null to get current value
* @return Options|callable Self if setting for chaining, callable if
* getting.
*/
public function where ( $_=null )
{
return $this->_getSet( $this->_where, $_ );
}
/**
* Get / set the array values used for a leftJoin condition if it is to be
* applied to the query to get the options.
*
* @param string $table to get the information from
* @param string $field1 the first field to get the information from
* @param string $operator the operation to perform on the two fields
* @param string $field2 the second field to get the information from
* @return self
*/
public function leftJoin ( $table, $field1, $operator, $field2 )
{
$this->_leftJoin[] = array(
"table" => $table,
"field1" => $field1,
"field2" => $field2,
"operator" => $operator
);
return $this;
}
/**
* Adds all of the where conditions to the desired query
*
* @param string $query the query being built
* @return self
*/
private function _get_where ( $query )
{
for ( $i=0 ; $i<count($this->_where) ; $i++ ) {
if ( is_callable( $this->_where[$i] ) ) {
$this->_where[$i]( $query );
}
else {
$query->where(
$this->_where[$i]['key'],
$this->_where[$i]['value'],
$this->_where[$i]['op']
);
}
}
return $this;
}
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Internal methods
*/
/**
* Execute the options (i.e. get them)
*
* @param Database $db Database connection
* @return array List of options
* @internal
*/
public function exec ( $field, $editor, $http, $fields, $leftJoinIn )
{
$db = $editor->db();
$readTable = $editor->readTable();
$filteringActive = isset($http['searchPanes']);
$viewCount = isset($http['searchPanes_options'])
? filter_var($http['searchPanes_options']['viewCount'], FILTER_VALIDATE_BOOLEAN)
: true;
$viewTotal = isset($http['searchPanes_options'])
? filter_var($http['searchPanes_options']['viewTotal'], FILTER_VALIDATE_BOOLEAN)
: false;
$cascade = isset($http['searchPanes_options'])
? filter_var($http['searchPanes_options']['cascade'], FILTER_VALIDATE_BOOLEAN)
: false;
$entries = null;
// If the value is not yet set then set the variable to be the field name
$value = $this->_value == null
? $field->dbField()
: $this->_value;
// If the table is not yet set then set the table variable to be the same as editor
// This is not taking a value from the SearchPaneOptions instance as the table should be defined in value/label. This throws up errors if not.
if($this->_table !== null) {
$table = $this->_table;
}
else if(count($readTable) > 0) {
$table = $readTable;
}
else {
$table = $editor->table();
}
// If the label value has not yet been set then just set it to be the same as value
$label = $this->_label == null
? $value
: $this->_label[0];
$formatter = $this->_renderer
? $this->_renderer
: function ( $str ) {
return $str;
};
// Set up the join variable so that it will fit nicely later
$leftJoin = gettype($this->_leftJoin) === 'array' ?
$this->_leftJoin :
array($this->_leftJoin);
foreach($leftJoinIn as $lj) {
$found = false;
foreach($leftJoin as $lje) {
if($lj['table'] === $lje['table']) {
$found = true;
}
}
if(!$found) {
array_push($leftJoin, $lj);
}
}
// Get the data for the pane options
$q = $db
->query('select')
->distinct(true)
->table( $table )
->get( $label." as label", $value." as value" )
->left_join($leftJoin)
->group_by( $value )
->where( $this->_where );
// If not cascading, then the total and count must be the same
if ($viewTotal) {
$q->get("COUNT(*) as total");
}
if ( $this->_order ) {
// For cases where we are ordering by a field which isn't included in the list
// of fields to display, we need to add the ordering field, due to the
// select distinct.
$orderFields = explode( ',', $this->_order );
for ( $i=0, $ien=count($orderFields) ; $i<$ien ; $i++ ) {
$orderField = strtolower( $orderFields[$i] );
$orderField = str_replace( ' asc', '', $orderField );
$orderField = str_replace( ' desc', '', $orderField );
$orderField = trim( $orderField );
if ( ! in_array( $orderField, $fields ) ) {
$q->get( $orderField );
}
}
$q->order( $this->_order );
}
$rows = $q
->exec()
->fetchAll();
// Remove any filtering entries that don't exist in the database (values might have changed)
if (isset($http['searchPanes'][$field->name()])) {
$values = array_column($rows, 'value');
$selected = $http['searchPanes'][$field->name()];
for ($i=0 ; $i<count($selected) ; $i++) {
$idx = array_search($selected[$i], $values);
if ($idx === false) {
array_splice($http['searchPanes'][$field->name()], $i, 1);
}
}
}
// Apply filters to cascade tables
if ($viewCount || $cascade) {
$query = $db
->query('select')
->distinct(true)
->table($table)
->left_join($leftJoin);
if ( $field->apply('get') && $field->getValue() === null ) {
$query->get($value." as value");
$query->group_by($value);
// We viewTotal is enabled, we need to do a count to get the number of records,
// If it isn't we still need to know it exists, but don't care about the cardinality
if ($viewCount) {
$query->get("COUNT(*) as count");
}
else {
$query->get("(1) as count");
}
}
// Construct the where queries based upon the options selected by the user
foreach ($fields as $fieldOpt) {
$add = false;
$fieldName = $fieldOpt->name();
// If there is a last value set then a slightly different set of results is required for cascade
// That panes results are based off of the results when only considering the selections of all of the others
if (isset($http['searchPanesLast']) && $field->name() === $http['searchPanesLast']) {
if (isset($http['searchPanes'][$fieldName]) && $fieldName !== $http['searchPanesLast']) {
$add = true;
}
}
else if (isset($http['searchPanes']) && isset($http['searchPanes'][$fieldName])) {
$add = true;
}
if ($add) {
$query->where( function ($q) use ($fieldOpt, $http, $fieldName) {
for($j=0, $jen=count($http['searchPanes'][$fieldName]); $j < $jen ; $j++) {
$q->or_where(
$fieldOpt->dbField(),
isset($http['searchPanes_null'][$fieldName][$j])
? null
: $http['searchPanes'][$fieldName][$j],
'='
);
}
});
}
}
$entriesRows = $query
->exec()
->fetchAll();
// Key by the value for fast lookup
$entriesKeys = array_column($entriesRows, 'value');
$entries = array_combine($entriesKeys, $entriesRows);
}
$out = array();
for ( $i=0, $ien=count($rows) ; $i<$ien ; $i++ ) {
$row = $rows[$i];
$value = $row['value'];
$total = isset($row['total']) ? $row['total'] : null;
$count = $total;
if ($entries !== null) {
$count = isset($entries[$value]) && isset($entries[$value]['count'])
? $entries[$value]['count']
: 0;
// For when viewCount is enabled and viewTotal is not
// the total needs to be the same as the count!
if ($total === null) {
$total = $count;
}
}
$out[] = array(
"label" => $formatter($row['label']),
"total" => $total,
"value" => $value,
"count" => $count
);
}
// Only sort if there was no SQL order field
if ( ! $this->_order ) {
usort( $out, function ( $a, $b ) {
$aLabel = $a['label'];
$bLabel = $b['label'];
if ($aLabel === null) {
$aLabel = '';
}
if ($bLabel === null) {
$bLabel = '';
}
return is_numeric($aLabel) && is_numeric($bLabel) ?
($aLabel*1) - ($bLabel*1) :
strcmp( $aLabel, $bLabel );
} );
}
return $out;
}
}