/** * Render sql query * [<sql>, {<params>}] * @return array */ public function toQuery() { $params = array(); $columns = $this->renderColumns($this->fromColumns, $this->fromAlias, $params); $fromParams = array(); if ($this->fromTable instanceof Select) { list($query, $params) = $this->fromTable->toQuery(); $from = 'FROM (' . $query . ') AS ' . $this->fromAlias . PHP_EOL; $fromParams = $params; } else { $from = 'FROM ' . $this->fromTable . ' AS ' . $this->fromAlias . PHP_EOL; } foreach ($this->join as $join) { list($type, $table, $alias, $on, $joinColumns) = $join; /** @var $table Select */ if ($table instanceof self) { list($joinSelect, $joinParams) = $table->toQuery(); $table = '(' . $joinSelect . ')'; $fromParams = array_merge($fromParams, $joinParams); } $from .= $type . ' ' . $table . ' AS ' . $alias; if ($on) { $from .= ' ON ( ' . $on . ' )'; } $from .= PHP_EOL; $columns = array_merge($columns, $this->renderColumns($joinColumns, $alias, $params)); } $where = $this->renderCondition($this->where, $params); $having = $this->renderCondition($this->having, $params); $group = $this->renderOrder($this->group, $params); $order = $this->renderOrder($this->order, $params); $sql = 'SELECT '; if ($this->isDistinct) { $sql .= 'DISTINCT '; } $sql .= join(', ', $columns) . PHP_EOL; $sql .= $from; if ($where) { $sql .= 'WHERE ' . $where . PHP_EOL; } if ($group) { $sql .= 'GROUP BY ' . $group . PHP_EOL; } if ($having) { $sql .= 'HAVING ' . $having; } if ($order) { $sql .= 'ORDER BY ' . $order . PHP_EOL; } $sql .= $this->renderLimit($this->limit, $this->offset); $params = array_merge($params, $fromParams); return array($sql, $params); }
/** * @param Select $select * @return static */ public function whereNotExists(Select $select) { list($query, $params) = $select->toQuery(); return $this->where('NOT EXISTS (' . $query . ')', $params); }