/**
* Builds SQL statement from 'select' node
* @return boolean
* @throws epExceptionQueryBuilder
*/
protected function buildSqlSelect(epQueryNode &$node)
{
// order matters!!
// build aggregate
$aggregate = $this->pm->quoteId($this->primary_alias . '.*');
if ($n = $node->getChild('aggregate')) {
$aggregate = $this->aggr_func = $this->buildSqlAggregate($n);
}
// build where
$where = 'WHERE 1=1';
if ($n = $node->getChild('where')) {
$where = $this->buildSqlWhere($n);
}
// build limit
$limit = '';
if ($n = $node->getChild('limit')) {
$limit = $this->limit = $this->buildSqlLimit($n);
}
// build orderby
$orderby = '';
if ($n = $node->getChild('orderby')) {
$orderby = $this->buildSqlOrderby($n);
}
// get the first part select+distinct+aggregate
$select = "SELECT DISTINCT {$aggregate} ";
// get the second part order by + limit
$orderby_limit = '';
if ($orderby) {
$orderby_limit .= ' ' . $orderby;
}
if ($limit) {
$orderby_limit .= ' ' . $limit;
}
// get sql left-joins for primary/secondary roots
$sql_parts = $this->pm->getRootSql();
// pick out the primary root
$p_sql_parts = $sql_parts[$this->primary_alias];
// unset primary and get all secondary parts
unset($sql_parts[$this->primary_alias]);
$s_sql_parts = $sql_parts;
// arrays to hold froms and joins
$froms = array();
$joins = array();
// quote id (primary alias)
$p_alias = $this->pm->quoteId($this->primary_alias);
// loop through tables for primary root
foreach ($p_sql_parts as $p_table => $p_joins) {
// quote id
$p_table = $this->pm->quoteId($p_table);
// collect table-as-alias for primary root
$froms[] = array($p_table . ' AS ' . $p_alias);
$joins[] = array($p_joins);
}
// loop through tables for secondary roots
foreach ($s_sql_parts as $s_alias => $s_sql_part) {
// quote id
$s_alias = $this->pm->quoteId($s_alias);
// backup froms and joins
$froms0 = $froms;
$joins0 = $joins;
// reset froms and joins
$froms = array();
$joins = array();
// loop through joins for each secondary table
foreach ($s_sql_part as $s_table => $s_joins) {
// quote id
$s_table = $this->pm->quoteId($s_table);
// start with froms/joins backup
$froms_ = $froms0;
$joins_ = $joins0;
// collect table-as-alias for secondary root
foreach ($froms_ as $k => $from) {
$froms_[$k][] = $s_table . ' AS ' . $s_alias;
}
// collect joins
foreach ($joins_ as $k => $join) {
$joins_[$k][] = $s_joins;
}
$froms = array_merge($froms, $froms_);
$joins = array_merge($joins, $joins_);
}
}
// array to hold all sql statements
$stmts = array();
// assemble from 'froms' and 'joins'
for ($i = 0; $i < count($froms); $i++) {
// make from clause
$from = implode(', ', $froms[$i]);
// make left join clauses
$join = '';
if ($joins[$i]) {
$join = implode('', $joins[$i]);
}
// assemble a sql statement
$stmts[] = $select . 'FROM ' . $from . ' ' . $join . $where;
}
// if we have only one statement
if (1 == count($stmts)) {
// append orderby and limit clauses
$stmts[0] .= $orderby_limit;
// empty limit so no post-query limit operation
$this->limit = false;
}
return $stmts;
}
/**
* Builds SQL statement from 'select' node
* @return boolean
* @throws epExceptionQueryBuilder
*/
protected function buildSqlSelect(epQueryNode &$node)
{
// order matters!!
// build aggregate
$aggregate = $this->qid($this->root_alias . '.*');
if ($n = $node->getChild('aggregate')) {
$aggregate = $this->aggr_func = $this->buildSqlAggregate($n);
}
// build where
$where = array();
$n = $node->getChild('where');
foreach ($this->root_classes as $root_class) {
if ($n) {
$this->aliases[$this->root_alias] = $root_class;
$where[] = $w = $this->buildSqlWhere($n);
} else {
$where[] = 'WHERE 1=1';
}
}
// build limit
$limit = '';
if ($n = $node->getChild('limit')) {
$limit = $this->limit = $this->buildSqlLimit($n);
}
// build orderby
$orderby = '';
if ($n = $node->getChild('orderby')) {
$orderby = $this->orderby = $this->buildSqlOrderby($n);
}
// build from (must be last)
$from = array();
if ($n = $node->getChild('from')) {
foreach ($this->root_classes as $root_class) {
$this->aliases[$this->root_alias] = $root_class;
$from[] = $this->buildSqlFrom($n);
}
}
// get the first part select+distinct+aggregate
$sql1 = "SELECT DISTINCT {$aggregate} ";
// get the second part order by + limit
$sql2 = '';
if ($orderby) {
$sql2 .= ' ' . $orderby;
}
if ($limit) {
$sql2 .= ' ' . $limit;
}
// add the froms and wheres
for ($i = 0; $i < count($this->root_classes); $i++) {
$sql[] = $sql1 . $from[$i] . ' ' . $where[$i];
}
// only one sql stmt, so orderby and limit won't mess up
if (count($this->root_classes) == 1) {
$this->limit = $this->orderby = false;
$sql[0] .= $sql2;
}
return $sql;
}