Version: current

      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 ?>

[Raise a SilverStripe Framework issue/bug](https://github.com/silverstripe/silverstripe-framework/issues/new)
- [Raise a SilverStripe CMS issue/bug](https://github.com/silverstripe/silverstripe-cms/issues/new)
- Please use the Silverstripe Forums to ask development related questions. -