subquery ?

1,717 Posts

coroico Reply #1, 15 years, 2 months ago

How could I set up a subquery like SELECT * FROM ( SELECT ... FROM ... WHERE ...) AS tb1 HAVING ...

I have the inner query as a xPDOQuery $c, how could I set up the final query ?

does $xpdo->newQuery method accept a subquery as argument somewhere ?

1,717 Posts

coroico Reply #2, 15 years, 2 months ago

To clarify my question, how could I implement a "from" subquery with XPDO ?

As explained here:

SELECT AVG(sum_column1)
  FROM (SELECT SUM(column1) AS sum_column1
        FROM t1 GROUP BY column1) AS t1;

is a valid query.

For instance, even this doesn’t any sense, try under phpMyAdmin (modify the table prefix):

SELECT AVG(sum_published)
  FROM (SELECT SUM(published) AS sum_published
        FROM revo_site_content GROUP BY published) AS t1;

I set the inner select with:

$c = $this->modx->newQuery(’modResource’);
$c->select(array(’SUM(published) AS sum_published’));
$c->groupby(’published’);

ok, but with this first xPDOQuery how could I set the final query ?

Here this is a very simple (without any interest) query, but if needed I have a realistic example.

MODX Staff
10,725 Posts

opengeek Reply #3, 15 years, 2 months ago

xPDO’s object retrieval methods (getObject, getCollection, getIterator, etc.) are for retrieving objects. You are creating reporting queries on the data and it is much better to work with the result sets, just as you would with SQL normally. You can use xPDO to abstract some of this though still, to take advantage of built-in caching, and other core features. Example —

<?php
$sql = "SELECT AVG(sum_published)
FROM (SELECT SUM(published) AS sum_published
FROM {$this->modx->getTableName('modResource')} GROUP BY published) AS t1";

$query = new xPDOCriteria($this->modx, $sql);

$average = null;
if ($query->stmt && $query->stmt->execute()) {
    $average = $query->stmt->fetchColumn();
    $query->stmt->closeCursor();
}

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek