Another SQL to xPDO translation (left join + FROM subquery)

- 534 Posts
exside Reply #1, 9 years, 2 months ago
This question is more about understanding xPDO than asking how it could work, because it does already...

The scenario is the following: I have a table with records of which each has a timestamp and a type field, the type can be assigned many times to different records. What I want is to fetch the newest/most recent record for each type (with a few types excluded, e.g. NOT IN). Additionally I need to fetch some information about the type from a separate types table (LEFT JOIN)... so like this I get what I want:
```
$query = new xPDOCriteria($modx, "SELECT * FROM (SELECT * FROM `table_with_records` WHERE type NOT IN('X','Y','Z') ORDER BY timestamp DESC) AS t LEFT JOIN `table_with_types` ON (t.typeid = table_with_types.id) GROUP BY t.type");

$objs = $modx->getCollection('ClassName', $query);

foreach ($objs as $obj) {
    echo print_r($obj->toArray(),1);
}
```
but how would I write something like that as a "normal" xPDO query, especially the FROM (SELECT...) AS t part...?

Something like:
```
$subquery = $modx->newQuery('ClassName');
$subquery->where('type:NOT IN' => array('X', 'Y', 'Z'));
$subquery->sortby('timestamp', 'DESC');

$query = $modx->newQuery('ClassName');
$query->leftJoin('JoinedClassName', 'Alias');
$query->groupby('type');
// how do I add $subquery to $query...??
```
[ed. note: exside last edited this post 9 years, 2 months ago.]

128 Posts

Michael Engel Reply #2, 9 years, 2 months ago

When i use subquerys i usually just use ->toSQL() to parse it into another Query. Using an xPDOCriteria when using as FROM and other times just passing into where or join.

$c = $modx->newQuery("Article", array("active" => 1));
$c->sortby('RAND()')->prepare();

$c1 = new xPDOCriteria($modx,'SELECT * FROM ('.$c->toSQL().') as collection GROUP BY color');
while ( $articleTmp = $modx->getCollection('Article', $c1) ) {

}

Edit: just tested out.. this should also work and is even more xPDO ,) pressing the query just into the from

$subquery = $modx->newQuery('ClassName');
$subquery->where('type:NOT IN' => array('X', 'Y', 'Z'));
$subquery->sortby('timestamp', 'DESC');
 
$query = $modx->newQuery('ClassName');
$query>query['from']['tables'] = array(array(
		'table' => "({$subquery->toSQL()})",
		'alias' => 'ClassName'
	));
$query->leftJoin('JoinedClassName', 'Alias');
$query->groupby('type');

Or another example with subquery Join, which comes in easier

	$subquery = $modx->newQuery('Ak');
	$subquery->select('Ak.id id');
	//...
	$subquery->limit(1);
	$subquery->prepare();

	$c->leftJoin('AkLage'
		, 'AkLageLinks'
		, 'AkLageLinks.id = (' . $subquery->toSQL() . ') '
	);

[ed. note: m.engel last edited this post 9 years, 2 months ago.]