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