function test_select_with_subselect_in_from_clause()
{
$db = $this->getConnection();
$from = pdoext_query("content");
$from->addColumn('con_hits');
$from->setConjunctionAnd();
$from->addCriterion('con_type', 1);
$from->addCriterion('con_posttime', pdoext_literal('date_add(current_date(), interval -12 hour)'), '<');
$from->addCriterion('con_refresh', 0);
$from->setOrder('con_posttime', 'DESC');
$from->setLimit(100);
$query = pdoext_query($from, 'x');
$query->addColumn(pdoext_literal('avg(con_hits)'), 'avg_hits');
// NOTE: Can't use assertSqlEqual here, since sqlite doesn't support the syntax
$this->assertEqual($query->toSql($db), 'SELECT avg(con_hits) AS "avg_hits"
FROM (
SELECT "con_hits"
FROM "content"
WHERE
"con_type" = \'1\'
AND "con_posttime" < date_add(current_date(), interval -12 hour)
AND "con_refresh" = \'0\'
ORDER BY "con_posttime" DESC
LIMIT 100) AS "x"');
}
protected function executeQuery()
{
if ($this->result) {
return;
}
if ($this->page_size) {
if ($this->db->supportsSqlCalcFoundRows() && is_null($this->custom_count)) {
$this->setSqlCalcFoundRows();
}
$limit = $this->pageSize();
$offset = max($this->currentPage() - 1, 0) * $this->pageSize();
$this->setLimit($limit);
$this->setOffset($offset);
}
$result = $this->db->query($this);
$result->setFetchMode(PDO::FETCH_BOTH);
if (method_exists($this->gateway, 'load')) {
$this->result = new pdoext_Resultset($result, $this->gateway);
} else {
$this->result = $result;
}
if ($this->page_size && is_null($this->custom_count)) {
if ($this->db->supportsSqlCalcFoundRows()) {
// MySql specific
$result = $this->db->query("SELECT FOUND_ROWS()");
$row = $result->fetch();
$this->total_count = $row[0];
} else {
// fall back on select count(*)
$this->setLimit(null);
$this->setOffset(null);
$q = new pdoext_Query($this);
$q->addColumn(pdoext_literal('count(*)'), 'total_count');
$result = $this->db->query($q);
$row = $result->fetch();
$this->total_count = $row[0];
$this->setLimit($limit);
$this->setOffset($offset);
}
}
}
function scopeWithNameLength($selection)
{
$selection->addColumn('*');
$selection->addColumn(pdoext_literal('length(name)'), 'name_length');
}