Unfortunately xPDO does not support join like that right now, the join methods require class to be set so had to do another way around the issue. Lucky that I got one site with quip and could duplicate it in to your form to support the CONCAT();
Instead of doing a left join, you can use subquery at ORDER BY 'sortby()' as shown below:
$quip = $modx->getService('quip','Quip',$modx->getOption('quip.core_path',null,$modx->getOption('core_path').'components/quip/').'model/quip/',$scriptProperties);
$cTable= $modx->getTableName("quipComment");
$c = $modx->newQuery('modResource');
$c->where(array("parent:IN" => array(13, 17, 23, 45)));
$c->sortby("(SELECT max(createdon) FROM {$cTable} WHERE thread = CONCAT('topic', modResource.id))", 'DESC');
$c->limit(10, 0);
This is tested to work, had different parents but the method is solid.
The SQL query for this is:
SELECT `modResource`.`id` AS `modResource_id`, `modResource`.`type`... FROM `modx_site_content` AS `modResource` WHERE `modResource`.`parent` IN (2,33) ORDER BY (SELECT max(createdon) FROM `modx_quip_comments` WHERE thread = CONCAT('topic', modResource.id)) DESC LIMIT 10
Truncated the select part a bit as there are few columns selected. The order of methods does not matter like Bob said, but personally want to keep them in SQL order so it makes it easier to read ad modify later on.
Note, you need to call the Quip service or add it as a package to snippet to be able to use the schema.
[ed. note: lazylegs last edited this post 14 years, 7 months ago.]
