system/classes/query.php

<?php

namespace Habari;

class Query {

  private $where = null;
  public $primary_table = null;
  protected $fields = array();
  protected $joins = array();
  protected $join_params = array();
  protected $limit = null;
  protected $offset = null;
  protected $orderby = null;
  protected $groupby = null;
  protected $having = null;

  public function __construct($primary_table = null)
  {
    $this->primary_table = $primary_table;
  }

  public static function create($primary_table = null)
  {
    return new Query($primary_table);
  }

  public function select($fields)
  {
    $args = func_get_args();
    foreach($args as $fields) {
      $this->fields = array_merge($this->fields, Utils::single_array($fields));
    }
    return $this;
  }

  public function set_select($fields)
  {
    $this->fields = Utils::single_array($fields);
    return $this;
  }

  public function distinct($set = true)
  {
    $this->distinct = $set;
    return $this;
  }

  public function from($primary_table)
  {
    $this->primary_table = $primary_table;
    return $this;
  }

  public function join($join, $parameters = array(), $alias = null)
  {
    if(empty($alias)) {
      $alias = md5($join);
    }
    $this->joins[$alias] = $join;
    $this->join_params = array_merge($this->join_params, $parameters);
    return $this;
  }

  public function joined($alias)
  {
    return array_key_exists($alias, $this->joins);
  }

  public function where($operator = 'AND')
  {
    if(!isset($this->where)) {
      $this->where = new QueryWhere($operator);
    }
    return $this->where;
  }

  public function groupby($value)
  {
    $this->groupby = empty($value) ? null : $value;
    return $this;
  }

  public function orderby($value)
  {
    $this->orderby = empty($value) ? null : $value;
    return $this;
  }

  public function having($value)
  {
    $this->having = empty($value) ? null : $value;
    return $this;
  }

  public function limit($value)
  {
    $this->limit = is_numeric($value) ? intval($value) : null;
    return $this;
  }

  public function offset($value)
  {
    $this->offset = is_numeric($value) ? intval($value) : null;
    return $this;
  }

  public function get()
  {
    $fields = $this->fields;

    // If the orderby has a function in it, try to create a select field for it with an alias
    $orderby = null;
    if(isset($this->orderby)) {
      $orderby = $this->orderby;
      if ( strpos( $orderby, '(' ) !== false ) {
        $orders = explode( ',', $orderby );
        $ob_index = 0;
        foreach ( $orders as $key => $order ) {
          if ( !preg_match( '%(?P<field>.+)\s+(?P<direction>DESC|ASC)%i', $order, $order_matches ) ) {
            $order_matches = array(
              'field' => $order,
              'direction' => '',
            );
          }

          if ( strpos( $order_matches['field'], '(' ) !== false ) {
            $ob_index++;
            $field = 'orderby' . $ob_index;
            $fields[$field] = "{$order_matches['field']} AS $field";
            $orders[$key] = $field . ' ' . $order_matches['direction'];
          }
        }
        $orderby = implode( ', ', $orders );
      }
    }

    $sql = "SELECT \n\t";
    if($this->distinct()) {
      $sql .= "DISTINCT \n\t";
    }
    if(count($fields) > 0) {
      $sql .= implode(",\n\t", $fields);
    }
    else {
      $sql .= "*";
    }
    $sql .= "\nFROM\n\t" . $this->primary_table;
    foreach($this->joins as $join) {
      $sql .= "\n" . $join;
    }
    $where = $this->where()->get();
    if(!empty($where)) {
      $sql .= "\nWHERE\n" . $this->where()->get();
    }

    if(isset($this->groupby)) {
      $sql .= "\nGROUP BY " . $this->groupby;
    }

    if(isset($this->having)) {
      $sql .= "\nHAVING " . $this->having;
    }
    if(isset($orderby)) {
      $sql .= "\nORDER BY " . $orderby;
    }

    if(isset($this->limit)) {
      $sql .= "\nLIMIT " . $this->limit;
      if(isset($this->offset)) {
        $sql .= "\nOFFSET " . $this->offset;
      }
    }

    return $sql;
  }

  public function params()
  {
    return array_merge($this->where()->params(), $this->join_params);
  }

  public static function new_param_name($prefix = 'param')
  {
    static $param_names = array();

    if(!isset($param_names[$prefix])) {
      $param_names[$prefix] = 0;
    }
    $param_names[$prefix]++;
    return $prefix . '_' . $param_names[$prefix];
  }

  public function row($class = null)
  {
    return DB::get_row($this->get(), $this->params(), $class);
  }

  public function results($class = null)
  {
    return DB::get_results($this->get(), $this->params(), $class);
  }

  public function keyvalue()
  {
    return DB::get_keyvalue($this->get(), $this->params());
  }

  public function column()
  {
    return DB::get_column($this->get(), $this->params());
  }

  public function value()
  {
    return DB::get_value($this->get(), $this->params());
  }
}

class QueryWhere {
  protected $operator = 'AND';
  protected $expressions = array();
  protected $parameters = array();

  public function __construct($operator = 'AND')
  {
    $this->operator = $operator;
  }

  public function create($operator = 'AND')
  {
    return new QueryWhere($operator);
  }

  public function add($expression, $parameters = array(), $name = null)
  {
    if(empty($name)) {
      $name = count($this->expressions) + 1;
    }
    $this->expressions[$name] = $expression;
    $this->parameters = array_merge($this->parameters, $parameters);
    return $this;
  }

  public function get_named($name)
  {
    if(isset($this->expressions[$name])) {
      return $this->expressions[$name];
    }
    return null;
  }

  public function in($field, $values, $paramname = null, $validator = null, $positive = true)
  {
    $expression = $field . ' ';
    if($values instanceof Query) {
      if( !$positive ) {
        $expression .= 'NOT ';
      }
      $expression .= 'IN (' . $values->get() . ')';
      $this->parameters = array_merge( $this->parameters, $values->params() );
    }
    elseif(is_array($values) && count($values) > 1) {
      $in_elements = array();
      if(is_callable($validator)) {
        foreach($values as $value) {
          $newvalue = $validator($value);
          if(!empty($newvalue)) {
            $in_elements[] = $newvalue;
          }
        }
      }
      else {
        foreach($values as $value) {
          $value_name = Query::new_param_name($paramname);
          $in_elements[] = ':' . $value_name;
          $this->parameters[$value_name] = $value;
        }
      }
      if(!$positive) {
        $expression .= 'NOT ';
      }
      $expression .= 'IN (' . implode(',', $in_elements) . ')';
    }
    else {
      if(is_array($values)) {
        $values = reset($values);
      }
      if(!$positive) {
        $expression .= ' <> ';
      }
      else {
        $expression .= ' = ';
      }

      if(empty($paramname)) {
        $paramname = Query::new_param_name();
      }

      if(is_callable($validator)) {
        $expression .= $validator($values);
      }
      else {
        $expression .= ':' . $paramname;
        $this->parameters[$paramname] = $values;
      }
    }

    if(empty($paramname)) {
      $paramname = count($this->expressions) + 1;
    }

    $this->expressions[$paramname] = $expression;
    return $this;
  }

  public function exists( Query $values, $paramname = null, $positive = true )
  {
    $expression = '';

    if( !$positive ) {
      $expression .= 'NOT ';
    }

    $expression .= 'EXISTS (' . $values->get() . ')';

    $this->parameters = array_merge( $this->parameters, $values->params() );

    if( empty( $paramname ) ) {
      $paramname = count( $this->expressions ) + 1;
    }

    $this->expressions[$paramname] = $expression;
    return $this;
  }

  public function params()
  {
    $parameters = $this->parameters;
    foreach($this->expressions as $expression) {
      if($expression instanceof Query) {
        $parameters = array_merge($parameters, $expression->params());
      }
      if($expression instanceof QueryWhere) {
        $parameters = array_merge($parameters, $expression->params());
      }
    }
    return $parameters;
  }

  public function __set($name, $value)
  {
    $this->parameters[$name] = $value;
    return $this->parameters[$name];
  }

  public function __get($name)
  {
    return $this->parameters[$name];
  }

  public function get($level = 0)
  {
    $outputs = array();
    $indents = str_repeat("\t", $level);
    if(count($this->expressions) == 0) {
      return null;
    }
    foreach($this->expressions as $expression) {
      if($expression instanceof Query) {
        $outputs[] = $expression->get();
      }
      if($expression instanceof QueryWhere) {
        $outputs[] = $expression->get($level + 1);
      }
      else {
        $outputs[] = $indents . "\t" .  $expression;
      }
    }
    $outputs = array_filter($outputs);
    $output = implode("\n" . $indents . $this->operator . "\n", $outputs);
    if($level == 0) {
      return $output;
    }
    return $indents . "(\n" . $output . "\n" . $indents . ")";
  }

  public function count()
  {
    return count($this->expressions);
  }

}

?>