1 <?php
  2 /**
  3  * Object representing a SQL query.
  4  * The various parts of the SQL query can be manipulated individually.
  5  * 
  6  * Caution: Only supports SELECT (default) and DELETE at the moment.
  7  * 
  8  * @todo Add support for INSERT and UPDATE queries
  9  * 
 10  * @package sapphire
 11  * @subpackage model
 12  */
 13 class SQLQuery {
 14     
 15     /**
 16      * An array of fields to select.
 17      * @var array
 18      */
 19     public $select = array();
 20     
 21     /**
 22      * An array of join clauses. The first one is just the table name.
 23      * @var array
 24      */
 25     public $from = array();
 26     
 27     /**
 28      * An array of filters.
 29      * @var array
 30      */
 31     public $where = array();
 32     
 33     /**
 34      * An ORDER BY clause.
 35      * @var string
 36      */
 37     public $orderby;
 38     
 39     /**
 40      * An array of fields to group by.
 41      * @var array
 42      */
 43     public $groupby = array();
 44     
 45     /**
 46      * An array of having clauses.
 47      * @var array
 48      */
 49     public $having = array();
 50     
 51     /**
 52      * A limit clause.
 53      * @var string
 54      */
 55     public $limit;
 56     
 57     /**
 58      * If this is true DISTINCT will be added to the SQL.
 59      * @var boolean
 60      */
 61     public $distinct = false;
 62     
 63     /**
 64      * If this is true, this statement will delete rather than select.
 65      * @var boolean
 66      */
 67     public $delete = false;
 68     
 69     /**
 70      * The logical connective used to join WHERE clauses. Defaults to AND.
 71      * @var string
 72      */
 73     public $connective = 'AND';
 74     
 75     /**
 76      * Keep an internal register of find/replace pairs to execute when it's time to actually get the
 77      * query SQL.
 78      */
 79     private $replacementsOld = array(), $replacementsNew = array();
 80     
 81     /**
 82      * Construct a new SQLQuery.
 83      * 
 84      * @param array $select An array of fields to select.
 85      * @param array $from An array of join clauses. The first one should be just the table name.
 86      * @param array $where An array of filters, to be inserted into the WHERE clause.
 87      * @param string $orderby An ORDER BY clause.
 88      * @param array $groupby An array of fields to group by.
 89      * @param array $having An array of having clauses.
 90      * @param string $limit A LIMIT clause.
 91      * 
 92      * TODO: perhaps we can quote things here instead of requiring all the parameters to be quoted
 93      * by this stage.
 94      */
 95     function __construct($select = "*", $from = array(), $where = "", $orderby = "", $groupby = "", $having = "", $limit = "") {
 96         $this->select($select);
 97         // @todo 
 98         $this->from = is_array($from) ? $from : array(str_replace(array('"','`'),'',$from) => $from);
 99         $this->where($where);
100         $this->orderby($orderby);
101         $this->groupby($groupby);
102         $this->having($having);
103         $this->limit($limit);
104     }
105     
106     /**
107      * Specify the list of columns to be selected by the query.
108      *
109      * <code>
110      *  // pass fields to select as single parameter array
111      *  $query->select(array("Col1","Col2"))->from("MyTable");
112      * 
113      *  // pass fields to select as multiple parameters
114      *  $query->select("Col1", "Col2")->from("MyTable");
115      * </code>
116      * 
117      * @param mixed $fields
118      * @return SQLQuery
119      */
120     public function select($fields) {
121         if (func_num_args() > 1) {
122             $this->select = func_get_args();
123         } else {
124             $this->select = is_array($fields) ? $fields : array($fields);
125         }
126         
127         return $this;
128     }
129     
130     /**
131      * Specify the target table to select from.
132      * 
133      * <code>
134      *  $query->from("MyTable"); // SELECT * FROM MyTable
135      * </code>
136      *
137      * @param string $table
138      * @return SQLQuery This instance
139      */
140     public function from($table) {
141         $this->from[str_replace(array('"','`'),'',$table)] = $table;
142         
143         return $this;
144     }
145     
146     /**
147      * Add a LEFT JOIN criteria to the FROM clause.
148      * 
149      * @return SQLQuery This instance 
150      */
151     public function leftJoin($table, $onPredicate) {
152         $this->from[$table] = "LEFT JOIN \"$table\" ON $onPredicate";
153         return $this;
154     }
155     
156     /**
157      * Add an INNER JOIN criteria to the FROM clause.
158      * 
159      * @return SQLQuery This instance 
160      */
161     public function innerJoin($table, $onPredicate) {
162         $this->from[$table] = "INNER JOIN \"$table\" ON $onPredicate";
163         return $this;
164     }
165     
166     /**
167      * Returns true if we are already joining to the given table alias
168      */
169     public function isJoinedTo($tableAlias) {
170         return isset($this->from[$tableAlias]);
171     }
172     
173     /**
174      * Pass LIMIT clause either as SQL snippet or in array format.
175      *
176      * @param string|array $limit
177      * @return SQLQuery This instance
178      */
179     public function limit($limit) {
180         $this->limit = $limit;
181         
182         return $this;
183     }
184     
185     /**
186      * Pass ORDER BY clause either as SQL snippet or in array format.
187      *
188      * @todo Implement passing of multiple orderby pairs in nested array syntax,
189      *  e.g. array(array('sort'=>'A','dir'=>'asc'),array('sort'=>'B'))
190      * 
191      * @param string|array $orderby
192      * @return SQLQuery This instance
193      */
194     public function orderby($orderby) {
195         // if passed as an array, assume two array values with column and direction (asc|desc) 
196         if(is_array($orderby)) {
197             if(!array_key_exists('sort', $orderby)) user_error('SQLQuery::orderby(): Wrong format for $orderby array', E_USER_ERROR);
198 
199             if(isset($orderby['sort']) && !empty($orderby['sort']) && isset($orderby['dir']) && !empty($orderby['dir'])) {
200                 $combinedOrderby = "\"" . Convert::raw2sql($orderby['sort']) . "\" " . Convert::raw2sql(strtoupper($orderby['dir']));
201             } elseif(isset($orderby['sort']) && !empty($orderby['sort'])) {
202                 $combinedOrderby = "\"" . Convert::raw2sql($orderby['sort']) . "\"";
203             } else {
204                 $combinedOrderby = false;
205             }
206         } else {
207             $combinedOrderby = $orderby;
208         }
209         
210         // If sort contains a function call, let's move the sort clause into a separate selected field.
211         // Some versions of MySQL choke if you have a group function referenced directly in the ORDER BY
212         if($combinedOrderby && strpos($combinedOrderby,'(') !== false && strtoupper(trim($combinedOrderby)) != DB::getConn()->random()) {
213             // Sort can be "Col1 DESC|ASC, Col2 DESC|ASC", we need to handle that
214             $sortParts = explode(",", $combinedOrderby);
215                 
216             // If you have select if(X,A,B),C then the array will return 'if(X','A','B)','C'.
217             // Turn this into 'if(X,A,B)','C' by counting brackets
218             while(list($i,$sortPart) = each($sortParts)) {
219                 while(substr_count($sortPart,'(') > substr_count($sortPart,')')) {
220                     list($i,$nextSortPart) = each($sortParts);
221                     if($i === null) break;
222                     $sortPart .= ',' . $nextSortPart;
223                 }
224                 $lumpedSortParts[] = $sortPart;
225             }
226                 
227             foreach($lumpedSortParts as $i => $sortPart) {
228                 $sortPart = trim($sortPart);
229                 if(substr(strtolower($sortPart),-5) == ' desc') {
230                     $this->select[] = substr($sortPart,0,-5) . " AS _SortColumn{$i}";
231                     $newSorts[] = "_SortColumn{$i} DESC";
232                 } else if(substr(strtolower($sortPart),-4) == ' asc') {
233                     $this->select[] = substr($sortPart,0,-4) . " AS _SortColumn{$i}";
234                     $newSorts[] = "_SortColumn{$i} ASC";
235                 } else {
236                     $this->select[] = "$sortPart AS _SortColumn{$i}";
237                     $newSorts[] = "_SortColumn{$i} ASC";
238                 }
239             }
240                 
241             $combinedOrderby =  implode(", ", $newSorts);
242         }
243         
244         if(!empty($combinedOrderby)) $this->orderby = $combinedOrderby;
245         
246         return $this;
247     }
248     
249     /**
250      * Add a GROUP BY clause.
251      *
252      * @param string|array $groupby
253      * @return SQLQuery
254      */
255     public function groupby($groupby) {
256         if(is_array($groupby)) {
257             $this->groupby = array_merge($this->groupby, $groupby);  
258         } elseif(!empty($groupby)) {
259             $this->groupby[] = $groupby;
260         }
261         
262         return $this;
263     }
264 
265     /**
266      * Add a HAVING clause.
267      *
268      * @param string|array $having
269      * @return SQLQuery
270      */
271     public function having($having) {
272         if(is_array($having)) {
273             $this->having = array_merge($this->having, $having);  
274         } elseif(!empty($having)) {
275             $this->having[] = $having;
276         }
277         
278         return $this;
279     }
280     
281     /**
282      * Apply a predicate filter to the where clause.
283      * 
284      * Accepts a variable length of arguments, which represent
285      * different ways of formatting a predicate in a where clause:
286      * 
287      * <code>
288      *  // the entire predicate as a single string
289      *  $query->where("Column = 'Value'");
290      * 
291      *  // an exact match predicate with a key value pair
292      *  $query->where("Column", "Value");
293      * 
294      *  // a predicate with user defined operator
295      *  $query->where("Column", "!=", "Value");
296      * </code>
297      * 
298      */
299     public function where() {
300         $args = func_get_args();
301         if (func_num_args() == 3) {
302             $filter = "{$args[0]} {$args[1]} '{$args[2]}'";
303         } elseif (func_num_args() == 2) {
304             $filter = "{$args[0]} = '{$args[1]}'";
305         } else {
306             $filter = $args[0];
307         }
308         
309         if(is_array($filter)) {
310             $this->where = array_merge($this->where,$filter);
311         } elseif(!empty($filter)) {
312             $this->where[] = $filter;
313         }
314         
315         return $this;
316     }
317     
318     /**
319      * Use the disjunctive operator 'OR' to join filter expressions in the WHERE clause.
320      */
321     public function useDisjunction() {
322         $this->connective = 'OR';
323     }
324 
325     /**
326      * Use the conjunctive operator 'AND' to join filter expressions in the WHERE clause.
327      */
328     public function useConjunction() {
329         $this->connective = 'AND';
330     }
331     
332     /**
333      * Swap the use of one table with another.
334      * @param string $old Name of the old table.
335      * @param string $new Name of the new table.
336      */
337     function renameTable($old, $new) {
338         $this->replaceText("`$old`", "`$new`");
339         $this->replaceText("\"$old\"", "\"$new\"");
340     }
341     
342     /**
343      * Swap some text in the SQL query with another.
344      * @param string $old The old text.
345      * @param string $new The new text.
346      */
347     function replaceText($old, $new) {
348         $this->replacementsOld[] = $old;
349         $this->replacementsNew[] = $new;
350     }
351 
352     /**
353      * Return an SQL WHERE clause to filter a SELECT query.
354      *
355      * @return string
356      */
357     function getFilter() {
358         return ($this->where) ? implode(") {$this->connective} (" , $this->where) : '';
359     }
360     
361     /**
362      * Generate the SQL statement for this query.
363      * 
364      * @return string
365      */
366     function sql() {
367         $sql = DB::getConn()->sqlQueryToString($this);
368         if($this->replacementsOld) $sql = str_replace($this->replacementsOld, $this->replacementsNew, $sql);
369         return $sql;
370     }
371     
372     /**
373      * Return the generated SQL string for this query
374      * 
375      * @return string
376      */
377     function __toString() {
378         return $this->sql();
379     }
380     
381     /**
382      * Execute this query.
383      * @return SS_Query
384      */
385     function execute() {
386         return DB::query($this->sql(), E_USER_ERROR);
387     }
388     
389     /**
390      * Checks whether this query is for a specific ID in a table
391      * 
392      * @todo Doesn't work with combined statements (e.g. "Foo='bar' AND ID=5")
393      *
394      * @return boolean
395      */
396     function filtersOnID() {
397         $regexp = '/^(.*\.)?("|`)?ID("|`)?\s?=/';
398         
399         // Sometimes the ID filter will be the 2nd element, if there's a ClasssName filter first.
400         if(isset($this->where[0]) && preg_match($regexp, $this->where[0])) return true;
401         if(isset($this->where[1]) && preg_match($regexp, $this->where[1])) return true;
402         
403         return  false;
404     }
405     
406     /**
407      * Checks whether this query is filtering on a foreign key, ie finding a has_many relationship
408      * 
409      * @todo Doesn't work with combined statements (e.g. "Foo='bar' AND ParentID=5")
410      *
411      * @return boolean
412      */
413     function filtersOnFK() { 
414         return (
415             $this->where
416             && preg_match('/^(.*\.)?("|`)?[a-zA-Z]+ID("|`)?\s?=/', $this->where[0])
417         );
418     }
419     
420     /// VARIOUS TRANSFORMATIONS BELOW
421     
422     /**
423      * Return the number of rows in this query if the limit were removed.  Useful in paged data sets. 
424      * @return int 
425      */ 
426     function unlimitedRowCount( $column = null) {
427         // we can't clear the select if we're relying on its output by a HAVING clause
428         if(count($this->having)) {
429             $records = $this->execute();
430             return $records->numRecords();
431         }
432 
433         $clone = clone $this;
434         $clone->limit = null;
435         $clone->orderby = null;
436 
437         // Choose a default column
438         if($column == null) {
439             if($this->groupby) {
440                 $countQuery = new SQLQuery();
441                 $countQuery->select = array("count(*)");
442                 $countQuery->from = array('(' . $clone->sql() . ') as all_distinct');
443 
444                 return $countQuery->execute()->value();
445 
446             } else {
447                 $clone->select = array("count(*)");
448             }
449         } else {
450             $clone->select = array("count($column)");
451         }
452 
453         $clone->groupby = null;
454         return $clone->execute()->value();
455     }
456 
457     /**
458      * Returns true if this query can be sorted by the given field.
459      * Note that the implementation of this method is a little crude at the moment, it wil return
460      * "false" more often that is strictly necessary.
461      */
462     function canSortBy($fieldName) {
463         $fieldName = preg_replace('/(\s+?)(A|DE)SC$/', '', $fieldName);
464         
465         $sql = $this->sql();
466     
467         $selects = $this->select;
468         foreach($selects as $i => $sel) {
469             if (preg_match('/"(.*)"\."(.*)"/', $sel, $matches)) $selects[$i] = $matches[2];
470         }
471     
472         $SQL_fieldName = Convert::raw2sql($fieldName);
473         return (in_array($SQL_fieldName,$selects) || stripos($sql,"AS {$SQL_fieldName}"));
474     }
475 
476 }
477 
478 ?>