DataAccess/Queries/QueryBuilder.php

   1 <?php
   2
   3 namespace DataAccess\Queries;
   4
   5 use DataAccess\Queries\IQueryBuilder;
   6
   7 class QueryBuilder implements IQueryBuilder
   8 {
   9     private $_queryString = 'SELECT * FROM %s';
  10
  11     protected $_whereClauses = array();
  12     protected $_limitClause;
  13     protected $_joinClauses = array();
  14     protected $_orderClause;
  15     protected $_countClause;
  16     protected $_groupClause;
  17
  18     public function buildQuery()
  19     {
  20         $this->applyJoinClauses()
  21              ->applyWhereClauses()
  22              ->applyGroupClause()
  23              ->applyOrderClause()
  24              ->applyLimitClause()
  25              ->applyCountClause();
  26
  27         return $this->_queryString;
  28     }
  29
  30     public function setBaseQuery($baseQuery)
  31     {
  32         $this->_queryString = $baseQuery;
  33     }
  34
  35     public function where($columnName, $operator, $value)
  36     {
  37         $this->_whereClauses[] = array('columnName' => $columnName, 'operator' => $operator, 'value' => $value);
  38         return $this;
  39     }
  40
  41     public function limit($start, $end = null)
  42     {
  43         if($end)
  44         {
  45             $this->_limitClause = sprintf(' LIMIT %u,%u', $start, $end);
  46             return $this;
  47         }
  48
  49         $this->_limitClause = sprintf(' LIMIT %u', $start);
  50
  51         return $this;
  52     }
  53
  54     public function join($type, $tableA, $columnA, $tableB, $columnB)
  55     {
  56         $this->_joinClauses[] = sprintf(' %s JOIN %s ON %s.%s = %s.%s', $type, $tableB, $tableA, $columnA, $tableB, $columnB);
  57         return $this;
  58     }
  59
  60     public function null($columnName)
  61     {
  62         return $this->where($columnName, 'is', null);
  63     }
  64
  65     public function count($column, $as = null)
  66     {
  67         if($as)
  68         {
  69             $this->_countClause = sprintf(', count(%s) as %s', $column, $as);
  70         } else {
  71             $this->_countClause = sprintf(', count(%s)', $column);
  72         }
  73
  74         return $this;
  75     }
  76
  77     public function group($column)
  78     {
  79         $this->_groupClause = sprintf(' group by %s', $column);
  80
  81         return $this;
  82     }
  83
  84     private function applyCountClause()
  85     {
  86         $pos = strpos($this->_queryString, '*')+1;
  87         $this->_queryString = substr_replace($this->_queryString, $this->_countClause, $pos, 0);
  88
  89         return $this;
  90     }
  91
  92     private function applyGroupClause()
  93     {
  94         $this->_queryString .= $this->_groupClause;
  95
  96         return $this;
  97     }
  98
  99     private function applyJoinClauses()
 100     {
 101         foreach($this->_joinClauses as $joinClause)
 102         {
 103             $this->_queryString .= $joinClause;
 104         }
 105
 106         return $this;
 107     }
 108
 109     public function orderBy($column, $direction)
 110     {
 111         $this->_orderClause = sprintf(' ORDER BY %s %s', $column, $direction);
 112
 113         return $this;
 114     }
 115
 116     private function applyWhereClauses()
 117     {
 118         if(!$this->_whereClauses) return $this;
 119
 120         $this->_queryString .= ' WHERE ';
 121
 122         foreach($this->_whereClauses as $whereClause)
 123         {
 124             switch(gettype($whereClause['value']))
 125             {
 126                 case 'integer':
 127                     $this->_queryString .= sprintf("%s%s%u", $whereClause['columnName'], $whereClause['operator'], $whereClause['value']) . ' AND ';
 128                     break;
 129                 case 'string':
 130                     $this->_queryString .= sprintf("%s %s '%s'", $whereClause['columnName'], $whereClause['operator'], $whereClause['value']) . ' AND ';
 131                     break;
 132                 case 'NULL':
 133                     $this->_queryString .= sprintf("%s is null", $whereClause['columnName']) . ' AND ';
 134                     break;
 135             }
 136
 137         }
 138
 139         $this->_queryString = rtrim($this->_queryString, ' AND ');
 140         return $this;
 141     }
 142
 143     private function applyLimitClause()
 144     {
 145         $this->_queryString .= $this->_limitClause;
 146         return $this;
 147     }
 148
 149     private function applyOrderClause()
 150     {
 151         $this->_queryString .= $this->_orderClause;
 152         return $this;
 153     }
 154
 155 }