JDatabaseQuery - использование union-методов в запросах

Перед прочтением этого материала рекомендуем вам ознакомиться со следующими материалами:

Общая информация

Использование UNION при составлении запросов позволяет удобно комбинировать результаты двух или более SELECT запросов, которые необязательно соединены какими-либо отношениями.

Это также может быть полезной оптимизацией производительности. Использование UNION для объединения результата запросов иногда может быть намного быстрее, чем единичный запрос с WHERE, в особенности тогда, когда запрос включает в себя объединение с другими большими таблицами.

Для тех, кто хоть немного знаком с теорией набора - UNION делает именно то, что от него ожидают. Он сливает набор результатов из одного запроса с набором результатов другого запроса в один единый набор результатов.

Для того чтобы использовать UNION, вы должны быть знакомы с основными требованиями, предъявляемыми SQL-сервером, который вы используете. Они не зависят от Joomla, но если вы не будете с ними считаться, то получите ошибки базы данных. Например, SELECT запрос должен возвращать такое же самое число полей, в той же самой последовательности и с тем же самым типом данных.

Простой пример

Предположим, что вы хотите сделать рассылку группе людей, но их имена и адреса электронной почты находятся в разных таблицах. Давайте разберем конкретный пример. Вы хотите сделать рассылку клиентам и поставщикам, и их имена и адреса электронной почты находятся в таблицах customers и suppliers соответственно.

Этот запрос вытащит всю необходимую информацию по клиентам для рассылки:

$query
    ->select(
        $db->quoteName(
            array('name', 'email')
        )
    )
    ->from($db->quoteName('#__customers'));

А этот запрос сделает тоже самое по поставщикам:

$query
    ->select(
        $db->quoteName(
            array('name', 'email')
        )
    )
    ->from($db->quoteName('#__suppliers'));

Вы можете объединить это в один запрос следующим образом:

$query
    ->select(
        $db->quoteName(
            array('name', 'email')
        )
    )
    ->from($db->quoteName('#__customers'))
    ->union(
        $q2->select(
            $db->quoteName(
                array('name', 'email')
            )
        )
        ->from($db->quoteName('#__suppliers'))
    );
 
$mailshot = $db->setQuery($query)->loadObjectList();

На самом деле набор результатов, который получается при использовании union(), будет немного отличаться от отдельных запросов, потому что union() автоматически удалит дубликаты. Если же вы хотите, чтобы в результирующем наборе остались дубликаты (что в принципе с математической точки зрения уже не является набором), используйте метод unionAll() вместо union().

Разные варианты использования union

Методы union() и unionAll() довольно гибки при передаче в них аргументов. Вы можете передать просто строку запроса, объект JDatabaseQuery или массив объектов JDatabaseQuery. Предположим, что у вас есть три таблицы, похожие на пример выше:

$q1
    ->select(
        $db->quoteName(
            array('name', 'email')
        )
    )
    ->from($db->quoteName('#__customers'))
 
$q2
    ->select(
        $db->quoteName(
            array('name', 'email')
        )
    )
    ->from($db->quoteName('#__suppliers'))
 
$q3
    ->select(
        $db->quoteName(
            array('name', 'email')
        )
    )
    ->from($db->quoteName('#__ shareholders '));

Тогда все эти запросы дадут один и тот же результат:

// Метод union может стыковаться
$q1
->union($q2)
->union($q3);
 
// Метод union принимает строковые запросы
$q1
->union($q2)
->union('SELECT name, email FROM shareholders');
 
// Метод union принимает массив объектов JDatabaseQuery 
$q1->union(
    array($q2, $q3)
);
 
// Не важно, какой из объектов является корневым. 
// В данном случае сам запрос будет немного другим, но результат будет таким же.
$q2->union(
    array($q1, $q3)
);

Использование UNION вместо OR

В некоторых случаях использование union() может привести к значительному приросту производительности по сравнению с использованием OR или WHERE вместе c IN.

Предположим, что у вас есть таблица продуктов, и вы хотите выделить только те продукты, которые принадлежат двум конкретным категориям. Обычно это делают так:

$query
    ->select('*')
    ->from($db->quoteName('#__products'))
    ->where(
        $db->quoteName('category')
        . ' = '
        . $db->quote('catA'), 'or')
    ->where(
        $db->quoteName('category')
        . ' = '
        . $db->quote('catB')
    );
 
$products = $db->setQuery($query)->loadObjectList();

Но, вполне вероятно, что вы увидите прирост производительности при использовании union():

$query
    ->select('*')
    ->from($db->quoteName('#__products'))
    ->where(
        $db->quoteName('category')
        . ' = '
        . $db->quote('catA')
);
 
$q2
    ->select('*')
    ->from($db->quoteName('#__products'))
    ->where(
        $db->quoteName('category')
        . ' = '
        . $db->quote('catB')

$query->union($q2);
 
$products = $db->setQuery($query)->loadObjectList();

Сортировка результата

Если вы хотите отсортировать результат, вы должны знать о том, каким образом база данных поступает с условием ORDER BY. Далее речь пойдет о MySQL, но вероятно это также применимо и к другим движкам базы данных.

Предположим, вы хотите отсортировать имена и адреса электронной почты из примера с рассылкой выше в алфавитном порядке:

$q2
    ->select(
        $db->quoteName(
            array('name', 'email')
        )
    )
    ->from($db->quoteName('#__suppliers'));
 
$query
    ->select(
        $db->quoteName(
            array('name', 'email')
        )
    )
    ->from($db->quoteName('#__customers'))
    ->union($q2)
    ->order($db->quoteName('name'));
 
$mailshot = $db->setQuery($query)->loadObjectList();

Давайте представим, что дополнительно вам необходимо сделать так, чтобы сначала шли все клиенты, а потом поставщики. Этот запрос не даст ожидаемого результата:

$q2
    ->select(
        $db->quoteName(
            array('name', 'email')
        )
    )
    ->from($db->quoteName('#__suppliers'))
    ->order($db->quoteName('name'));
 
$query
    ->select(
        $db->quoteName(
            array('name', 'email')
        )
    )
    ->from($db->quoteName('#__customers'))
    ->order($db->quoteName('name'))
    ->union($q2);
 
$mailshot = $db->setQuery($query)->loadObjectList();

Все дело в том, что ORDER BY в отдельном операторе SELECT никак не влияет на сортировку в результирующем наборе. Запрос выше синтаксически верен, но MySQL оптимизатор просто проигнорирует ORDER BY в запросе SELECT по suppliers, и будет применен ORDER BY из SELECT запроса по customers.

Чтобы обойти эту проблему, необходимо добавить дополнительную колонку к набору результата и сортировать по ней. Вот один из вариантов:

$q2
    ->select(
        $db->quoteName(
            array('name , email, 1 as sort_col')
        )
    )
    ->from($db->quoteName('#__suppliers'));
 
$query
    ->select(
        $db->quoteName(
            array('name , email, 2 as sort_col')
        )
    )
    ->from($db->quoteName('#__customers'))
    ->union($q2)
    ->order(
        $db->quoteName('sort_col')
        . ', ' 
        . $db->quoteName('name')
    );
 
$mailshot = $db->setQuery($query)->loadObjectList();

Расширенная сортировка

Однако возникают случаи, когда важно иметь возможность сортировки ORDER BY по отдельным запросам, которые не будут проигнорированы оптимизатором.

Предположим, вы хотите отправить специальное предложение для топ 10 клиентов и топ 5 поставщиков. Для этого примените условие LIMIT в комбинации с ORDER BY и используйте фиктивный запрос:

$q2
    ->select(
        $db->quoteName(
            array('name , email, 1 as sort_col')
        )
    ->from($db->quoteName('#__suppliers'))
    ->order($db->quoteName('turnover') .' DESC')
    ->setLimit(5);
 
$q1
    ->select(
        $db->quoteName(
            array('name , email, 2 as sort_col')
        )
    ->from($db->quoteName('#__customers'))
    ->order($db->quoteName('turnover') .' DESC')
    ->setLimit(10);
 
$query
    ->select(
        $db->quoteName(
            array('name , email, 0 as sort_col')
        )
    ->from($db->quoteName('#__customers'))
    ->where('1 = 0')
    ->union($q1)
    ->union($q2)
    ->order('sort_col, name');
    ->order(
        $db->quoteName('sort_col')
        . ', ' 
        . $db->quoteName('name')
    );
 
$mailshot = $db->setQuery($query)->loadObjectList();

Если не использовать фиктивный запрос, то сортировка и лимит будут добавлены к результирующему набору, а не к отдельному.