/** * 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; }