1<?php 2 3namespace Elgg\Database\Clauses; 4 5use Elgg\Database\QueryBuilder; 6use Elgg\Values; 7 8/** 9 * Utility class for building composite comparison expression 10 */ 11class ComparisonClause extends Clause { 12 13 /** 14 * @var string 15 */ 16 public $x; 17 18 /** 19 * @var string 20 */ 21 public $comparison; 22 23 /** 24 * @var mixed|null 25 */ 26 public $y; 27 28 /** 29 * @var null|string 30 */ 31 public $type; 32 33 /** 34 * @var bool|null 35 */ 36 public $case_sensitive; 37 38 /** 39 * Constructor 40 * 41 * @param string $x Comparison value (e.g. prefixed column name) 42 * @param string $comparison Comparison operator 43 * @param mixed $y Value to compare against 44 * @param string $type Value type for sanitization/casting 45 * @param bool $case_sensitive Use case sensitive comparison for strings 46 */ 47 public function __construct($x, $comparison, $y = null, $type = null, $case_sensitive = null) { 48 $this->x = $x; 49 $this->comparison = $comparison; 50 $this->y = $y; 51 $this->type = $type; 52 $this->case_sensitive = $case_sensitive; 53 } 54 55 /** 56 * {@inheritdoc} 57 * 58 * @throws \InvalidParameterException 59 */ 60 public function prepare(QueryBuilder $qb, $table_alias = null) { 61 $x = $this->x; 62 $y = $this->y; 63 $type = $this->type; 64 $case_sensitive = $this->case_sensitive; 65 66 switch ($type) { 67 case ELGG_VALUE_TIMESTAMP : 68 $y = Values::normalizeTimestamp($y); 69 $type = ELGG_VALUE_INTEGER; 70 break; 71 72 case ELGG_VALUE_GUID : 73 $y = Values::normalizeGuids($y); 74 $type = ELGG_VALUE_INTEGER; 75 break; 76 77 case ELGG_VALUE_ID : 78 $y = Values::normalizeIds($y); 79 $type = ELGG_VALUE_INTEGER; 80 break; 81 } 82 83 if (is_array($y) && count($y) === 1) { 84 $y = array_shift($y); 85 } 86 87 $compare_with = function ($func, $boolean = 'OR') use ($x, $y, $type, $case_sensitive, $qb) { 88 if (!isset($y)) { 89 return; 90 } 91 92 $parts = []; 93 foreach ((array) $y as $val) { 94 $val = $qb->param($val, $type); 95 if ($case_sensitive && $type == ELGG_VALUE_STRING) { 96 $val = "BINARY $val"; 97 } 98 $parts[] = $qb->expr()->$func($x, $val); 99 } 100 101 return $qb->merge($parts, $boolean); 102 }; 103 104 $match_expr = null; 105 $comparison = strtolower($this->comparison); 106 switch ($comparison) { 107 case '=' : 108 case 'eq' : 109 case 'in' : 110 if ($this->case_sensitive && $this->type == ELGG_VALUE_STRING) { 111 $x = "CAST($x as BINARY)"; 112 } 113 if (is_array($y) || $comparison === 'in') { 114 if (!empty($y)) { 115 $param = isset($type) ? $qb->param($y, $type) : $y; 116 $match_expr = $qb->expr()->in($x, $param); 117 } 118 } else if (isset($y)) { 119 $param = isset($type) ? $qb->param($y, $type) : $y; 120 $match_expr = $qb->expr()->eq($x, $param); 121 } 122 123 return $match_expr; 124 125 case '!=' : 126 case '<>' : 127 case 'neq' : 128 case 'not in' : 129 if ($this->case_sensitive && $this->type == ELGG_VALUE_STRING) { 130 $x = "CAST($x as BINARY)"; 131 } 132 if (is_array($y) || $comparison === 'not in') { 133 if (!empty($y)) { 134 $param = isset($type) ? $qb->param($y, $type) : $y; 135 $match_expr = $qb->expr()->notIn($x, $param); 136 } 137 } else if (isset($y)) { 138 $param = isset($type) ? $qb->param($y, $type) : $y; 139 $match_expr = $qb->expr()->neq($x, $param); 140 } 141 142 return $match_expr; 143 144 case 'like' : 145 return $compare_with('like'); 146 147 case 'not like' : 148 return $compare_with('notLike', 'AND'); 149 150 case '>': 151 case 'gt' : 152 return $compare_with('gt'); 153 154 case '<' : 155 case 'lt' : 156 return $compare_with('lt'); 157 158 case '>=' : 159 case 'gte' : 160 return $compare_with('gte'); 161 162 case '<=' : 163 case 'lte' : 164 return $compare_with('lte'); 165 166 case 'is null' : 167 return $qb->expr()->isNull($x); 168 169 case 'is not null' : 170 return $qb->expr()->isNotNull($x); 171 172 case 'exists' : 173 return "EXISTS ($y)"; 174 175 case 'not exists' : 176 return "NOT EXISTS ($y)"; 177 178 default : 179 throw new \InvalidParameterException("'{$this->comparison}' is not a supported comparison operator"); 180 } 181 } 182} 183