[WIP] Query Builder (Core)

Query.php

<?php

namespace SQL;

class Query {
  public static function select(){
    return new QuerySelect;
  }  
}

class QuerySelect extends Query {
  public $fields = [],
         $tables = [],
         $wheres = [],
         $groups = [],
         $orders = [],
         $limit,
         $offset;
            
  private  $_query,
           $_context;
  
  public function __construct(){
    $this->_context = (object)['type'=>'select','element'=>null];
  }
            
  public function sql(){
    if (!$this->_query) {
      $q      = ["SELECT"];

      // Fields
      if ($this->fields){
        $fields = $this->fields;
        $block  = implode(",\n  ",array_reduce(array_keys($fields),
         function($r,$key) use ($fields) {
          $alias = $fields[$key];
          $r[]   = "`$key`" . ($alias ? " as `$alias`" : '');
          return $r;
         },[])
        );
        $q[]    = $block ? "  $block" : '';
      } else $q[] = '  *';
      

      // Tables and Joins
      if ($this->tables){
        $q[]    = "\nFROM";
        $fields = $this->tables;
        $block  = implode(",\n  ",array_reduce(array_keys($fields),
         function($r,$key) use ($fields) {
          $alias = $fields[$key];
          $r[]   = $key . ($alias ? " as `$alias`" : '');
          return $r;
         },[])
        );
        $q[]    = $block ? "  $block" : '';
      }

      // Conditions : Where
      if ($this->wheres){
        $q[]    = "\nWHERE";
        $fields = $this->wheres;
        $operation = 'AND';
        $block  = implode(" ",array_reduce(array_keys($fields),
         function($r,$key) use ($fields, &$operation) {
          $operation = $fields[$key];
          if (is_array($operation) && count($operation)){
            $x = ["($key)"];
            foreach ($operation as $sub => $sub_op) {
              $x[] = "$sub_op ($sub)";
            }
            $key = "(" . implode(' ', $x) . ")";
          }
          $r[]   = "$key\n  AND\n   ";
          return $r;
         },[])
        );
        $block = rtrim($block, "\n AND\n   ");
        $q[]    = $block ? "    $block" : '';
      }


      // Orders
      if ($this->orders){
        $q[]    = "\nORDER BY";
        $fields = $this->orders;
        $block  = implode(",\n  ",array_reduce(array_keys($fields),
         function($r,$key) use ($fields) {
          $r[]   = "$key {$fields[$key]}";
          return $r;
         },[])
        );
        $q[]    = $block ? "  $block" : '';
      }


      // LIMIT and OFFSET
      $x = [];
      if (is_numeric($this->limit))
        $x[]  = "LIMIT {$this->limit}";
      if (is_numeric($this->offset))
        $x[]  = "OFFSET {$this->offset}";
      if ($x) $q[] = "\n" . implode("\n", $x);
      
      $this->_query = implode("\n", $q);
    }
    return $this->_query;
  }

  public function __toString(){
    return $this->sql();
  }

  protected function _setContext($type, $element=null){
    $this->_context->type    = $type;
    $this->_context->element = $element;
    if ($this->_query) $this->_query = '';
    return $this;
  }

  protected function _readAliases($elements, $callback=null){
    $results = [];
    foreach (array_filter((array)$elements) as $field => $alias) {
      if (is_numeric($field)) list($field, $alias) = [$alias, null];
      if ($callback) $callback($field, $alias);
      $results[$field] = $alias;
    }
    return $results;
  }

  public function __call($n,$p) {
     switch (true) {
       
       case strpos('|or|and|',$n) !== false :
         switch ($this->_context->type){
           case "wheres":
             return $this->whereOperator($p[0],$n);
           break;
         }
       break;
       
       case strpos('|left|inner|full|right|',$n) !== false :
         switch ($this->_context->type){
           case "tables":
             return $this->join($p[0], $n);
           break;
         }
       break;
      
    }
    return $this;
  }

  public function fields($fields){
    return $this->_setContext('fields',$this->_readAliases($fields,(function($field, $alias){
      $this->fields[$field] = $alias;
    })->bindTo($this)));
  }

  public function from($tables){
    return $this->_setContext('tables', $this->_readAliases($tables,(function($table, $alias){
      $this->tables[$table] = $alias;
    })->bindTo($this)));
  }

  public function join($joins, $type=null){
    $JOIN_ = " \n    " . ($type ? strtoupper($type) . ' ' : '' ) . "JOIN";
    $context = $this->_context;
    if ($context->type == 'tables') {
      $tables = $context->element;
      $joins  = $this->_readAliases($joins);
      foreach ($tables as $table => $table_alias) {
        $j = [];
        foreach ($joins as $join_table => $on) {
          $j[] = $join_table . ($on ? " ON $on" : '');
        }
        if ($j){
          unset($this->tables[$table]);
          unset($tables[$table]);
          $def = $table 
                  . ($table_alias?" AS `$table_alias`":'') 
                  . (count($j)>1 ? "\n     " : '') 
                  . " $JOIN_ " 
                  . implode("\n      $JOIN_ ",$j);
          $this->tables[$def] = $tables[$def] = null;
        }
      }
      return $this->_setContext('tables', $tables);
    } else return $this;
  }

  public function offset($offset){
    return $this->_setContext('offset', $this->offset = $offset);
  }  

  public function limit($limit){
    return $this->_setContext('limit', $this->limit = $limit);
  }  

  public function page($page, $count=25){
    $page = max($page,1);
    $this->offset = (int)(($page - 1) * $count);
    $this->limit  = (int)$count;
    return $this->_setContext('page', [$page, $count]);
  }  

  public function order($fields){
    return $this->_setContext('orders',$this->_readAliases($fields,(function($field, $alias){
      switch ($alias=strtoupper($alias)) {
        case "DESC": case "ASC": break;
        default: $alias = 'ASC'; break;
      }
      $this->orders[$field] = $alias?:'ASC';
    })->bindTo($this)));
  }

  public function where($fields){
    return $this->_setContext('wheres',$this->_readAliases($fields,(function($condition, $extra){
      $this->wheres[$extra ? "$condition = $extra" : $condition] = [];
    })->bindTo($this)));
  }

  public function whereOperator($conditions, $operation='AND'){
    $operation = strtoupper($operation);
    $context = $this->_context;
    if ($context->type == 'wheres') {
      $wheres = $context->element;
      foreach ($wheres as $condition => $extra) {
        $where = $extra ? "$condition = $extra" : $condition;
        $conditions = $this->_readAliases($conditions,(function($condition, $extra) use ($where, $operation){
          $this->wheres[$where][$extra ? "$condition = $extra" : $condition] = $operation;
         })->bindTo($this));
      }
      return $this->_setContext('wheres', $wheres);
    } else return $this;
  }


}


echo Query::select()

              ->fields([
                'name',
                'surname',
                'age' => 'età'
              ])

              ->order(["name"=>'desc'])
              
              ->from(["users" => "u"])
                ->join(["user_info" => 'user_id = u.id'])
              
              ->from("(select * from logs) l")
                ->inner(['inner_table' => 'id'])
                ->full(['full_table' => 'full_table.ext_id = u.id'])

              ->offset(12)
              ->limit(2)
              
              ->page(5)
              
              ->order("age")
              
              ->where(["age" => "?", "name" => "name is not null"])
                ->or("age < 15")
                ->and("age <> 3")

             // ->or()
                            
              ->where("name is not null")
                ->and(["name" => "Gianni"])
;