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

Вообще с этой задачей бился не один год, и вот только сегодня победил…
Задача: средствами 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-ы предпочтительней.
3 комментария
c
core01 12 апреля 2016г в 00:51 #
А что с производительностью, ведь подзапросы нагружают базу?
Может быть я чего-то не понимаю, но почему бы не сделать leftJoin modResource и modUser, или это просто пример не удачный?
Fi1osof1
Fi1osof 12 апреля 2016г в 01:13 #
Пример не неудачный, он просто простой. Вот пример посложнее:
$q2 = $this->modx->newQuery($this->classKey);
        
        $offersAlias = "offer";
        
        $q2->setClassAlias($offersAlias);
        
        $q2->select(array(
            "property_198",
            "GROUP_CONCAT({$offersAlias}.id SEPARATOR '||') as offers_id", 
            "GROUP_CONCAT({$offersAlias}.price SEPARATOR '||') as prices", 
            "GROUP_CONCAT({$offersAlias}.article SEPARATOR '||') as articles", 
            "GROUP_CONCAT({$offersAlias}.packaging SEPARATOR '||') as packages", 
        ));
        
        $q2->where(array(
            "{$offersAlias}.published" => 1,
            "{$offersAlias}.deleted" => 0,
            "{$offersAlias}.hidemenu" => 0,
            "{$offersAlias}.property_198:!=" => "", 
        ));
        
        $q2->groupby("{$offersAlias}.property_198");
        
        $q2->prepare();
        
        $sql = $q2->toSQL();
        
        $c->query['from']['joins'][] = array(
            "type"      => "left join",
            "table" => "({$sql})",
            "alias" => "t1",
            "conditions"    => array(
                new xPDOQueryCondition(array(
                    "sql" => "{$alias}.property_198 = t1.property_198",
                )),
            ),
        );
        
        $c->select(array(
            "if(t1.offers_id IS NOT NULL, t1.offers_id, {$alias}.id) as offers_id", 
            "if(t1.prices IS NOT NULL, t1.prices, {$alias}.price) as prices", 
            "if(t1.articles IS NOT NULL, t1.articles, {$alias}.article) as articles", 
            "if(t1.packages IS NOT NULL, t1.packages, {$alias}.packaging) as packages", 
        ));


SQL:
SELECT 
    if(modResource.remains > 0 , 1, 0) as in_store, 
    if(t1.offers_id IS NOT NULL, t1.offers_id, modResource.id) as offers_id, 
    if(t1.prices IS NOT NULL, t1.prices, modResource.price) as prices, 
    if(t1.articles IS NOT NULL, t1.articles, modResource.article) as articles, 
    if(t1.packages IS NOT NULL, t1.packages, modResource.packaging) as packages, 
FROM 
    `modx_site_content` AS `modResource` 
left join (
    SELECT `property_198`, 
    GROUP_CONCAT(offer.id SEPARATOR '||') as offers_id, 
    GROUP_CONCAT(offer.price SEPARATOR '||') as prices, 
    GROUP_CONCAT(offer.article SEPARATOR '||') as articles,
    GROUP_CONCAT(offer.packaging SEPARATOR '||') as packages 
FROM 
    `modx_site_content` AS `offer` 
    WHERE  ( 
        `offer`.`published` = 1 
        AND `offer`.`deleted` = 0 
        AND `offer`.`hidemenu` = 0 
        AND `offer`.`property_198` != '' 
    )  
    GROUP BY offer.property_198 
) `t1` 
    ON modResource.property_198 = t1.property_198 
WHERE  (  
    (
         `modResource`.`deleted` = 0 
        AND `modResource`.`hidemenu` = 0 
        AND `modResource`.`published` = 1 
    )  
    AND `modResource`.`template` in (3,21) 
)


Здесь выборка несколько килотоваров с группировкой по полю (тут товары с вариациями товаров). И вот когда у всех товаров были заполнено это дополнительное поле, участвующее в группировке, тогда да, обычным запросом обходился. А теперь появились товары, у которых это поле не было заполнено. Пришлось переписывать запрос, так как если я сейчас сделаю группировку по этому незаполненному полю, у меня будут сотни товаров в одной карточке, а если я исключу такие товары, то они у меня просто не попадут в выборку.
Fi1osof1
Fi1osof 12 апреля 2016г в 01:17 #
На счет производительности: не самый шустрый вариант, конечно, но более хитрый вариант без подзапроса с дополнительными условиями вообще мускул ложили. В целом, джоинить такой подзапрос не особо нагруженно, так как там получается связь один-к-одному. Здесь выборка из нескольких килотоваров на холодную 2 секунды занимает. Запросы кешируются, так что в целом сайт будет работать нормально.
Авторизуйтесь или зарегистрируйтесь (можно через соцсети ), чтобы оставлять комментарии.