[Summary view] [Print] [Text view]

   1  <?php
   2  /*
   3   * Limb PHP Framework
   4   *
   5   * @link http://limb-project.com 
   6   * @copyright  Copyright &copy; 2004-2007 BIT(http://bit-creative.com)
   7   * @license    LGPL http://www.gnu.org/copyleft/lesser.html 
   8   */
   9  lmb_require('limb/dbal/src/query/lmbCriteriaQuery.class.php');
  10  
  11  //TODO: use primitive lexer for parsing sql templates someday...
  12  
  13  /**
  14   * class lmbSelectQuery.
  15   *
  16   * @package dbal
  17   * @version $Id: lmbSelectQuery.class.php 6005 2007-06-19 21:14:49Z pachanga $
  18   */
  19  class lmbSelectQuery extends lmbCriteriaQuery
  20  {
  21    protected $_fields = array();
  22    protected $_tables = array();
  23    protected $_left_join_constraints  = array();
  24    protected $_order = array();
  25    protected $_having = array();
  26    protected $_group_by = array();
  27  
  28    function __construct($sql = null, $conn)
  29    {
  30      if(is_null($sql))
  31        $sql = "SELECT %fields% FROM %tables% %left_join% %where% %group% %having% %order%";
  32  
  33      parent :: __construct($sql, $conn);
  34    }
  35  
  36    function addField($field, $alias = null)
  37    {
  38      $this->_fields[$field] = $alias;
  39    }
  40  
  41    function addTable($table, $alias = null)
  42    {
  43      $this->_tables[$table] = $alias;
  44    }
  45  
  46    function addOrder($field, $type='ASC')
  47    {
  48      $this->_order[] = "$field $type";
  49    }
  50  
  51    function addGroupBy($group)
  52    {
  53      $this->_group_by[] = $group;
  54    }
  55  
  56    function addHaving($criteria)
  57    {
  58      $this->_having[] = $criteria;
  59    }
  60  
  61    function addLeftJoin($table, $connect_by)
  62    {
  63      $this->_left_join_constraints[$table] = $connect_by;
  64    }
  65  
  66    function getRecordSet()
  67    {
  68      $stmt = $this->getStatement();
  69      return $stmt->getRecordSet();
  70    }
  71  
  72    protected function _getFieldsHint()
  73    {
  74      $fields = '';
  75      foreach($this->_fields as $field => $alias)
  76      {
  77        $fields .= $this->_conn->quoteIdentifier($field) .
  78                   ($alias ? ' as ' . $this->_conn->quoteIdentifier($alias) : '') . ',';
  79      }
  80      $fields = rtrim($fields, ',');
  81  
  82      if($this->_selectFieldsExists())
  83      {
  84        if(count($this->_fields))
  85          return ',' . $fields;
  86        else
  87          return '';
  88      }
  89      elseif(count($this->_fields) == 0)
  90        return '*';
  91      else
  92        return $fields;
  93    }
  94  
  95    protected function _getTablesHint()
  96    {
  97      if(count($this->_tables) == 0)
  98        return '';
  99  
 100      $tables = '';
 101      foreach($this->_tables as $table => $alias)
 102      {
 103        $tables .= $this->_conn->quoteIdentifier($table) .
 104                   ($alias ? ' ' . $this->_conn->quoteIdentifier($alias) : '') . ',';
 105      }
 106      $tables = rtrim($tables, ',');
 107  
 108      if($this->_selectTablesExists())
 109        $tables = ',' . $tables;
 110  
 111      return $tables;
 112    }
 113  
 114    protected function _getLeftJoinHint()
 115    {
 116      $join = array();
 117      foreach($this->_left_join_constraints as $table => $connect_by)
 118      {
 119        $foreign_key = $this->_conn->quoteIdentifier(key($connect_by));
 120        $alias_key = $this->_conn->quoteIdentifier(reset($connect_by));
 121        $join[] = "LEFT JOIN " . $this->_conn->quoteIdentifier($table) . " ON $foreign_key=$alias_key";
 122      }
 123  
 124      return implode(' ', $join);
 125    }
 126  
 127    protected function _getGroupHint()
 128    {
 129      if (count($this->_group_by) == 0)
 130        return '';
 131  
 132      $group = implode(',', array_map(array($this->_conn, 'quoteIdentifier'), $this->_group_by));
 133  
 134      if($this->_groupByClauseExists($group_by_args))
 135      {
 136        //primitive check if comma is required
 137        if($group_by_args)
 138          return ',' . $group;
 139        else
 140          return $group;
 141      }
 142      else
 143        return 'GROUP BY ' . $group;
 144    }
 145  
 146    protected function _getHavingHint()
 147    {
 148      if (count($this->_having) == 0)
 149        return '';
 150  
 151      if(!count($this->_group_by) && !$this->_groupByClauseExists())
 152        throw new lmbException('can not generate HAVING condition GROUP BY missing');
 153  
 154      $implode = array();
 155      foreach($this->_having as $criteria)
 156      {
 157        $implode[] = $criteria->toStatementString($this->_stmt_values);
 158      }
 159  
 160      $having = implode(' AND ', $implode);
 161  
 162      if($this->_havingClauseExists($having_args))
 163      {
 164        if($having_args)
 165          return 'AND ' . $having;
 166        else
 167          return $having;
 168      }
 169      else
 170        return 'HAVING ' . $having;
 171    }
 172  
 173    protected function _getOrderHint()
 174    {
 175      if (count($this->_order) == 0)
 176        return '';
 177  
 178      $order = implode(',', $this->_order);
 179  
 180      if($this->_orderByClauseExists($order_by_args))
 181      {
 182        //primitive check if comma is required
 183        if($order_by_args)
 184          return ',' . $order;
 185        else
 186          return $order;
 187      }
 188      else
 189        return 'ORDER BY ' . $order;
 190    }
 191  
 192    protected function _orderByClauseExists(&$args = '')
 193    {
 194      //!!!make it better later
 195      if(preg_match('~(?<=from).+order\s+by\s(.*)$~si', $this->_getNoHintsSQL(), $matches))
 196      {
 197        $args = trim($matches[1]);
 198        return true;
 199      }
 200  
 201      return false;
 202    }
 203  
 204    protected function _groupByClauseExists(&$args = '')
 205    {
 206      //!!!make it better later
 207      if(preg_match('~(?<=\Wfrom).+group\s+by\s(.*)$~si', $this->_getNoHintsSQL(), $matches))
 208      {
 209        $args = trim($matches[1]);
 210        return true;
 211      }
 212  
 213      return false;
 214    }
 215  
 216    protected function _havingClauseExists(&$args = '')
 217    {
 218      //!!!make it better later
 219      if(preg_match('~(?<=\Wgroup)\s+by\s+.+?having\s(.*)(order)?$~si', $this->_getNoHintsSQL(), $matches))
 220      {
 221        $args = trim($matches[1]);
 222        return true;
 223      }
 224  
 225      return false;
 226    }
 227  
 228    protected function _selectFieldsExists()
 229    {
 230      //!!!make it better later
 231      return preg_match('~^select\s+\S+.+?from~si', $this->_getNoHintsSQL());
 232    }
 233  
 234    protected function _selectTablesExists()
 235    {
 236      return preg_match('~(?<=\Wfrom)\s+\S+(where|order|group)?~si', $this->_getNoHintsSQL());
 237    }
 238  
 239    protected function _whereClauseExists(&$args = '')
 240    {
 241      //primitive check if WHERE was already in sql
 242      //!!!make it better later
 243      if(preg_match('~(?<=\Wfrom).+where\s+(.*)~si', $this->_getNoHintsSQL(), $matches))
 244      {
 245        if(preg_match('~([a-zA-Z].*)(group|order)?$~si', $matches[1], $args_matches))
 246          $args = $args_matches[1];
 247  
 248        return true;
 249      }
 250      return false;
 251    }
 252  }
 253  ?>