Джоины подзапросов средствами xPDO

Материал для экспертов.

Вообще с этой задачей бился не один год, и вот только сегодня победил… Задача: средствами xPDO в запрос добавить подзапрос, то есть еще один select. Пример такого запроса:

SELECT modResource.* 
FROM 
`modx_site_content` AS `modResource` 
inner join 
(select * from modx_users) `t1` 
ON modResource.createdby = t1.id

Вообще, если у кого-то есть свои варианты решения данной задачи, напишите в комментариях, было бы интересно глянуть.

Итак, простого решения я не нашел. Вариант типа $q->leftJoin("(select * from modx_users)", «t1», «modResource.creteadby = t1.id»); не канает, так как xPDOQuery::leftJoin() ожидает первым параметром имя xPDO-класса, получив который будет пытаться определить имя таблицы методом xPDO::getTableName(). В результате мы получим ошибку:

Could not load class: (select * from modx_users) from mysql.(select * from modx_users)

Вот мой вариант решения этой задачи:

$q = $modx->newQuery('modResource');

$q->select(array(
"modResource.*",
));

$table = $modx->getTableName('modUser');

$q->query['from']['joins'][] = array(
    "type" => "inner join",
    "table" => "(select * from {$table})",
    "alias" => "t1",
    "conditions" => array(
        new xPDOQueryCondition(array(
            "sql" => "modResource.createdby = t1.id",
        )),
    ),
);

$s = $q->prepare();

print $q->toSQL();

Можно этот же запрос расширить xPDO-подзапросом:

$q = $modx->newQuery('modResource');

$q->select(array(
    "modResource.*",
));

$q2 = $modx->newQuery('modUser', array(
    "username" => "Fi1osof",
));
$q2->select(array(
    "modUser.*",
));

$q2->prepare();
$sql = $q2->toSQL();

$q->query['from']['joins'][] = array(
    "type" => "inner join",
    "table" => "({$sql})",
    "alias" => "t1",
    "conditions" => array(
        new xPDOQueryCondition(array(
            "sql" => "modResource.createdby = t1.id",
        )),
    ),
);

$s = $q->prepare();

print $q->toSQL();

На выходе получаем SQL-запрос:

SELECT modResource.* 
FROM 
    `modx_site_content` AS `modResource` 
    inner join (
        SELECT 
        modUser.* 
        FROM 
            `modx_users` AS `modUser` 
        WHERE 
            `modUser`.`username` = 'Fi1osof' 
   ) `t1` 
ON modResource.createdby = t1.id

Такой метод формирования запроса хорош тем, что имеющийся объект xPDOQuery $q можно использовать и для подсчета количества найденных строк$modx->getCount('modResource', $q), и для установки лимитов$q->limit($limit), и для сортировки$q->sortby(«modResource.id», «ASC»); и т.д. и т.п. Так же можно и в объекты сразу набивать $modx->getCollection(«modResource»,$q) или $modx->getIterator(«modResource»,$q). В общем, xPDO API можно использовать в полной мере.

Так же в процессе поиска данного решения был освоен и иной промежуточный вариант:

$q = $modx->newQuery('modResource');

$q->select(array(
"modResource.*",
"t1.*",
));

$q->query['from']['tables'][] = array(
"table" => "(select * from modx_users)",
"alias" => "t1",
);

$q->where(array(
"modResource.createdby = t1.id",
));

print_r($q->query);
$s = $q->prepare();

print $q->toSQL();

Получаемый запрос:

SELECT 
modResource.*, 
t1.* 
FROM 
`modx_site_content` AS `modResource`, 
(select * from modx_users) AS `t1` 
WHERE 
modResource.createdby = t1.id

Но, во-первых, это эквивалент inner join (то есть left join уже не получается, если нужен), а во-вторых, давно уже такой вариант запроса не считается стандартом, JOIN-ы предпочтительней.