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
|
<?php
class HardwareQueryColumn
{
/** @var int For unique table names in join */
private static $id = 0;
private $global;
private $tableAlias;
private $virtualColumnName;
private $alias;
private $conditions = [];
private $params = [];
private $classId;
private static function getId(): string
{
return 't' . ++self::$id;
}
public function __construct(bool $global, string $column, string $alias = null)
{
$this->classId = ++self::$id;
$this->global = $global;
$this->tableAlias = self::getId();
$this->virtualColumnName = $column;
$this->alias = '`' . ($alias ?? $column) . '`';
}
/**
* Add necessary conditions, joins, columns to final SQL arrays. To be called
* from HardwareQuery::buildQuery().
* @param string[] $groupConcat if column name is NOT in this array, add as distinct GROUP_CONCAT to column.
*/
public function generate(array &$joins, array &$columns, array &$params, array $groupConcat = [], string $globalSrcTableAlias = null)
{
if ($this->global) {
$srcTable = $globalSrcTableAlias ?? 'shw';
$table = 'statistic_hw_prop';
$column = 'hwid';
} else {
$srcTable = 'mxhw';
$table = 'machine_x_hw_prop';
$column = 'machinehwid';
}
$tid = $this->tableAlias;
$pid = self::getId();
$this->conditions[] = "$srcTable.$column = $tid.$column AND $tid.prop = :$pid";
$params[$pid] = $this->virtualColumnName; // value of property column is our virtual column
// If we have just one condition, it's the join condition itself. Since we pretend we're just adding
// a column to the query, do a left join, so the "column" is NULL if the join doesn't match.
// If however any conditions were added to this class via the addCondition() method, do a regular
// INNER JOIN, so the result will be empty if the condition doesn't match.
$type = count($this->conditions) === 1 ? 'LEFT' : 'INNER';
$joins[] = "$type JOIN $table $tid ON (" . implode(' AND ', $this->conditions) . ")";
if (!empty($groupConcat) && !in_array($this->alias, $groupConcat)) {
$columns[] = "Group_Concat(DISTINCT $tid.`value` SEPARATOR ', ') AS {$this->alias}";
} else {
$columns[] = "$tid.`value` AS {$this->alias}";
}
$params += $this->params;
}
/**
* @param string $op Operator (<>=, IN, LIKE)
* @param string|string[]|HardwareQueryColumn $other value to compare with.
* Can be a literal, an array (if opererator is IN), or another Column
* @return void
*/
public function addCondition(string $op, $other)
{
$valueCol = ($op === '<' || $op === '>' || $op === '<=' || $op === '>=') ? 'numeric' : 'value';
if ($other instanceof HardwareQueryColumn) {
$cond = "{$this->tableAlias}.`$valueCol` $op {$other->tableAlias}.`$valueCol`";
// Don't reference a column of a table that hasn't been joined yet
if ($this->classId > $other->classId) {
$this->conditions[] = $cond;
} else {
$other->conditions[] = $cond;
}
} elseif ($op === '~' || $op === '!~') {
$op = $op === '~' ? 'LIKE' : 'NOT LIKE';
$other = str_replace(array('=', '_', '%', '*', '?'), array('==', '=_', '=%', '%', '_'), $other);
$pid = self::getId();
$this->conditions[] = "{$this->tableAlias}.`$valueCol` $op (:$pid) ESCAPE '='";
$this->params[$pid] = $other;
} else {
$pid = self::getId();
$this->conditions[] = "{$this->tableAlias}.`$valueCol` $op (:$pid)";
$this->params[$pid] = $other;
}
}
}
|
