I have a query that I am trying to translate to modx/xpdo. I was able to figure out the basics but am getting stuck when I try to add a subquery. In SQL, it looks like this:
SELECT `title` , `id` , `type`
FROM `mytable`
WHERE `status` = 1
AND `type` NOT
IN (
'foo', 'bar', 'baz', 'bacon', 'ham', 'eggs'
)
AND `id` NOT
IN (
(
SELECT `id`
FROM `mytable`
WHERE `title` LIKE '%Test%'
)
)
in modx/xpdo I have this:
$query = $modx->newQuery('Mytable');
$query->select(array('title', 'id', 'type'));
$query->where(array(
'status:=' => 1,
'type:NOT IN' => array('foo', 'bar', 'baz', 'bacon', 'ham', 'eggs')
));
I tried a variety of things to add the subquery, but they all either failed or returned just the result of the subquery. Would someone mind explaining what I need to do?
MODX Staff