• Main Page
  • Related Pages
  • Namespaces
  • Classes
  • Files
  • Examples
  • File List

system/classes/query.php

00001 <?php
00002 
00003 namespace Habari;
00004 
00009 class Query {
00010 
00012   private $where = null;
00013   public $primary_table = null;
00014   protected $fields = array();
00015   protected $joins = array();
00016   protected $join_params = array();
00017   protected $limit = null;
00018   protected $offset = null;
00019   protected $orderby = null;
00020   protected $groupby = null;
00021   protected $having = null;
00022 
00029   public function __construct($primary_table = null)
00030   {
00031     $this->primary_table = $primary_table;
00032   }
00033 
00042   public static function create($primary_table = null)
00043   {
00044     return new Query($primary_table);
00045   }
00046 
00052   public function select($fields)
00053   {
00054     $args = func_get_args();
00055     foreach($args as $fields) {
00056       $this->fields = array_merge($this->fields, Utils::single_array($fields));
00057     }
00058     return $this;
00059   }
00060 
00066   public function set_select($fields)
00067   {
00068     $this->fields = Utils::single_array($fields);
00069     return $this;
00070   }
00071 
00077   public function distinct($set = true)
00078   {
00079     $this->distinct = $set;
00080     return $this;
00081   }
00082 
00088   public function from($primary_table)
00089   {
00090     $this->primary_table = $primary_table;
00091     return $this;
00092   }
00093 
00101   public function join($join, $parameters = array(), $alias = null)
00102   {
00103     if(empty($alias)) {
00104       $alias = md5($join);
00105     }
00106     $this->joins[$alias] = $join;
00107     $this->join_params = array_merge($this->join_params, $parameters);
00108     return $this;
00109   }
00110 
00116   public function joined($alias)
00117   {
00118     return array_key_exists($alias, $this->joins);
00119   }
00120 
00126   public function where($operator = 'AND')
00127   {
00128     if(!isset($this->where)) {
00129       $this->where = new QueryWhere($operator);
00130     }
00131     return $this->where;
00132   }
00133 
00139   public function groupby($value)
00140   {
00141     $this->groupby = empty($value) ? null : $value;
00142     return $this;
00143   }
00144 
00150   public function orderby($value)
00151   {
00152     $this->orderby = empty($value) ? null : $value;
00153     return $this;
00154   }
00155 
00156   public function having($value)
00157   {
00158     $this->having = empty($value) ? null : $value;
00159     return $this;
00160   }
00161 
00167   public function limit($value)
00168   {
00169     $this->limit = is_numeric($value) ? intval($value) : null;
00170     return $this;
00171   }
00172 
00178   public function offset($value)
00179   {
00180     $this->offset = is_numeric($value) ? intval($value) : null;
00181     return $this;
00182   }
00183 
00188   public function get()
00189   {
00190     $fields = $this->fields;
00191 
00192     // If the orderby has a function in it, try to create a select field for it with an alias
00193     $orderby = null;
00194     if(isset($this->orderby)) {
00195       $orderby = $this->orderby;
00196       if ( strpos( $orderby, '(' ) !== false ) {
00197         $orders = explode( ',', $orderby );
00198         $ob_index = 0;
00199         foreach ( $orders as $key => $order ) {
00200           if ( !preg_match( '%(?P<field>.+)\s+(?P<direction>DESC|ASC)%i', $order, $order_matches ) ) {
00201             $order_matches = array(
00202               'field' => $order,
00203               'direction' => '',
00204             );
00205           }
00206 
00207           if ( strpos( $order_matches['field'], '(' ) !== false ) {
00208             $ob_index++;
00209             $field = 'orderby' . $ob_index;
00210             $fields[$field] = "{$order_matches['field']} AS $field";
00211             $orders[$key] = $field . ' ' . $order_matches['direction'];
00212           }
00213         }
00214         $orderby = implode( ', ', $orders );
00215       }
00216     }
00217 
00218     $sql = "SELECT \n\t";
00219     if($this->distinct()) {
00220       $sql .= "DISTINCT \n\t";
00221     }
00222     if(count($fields) > 0) {
00223       $sql .= implode(",\n\t", $fields);
00224     }
00225     else {
00226       $sql .= "*";
00227     }
00228     $sql .= "\nFROM\n\t" . $this->primary_table;
00229     foreach($this->joins as $join) {
00230       $sql .= "\n" . $join;
00231     }
00232     $where = $this->where()->get();
00233     if(!empty($where)) {
00234       $sql .= "\nWHERE\n" . $this->where()->get();
00235     }
00236 
00237     if(isset($this->groupby)) {
00238       $sql .= "\nGROUP BY " . $this->groupby;
00239     }
00240 
00241     if(isset($this->having)) {
00242       $sql .= "\nHAVING " . $this->having;
00243     }
00244     if(isset($orderby)) {
00245       $sql .= "\nORDER BY " . $orderby;
00246     }
00247 
00248     if(isset($this->limit)) {
00249       $sql .= "\nLIMIT " . $this->limit;
00250       if(isset($this->offset)) {
00251         $sql .= "\nOFFSET " . $this->offset;
00252       }
00253     }
00254 
00255     return $sql;
00256   }
00257 
00262   public function params()
00263   {
00264     return array_merge($this->where()->params(), $this->join_params);
00265   }
00266 
00273   public static function new_param_name($prefix = 'param')
00274   {
00275     static $param_names = array();
00276 
00277     if(!isset($param_names[$prefix])) {
00278       $param_names[$prefix] = 0;
00279     }
00280     $param_names[$prefix]++;
00281     return $prefix . '_' . $param_names[$prefix];
00282   }
00283 
00289   public function row($class = null)
00290   {
00291     return DB::get_row($this->get(), $this->params(), $class);
00292   }
00293 
00299   public function results($class = null)
00300   {
00301     return DB::get_results($this->get(), $this->params(), $class);
00302   }
00303 
00308   public function keyvalue()
00309   {
00310     return DB::get_keyvalue($this->get(), $this->params());
00311   }
00312 
00317   public function column()
00318   {
00319     return DB::get_column($this->get(), $this->params());
00320   }
00321 
00326   public function value()
00327   {
00328     return DB::get_value($this->get(), $this->params());
00329   }
00330 }
00331 
00337 class QueryWhere {
00338   protected $operator = 'AND';
00339   protected $expressions = array();
00340   protected $parameters = array();
00341 
00346   public function __construct($operator = 'AND')
00347   {
00348     $this->operator = $operator;
00349   }
00350 
00356   public function create($operator = 'AND')
00357   {
00358     return new QueryWhere($operator);
00359   }
00360 
00368   public function add($expression, $parameters = array(), $name = null)
00369   {
00370     if(empty($name)) {
00371       $name = count($this->expressions) + 1;
00372     }
00373     $this->expressions[$name] = $expression;
00374     $this->parameters = array_merge($this->parameters, $parameters);
00375     return $this;
00376   }
00377 
00383   public function get_named($name)
00384   {
00385     if(isset($this->expressions[$name])) {
00386       return $this->expressions[$name];
00387     }
00388     return null;
00389   }
00390 
00400   public function in($field, $values, $paramname = null, $validator = null, $positive = true)
00401   {
00402     $expression = $field . ' ';
00403     if($values instanceof Query) {
00404       if( !$positive ) {
00405         $expression .= 'NOT ';
00406       }
00407       $expression .= 'IN (' . $values->get() . ')';
00408       $this->parameters = array_merge( $this->parameters, $values->params() );
00409     }
00410     elseif(is_array($values) && count($values) > 1) {
00411       $in_elements = array();
00412       if(is_callable($validator)) {
00413         foreach($values as $value) {
00414           $newvalue = $validator($value);
00415           if(!empty($newvalue)) {
00416             $in_elements[] = $newvalue;
00417           }
00418         }
00419       }
00420       else {
00421         foreach($values as $value) {
00422           $value_name = Query::new_param_name($paramname);
00423           $in_elements[] = ':' . $value_name;
00424           $this->parameters[$value_name] = $value;
00425         }
00426       }
00427       if(!$positive) {
00428         $expression .= 'NOT ';
00429       }
00430       $expression .= 'IN (' . implode(',', $in_elements) . ')';
00431     }
00432     else {
00433       if(is_array($values)) {
00434         $values = reset($values);
00435       }
00436       if(!$positive) {
00437         $expression .= ' <> ';
00438       }
00439       else {
00440         $expression .= ' = ';
00441       }
00442 
00443       if(empty($paramname)) {
00444         $paramname = Query::new_param_name();
00445       }
00446 
00447       if(is_callable($validator)) {
00448         $expression .= $validator($values);
00449       }
00450       else {
00451         $expression .= ':' . $paramname;
00452         $this->parameters[$paramname] = $values;
00453       }
00454     }
00455 
00456     if(empty($paramname)) {
00457       $paramname = count($this->expressions) + 1;
00458     }
00459 
00460     $this->expressions[$paramname] = $expression;
00461     return $this;
00462   }
00463 
00471   public function exists( Query $values, $paramname = null, $positive = true )
00472   {
00473     $expression = '';
00474 
00475     if( !$positive ) {
00476       $expression .= 'NOT ';
00477     }
00478 
00479     $expression .= 'EXISTS (' . $values->get() . ')';
00480 
00481     $this->parameters = array_merge( $this->parameters, $values->params() );
00482 
00483     if( empty( $paramname ) ) {
00484       $paramname = count( $this->expressions ) + 1;
00485     }
00486 
00487     $this->expressions[$paramname] = $expression;
00488     return $this;
00489   }
00490 
00495   public function params()
00496   {
00497     $parameters = $this->parameters;
00498     foreach($this->expressions as $expression) {
00499       if($expression instanceof Query) {
00500         $parameters = array_merge($parameters, $expression->params());
00501       }
00502       if($expression instanceof QueryWhere) {
00503         $parameters = array_merge($parameters, $expression->params());
00504       }
00505     }
00506     return $parameters;
00507   }
00508 
00515   public function __set($name, $value)
00516   {
00517     $this->parameters[$name] = $value;
00518     return $this->parameters[$name];
00519   }
00520 
00526   public function __get($name)
00527   {
00528     return $this->parameters[$name];
00529   }
00530 
00536   public function get($level = 0)
00537   {
00538     $outputs = array();
00539     $indents = str_repeat("\t", $level);
00540     if(count($this->expressions) == 0) {
00541       return null;
00542     }
00543     foreach($this->expressions as $expression) {
00544       if($expression instanceof Query) {
00545         $outputs[] = $expression->get();
00546       }
00547       if($expression instanceof QueryWhere) {
00548         $outputs[] = $expression->get($level + 1);
00549       }
00550       else {
00551         $outputs[] = $indents . "\t" .  $expression;
00552       }
00553     }
00554     $outputs = array_filter($outputs);
00555     $output = implode("\n" . $indents . $this->operator . "\n", $outputs);
00556     if($level == 0) {
00557       return $output;
00558     }
00559     return $indents . "(\n" . $output . "\n" . $indents . ")";
00560   }
00561 
00566   public function count()
00567   {
00568     return count($this->expressions);
00569   }
00570 
00571 }
00572 
00573 ?>

Generated on Sun Aug 4 2013 12:51:43 for Habari by  doxygen 1.7.1