-
Notifications
You must be signed in to change notification settings - Fork 9
sql
Данная процедура предназначена для того, чтобы минимизировать количество SQL-кода в исходных текстах приложения, упростить формирование запросов к БД в подавляющем большинстве стандартных ситуаций и поспособствовать использованию максимально эффективных схем данных. В некоторой степени аналогичный подход применяется в модуле SQL::Abstract.
Прежде чем описывать различные варианты вызова функции sql и формат соответствующих результатов, оговорим некоторые ограничения на рассматриваемое множество запросов, а также введём некоторые понятия, которые будут использоваться ниже.
Функция sql не поддерживает и, вероятно, никогда не будет поддерживать синтаксические конструкции UNION, INTERSECT, MINUS и им подобных. Мы убеждены, что необходимость применения данных возможностей языка SQL в приложениях (в всяком случае, при вводе данных) в подавляющем большинстве случаев связана с ошибками проектирования схемы БД, так что приветствовать их использование путём какой-либо автоматизации не стоит. Поясним нашу мысль на примере UNION из 2 подзапросов.
Рассмотрим вначале ситуацию, в которой выражения FROM обоих слагаемых совпадают. В этом случае запросы, скорее всего, различаются условиями WHERE, и их объединение идентично результату одного запроса с тем же FROM и OR-комбинацией условий WHERE. По требованиям к ресурсам UNION, скорее всего, будет по крайней мере не оптимальнее OR-запроса (а может быть, и хуже).
Если же производится объединение записей из разных таблиц, то, скорее всего, модель данных составлена неверно: в действительности извлекаемые данные должны храниться в одной таблице.
Любой план запроса (не использующего множественных операций) можно упрощённо представить в виде вложенного цикла, где каждый уровень соответствует перебору записей одной из таблиц из FROM-части запроса. То есть
SELECT ... FROM t1, t2,... tn
это всегда, условно говоря,
foreach $r1 (@t1) {
foreach $r2 (@t2) {
...
foreach $rn (@tn) {
...
if (...) {
push @r, ...;
}
}
...
}
}
за которым, возможно, следует
@r = sort {...} @r; # дополнительная сортировка
Теоретически (в особенности если писать условия связи в раздел WHERE, как делалось с древнейших времён, а в Oracle, например, так до выхода 9-й версии в 2001 г.) все таблицы выглядят равноправными, а выборку можно считать результатом фильтрации их декартова произведения.
Однако на практике условия связи таблиц могут учитываться не в самом глубоко вложенном теле, а при формировании списка для каждого очередного foreach
foreach $r1 (@t1) {
unless (...) {next}
foreach $r2 (t2 ($r1)) {
unless (...) {next}
...
foreach $rn (tn ($r1, $r2,...)) {
unless (...) {next}
push @r, ...;
}
...
}
}
что влияет на производительность самым серьёзным образом. Дабы минимизировать число итераций, необходимо добиваться того, чтобы условия на подмножества @t2, ... @tn были как можно более ограничительными. В идеале эти подмножества должны содержать не более 1 элемента. Тогда вложенные циклы вообще исчезают:
foreach $r1 (@t1) {
unless (...) {next}
$r2 = t2 ($r1);
$r3 = t3 ($r1, $r2);
...
$rn = tn ($r1, $r2,...);
push @r, ...;
}
Так и происходит, если @t1 (назовём её корневой таблицей) — таблица фактов (например, договоров), а прочие таблицы — справочники (типы договоров, статусы, контрагенты, страны, города и пр.). При этом общее число итераций за время выполнения запроса равно числу просмотренных записей корневой таблицы.
Если Вы задублировали в таблице фактов всю информацию, необходимую для поиска и сортировки, привели WHERE и ORDER BY к той же корневой таблице (о WHERE-фильтрах см. также здесь) и грамотно построили индексы, то запрос будет выполнен за 1 проход по B-дереву без дополнительной сортировки, то есть оптимально. К этому необходимо стремиться. С точки зрения скорости запросов, идеально на каждую комбинацию условий с порядком сортировки иметь по индексу, но это может снизить общую эффективность уже за счёт замедления операций записи и раздувания БД.
Первые версии функции sql поддерживали только запросы описанной, "звёздчатой", структуры, где все фильтры приведены к корню. В настоящее время это ограничение снято, хотя понятие корневой таблицы по-прежнему имеет смысл.
Перейдём, наконец, к описанию функции sql. Вначале рассмотрим не самый частый, но хорошо иллюстрирующий основные используемые опции случай.
Допустим, вы разрабатываете экран просмотра для сущности "Поручения" (tasks). Текущая запись из tasks доступна как $data. С поручением связан список реплик (task_notes), связанный с родительской записью по полю id_task. Требуется вывести список последних 15 реплик в порядке убывания их id. Соответствующий вызов sql выглядит так:
Для сравнения приведём эквивалентный вызов sql_select_all_cnt:
Сразу отметим, что функция sql может как добавлять свои результаты компонентами переданного ей хэша, так и просто возвращать их в стек. Следующие 2 вызова эквивалентны:
($data -> {task_notes}, $data -> {cnt}) = sql ($data, task_notes => ...);
На первый взгляд, экономия не особенно существенная: только за счёт отмены минимум 4-кратного повторения имени корневой таблицы task_notes. Что, впрочем, само по себе уже неплохо: ведь в каждом из этих 4 мест можно опечататься.
Комфорт ощущается сильнее тогда, когда мы вспоминаем, что task_notes ссылается на справочники. Допустим, там есть 3 ссылки:
- id_voc_status на voc_status;
- id_user на users;
- id_author опять на users.
sql ($data, task_notes => [
[ id_task => $data -> {id}],
[ ORDER => 'id DESC'],
[ LIMIT => [0 + $_REQUEST {start}, 15] ],
], 'voc_status', 'users', 'users AS authors');
Снова приведём аналог с полным текстом SQL:
($data -> {task_notes}, $data -> {cnt}) = [[sql_select_all_cnt]] (<<EOS, $data -> {id}, {fake => 'task_notes'});
SELECT
task_notes.*
, voc_status.label AS voc_status_label
, users.label AS user_label
, authors.label AS author_label
FROM
task_notes
LEFT JOIN voc_status ON task_notes.id_voc_status = voc_status.id
LEFT JOIN users ON task_notes.id_user = users.id
LEFT JOIN users AS authors ON task_notes.id_author = authors.id
WHERE
task_notes.id_task = ?
ORDER BY
task_notes.id DESC
LIMIT
$_REQUEST{start}, 15
EOS
Ссылающиеся поля определяются автоматически, по именам таблиц или синонимов. При этом для вычисления единственного числа применяется процедура en_unplural. В частности, она принимает во внимание тот факт, что английское слово status во множественном числе пишется как status.
Корневая таблица может содержать ссылки не на все указанные справочники, предусмотрен вариант с каскадным прикреплением. Например, при корневой task_notes, где нет поля id_role, возможен список таблиц
'users', 'roles'
тогда будет установлен
LEFT JOIN roles ON users.id_role = roles.id
В вышеприведённом примере результаты работы sql_select_all_cnt и sql будут немного отличаться: то, что в первом случае доступно как
$data -> {task_notes} -> [0] -> {author_label}
во втором случае пишется
$data -> {task_notes} -> [0] -> {author} -> {label}
Таким образом, поля, соответствующие справочникам, находятся не на уровне полей корневой таблицы, а собраны в хэши 2-го уровня. По умолчанию каждый такой хэш содержит поля id и label. Но это легко переопределить. Например, если Вам требуется достать код иконки, соответствующей статусу (voc_status.icon), это будет выглядеть так:
sql ($data, task_notes => [
[ id_task => $data -> {id}],
[ ORDER => 'id DESC'],
[ LIMIT => [0 + $_REQUEST {start}, 15] ],
], 'voc_status (id, label, icon)', 'users', 'users AS authors');
или вообще
sql ($data, task_notes => [
[ id_task => $data -> {id}],
[ ORDER => 'id DESC'],
[ LIMIT => [0 + $_REQUEST {start}, 15] ],
], 'voc_status (*)', 'users', 'users AS authors');
Теперь обратимся к строкам 2-4 нашего примера. Их смысл очевиден, однако настало время уточнить: 2-я строка определяет фильтр (одно из условий WHERE), её 1-й элемент соответствует имени столбца таблицы, таких строк может быть сколько угодно. О фильтрах мы подробнее расскажем чуть ниже. А строки 3 и 4 соответствуют выражениям ORDER BY и LIMIT и встречаются в одном вызове не более одного раза.
Данная опция определяет порядок сортировки выборки. По умолчанию принимается 'label', если такое поле упомянуто в описании схемы данных, в противном случае — 'id'. Таким образом, если, например, требуется список всех дочерних (по полю parent) подразделений текущего, можно не указывать ни LIMIT, ни ORDER:
sql ($data, departments => [
[parent => $data -> {id}],
]);
Значение аргумента псевдофильтра ORDER подставляется в результирующий SQL не напрямую, а обрабатывается функцией order. Что можно использовать при управлении сортировкой примерно таким образом:
sql ($data, goods => [ ... [ORDER => ['label', 'price' => 'price, label ASC!', ]] ]);
В приведённом примере при $_REQUEST {order} eq 'price' имеет место упорядочение сначала по цене (по возрастанию или убыванию в зависимости от $_REQUEST {desc}), а потом по наименованию (всегда в прямом алфавитном порядке). По умолчанию же выборка сортируется только по наименованию.
Данная опция определяет ограничение на количество записей в выборке. Предусмотрено 2 принципиально разных варианта её использования.
Первый вариант описан выше, при этом указывается 2 аргумента: начало и объём выборки. Если LIMIT указан с 2 аргументами, то функция sql, как и sql_select_all_cnt, возвращает 2 результата: выборку (список хэшей) и количество записей без учёта ограничителя.
my ($ad_users, $cnt) = sql (
ad_users => [
'id_ad_server',
['(label LIKE %?% OR login LIKE %?%)' => [$_REQUEST {q}, $_REQUEST {q}]],
['ISNULL(id_user)' => $_REQUEST {empty}],
[ LIMIT => [0 + $_REQUEST {start}, 50]],
],
);
Значения для псевдофильтра LIMIT в приведённом запросе можно указать в форме:
[LIMIT => 'start, 50'],
тогда они будут развёрнуты в массив автоматически.
Если же аргументом является число 1:
[LIMIT => 1],
то результатом sql является единственная ссылка на хэш (не список!) При этом функция sql используется в режиме выборки отдельных записей. Данная форма вызова sql характерна для запросов типа "последнее действие такого-то типа":
my $last_log = sql (log => [ [ type => $type], [ ORDER => 'id DESC'], [ LIMIT => 1], ], 'users');
Такой фильтр имеет сокращённую форму записи:
[1]
удобную для однострочников типа
my $id = [[sql]] ('departments(id)' => [[label => 'Руководство'], [1]]);
Отметим, что в последнем случае sql возвращает не хэш, а скаляр. Так происходит, когда, помимо фильтра на единственность записи, указано единственное поле для выборки.
Ограничение выборки, как правило, имеет смысл только при фиксированном порядке сортировки (в PostgreSQL обратное вообще считается ошибкой). Отсюда возникает естественная мысль: включить поля для ORDER BY в псевдофильтр LIMIT, а не тратить на них целую строку:
[LIMIT => 'start, 50 BY code'],
После BY указывается то, что будет подставлено в ORDER BY: список полей, которые можно сопровождать модификаторами DESC и, при желании, ASC. Если требуется сортировка по единственному полю, то ключевое слово DESC смотрится несколько громоздко. Но вместо него можно указать отрицательное значение для размера выборки. Например, последняя по id запись в таблице log выбирается вот так:
my $last_log = sql (log => [ ['-1 BY id'] ]);
(мнемоника: -1-й элемент в массиве log).
Выше мы рассмотрели использование функции sql с фильтром по ссылке на родительский объект. Обычно такие выборки (дочерних объектов) встречаются на формах редактирования и просмотра отдельных записей. Выборки для экранов-списков формируются совершенно аналогично, только вместо одного обязательного фильтра у них, как правило, множество необязательных (подробнее о необязательности — чуть ниже):
Ещё раз напомним, что все определённые здесь условия могут относиться к полям только одной — корневой — таблицы.
В случае экрана-карточки извлечение данных начинается с хэша, соответствующего текущей записи в тематической таблице. Тогда, как было показано, sql может добавлять свои результаты в виде дополнительных компонент. У экрана-списка такой записи нет, но начальный хэш задать всё равно удобно:
return sql ({}, tasks => [...], ...);
Как правило, экран-список использует несколько словарей данных, порождаемых функцией add_vocabularies. Если воспользоваться её результатом в качестве стартового хэша, то можно оформить извлечение всех данных для экрана-списка как один вызов:
Именно такой вариант заложен в стандартный шаблон StEludio для экрана-списка.
Заголовок фильтра, помимо имени поля (вообще говоря, левой части условия) может содержать оператор сравнения. Собственно, он содержит его всегда, просто по умолчанию справа приписывается ' ='.
Аналогичным образом к коду добавляется placeholder ('?'), если пользователь ни разу не указал его явно.
Вообще в левой части фильтра можно приводить любые логические выражения от полей корневой таблицы, пригодные для подстановки в WHERE. При этом необходимо проставлять все необходимые placeholders и в правой части приводить соответствующее число значений:
sql (orgs => [
...
['inn = ? OR ogrn = ?' => [$_REQUEST {code}, $_REQUEST {code}]],
...
], 'voc_status', 'users', 'users AS authors');
В подавляющем большинстве случаев пустое значение фильтрующего параметра (HTTP-) запроса соответствует не требованию поставить условие "IS NULL", а наоборот, вовсе не использовать соответствующее поле в отборе записей. Именно так и работают фильтры в sql. Значение "0" пустым не считается. Итак, фильтр
[id_task_type => $_REQUEST {id_task_type}],
повлияет на сгенерированный SQL только в том случае, если id_task_type установлен. Обычно подобные параметры передаются select-списками с верхней панели при таблице, у которых по умолчанию выбрана первая строка, определённая опцией empty: "[Все]" или что-то в этом роде.
Если значений в фильтре более одного, то на непустоту проверяется только первое. Только оно может блокировать фильтр, вне зависимости от остальных элементов списка.
Итак, значение undef никогда не подставляется в запрос в качестве параметра — вместо этого оно отключает условие фильтрации в целом. Если же вам действительно требуется использовать SQL-предикат IS NULL, его можно привести в явном виде без параметра:
['dt_finish IS NULL'],
Аналогичным образом оформляется условие IS NOT NULL.
Имя переменной в приведённом примере стандартно для поля "Дата окончания периода". Пустое значение такого поля обычно соответствует формулировке "по текущее время", с гипотетическим продолжением до бесконечности. Рассмотрим стандартную задачу: найти все интервалы, даты окончания которых находятся в будущем относительно $dt. Поскольку интервалы могут быть как закрытыми, так и открытыми, на чистом SQL данное условие формулируется как
dt_finish >= '$dt' OR dt_finish IS NULL
Функция SQL предлагает для такой ситуации (OR IS NULL) компактный синтаксис фильтра:
['dt_finish... >= ' => $dt],
(отточие примерно соответствует фразе: "Э-э-э... Или там вообще пусто...")
Ту же задачу можно решить при помощи функции IFNULL:
['IFNULL(dt_finish, '$dt') >= ' => $dt],
однако в плане оптимизации запроса это всегда хуже. Да и в наглядности выигрыша никакого.
Зачастую фильтруемое поле совпадает по имени с параметром запроса, содержащим требуемое значение. В таких случаях значение можно не указывать: оно будет добыто из %_REQUEST автоматически.
Фильтр по полю fake
Как показано выше, sql учитывает корневую таблицу в качестве значения опции fake процедур sql_select_all и sql_select_all_cnt, то есть добавляет в WHERE условие на поле fake, исходя из значения параметра $_REQUEST {fake}. По умолчанию извлекаются записи с fake = 0, то есть актуальные.
Для LIKE предусмотрен специальный синтаксический элемент, в соответствии с которым к значению параметра приписываются спецсимволы '%': справа (?%) или с обеих сторон (%?%).
sql (tasks => [
'id_task_type',
'id_author',
'duration >=',
['label LIKE %?%' => $_REQUEST {q}],
[ LIMIT => [0 + $_REQUEST {start}, 15] ],
], 'voc_status', 'users', 'users AS authors');
Даты вводятся в поля форм и попадают в %_REQUEST в национальном формате, однако передавать их в sql необходимо в формате ISO (как и везде в Eludia), для чего следует использовать функцию dt_iso.
Значение '0000-00-00' считается пустым и отключает фильтр.
Имеет место следующая проблема: если в поле, скажем, "Когда создано поручение" фиксируется и дата, и время, то значение поля (например, "2008-09-07 17:15:27") превосходит то, которое соответствует дню как таковому (соответственно, "2008-09-07"). При этом в запросе вилка дат "Создано с ... по ..." указывается, естественно, без минут и секунд, так что значение с ненулевым временем как бы выходит за рамки своего дня.
Чтобы запрос исполнялся корректно, необходимо подправить либо левую часть условия, либо значение параметра. Второе лучше, поскольку индекс по функции от поля обойдётся либо дороже индекса по полю, либо вообще не поддерживается Вашей СУБД.
Для этой цели в sql введён специальный оператор сравнения: "меньше + 1 день"
['dt_created >=' => [[dt_iso]] ($_REQUEST {dt_from})],
['dt_created <+' => [[dt_iso]] ($_REQUEST {dt_to})],
[ LIMIT => [0 + $_REQUEST {start}, 15] ],
], 'voc_status', 'users', 'users AS authors');
Выше приведён пример поиска записей, у которых значение заданного поля попадает в нужный интервал. Часто при работе с датами возникает двойственная задача: найти записи, для которых заданная константа ("сегодня") попадает в интервал между значениями двух полей ("начало" и "окончание"). На SQL это выглядит так:
AND dt_start <= $now AND dt_finish >= $now
для функции sql можно использовать фильтр вида:
['dt_start .. dt_finish' => $now],
Если ситуация dt_finish IS NULL соответствует окончанию в бесконечности (незакрытый интервал), то можно поставить отточие после dt_finish:
['dt_start .. dt_finish...' => $now],
Это породит SQL вида
AND dt_start <= $now AND ((dt_finish >= $now) OR (dt_finish IS NULL))
Интервалы можно искать не только по отдельным точкам, но и по условию пересечения с заданными интервалами. Выглядит это просто:
['dt_start .. dt_finish...' => [$from, $to]],
Такой фильтр порождает условие, где начала и окончания периодов сопоставляются "крест-накрест":
AND dt_start <= $to AND ((dt_finish >= $from) OR (dt_finish IS NULL))
Если Вы накладываете все условия WHERE на одну таблицу (что всячески приветствуется sql), то зачастую возникает необходимость определить фильтр по принадлежности или, наоборот, не принадлежности к некоторому подмножеству. В простом случае, когда множество известно как список констант (например, [1,]) в точке вызова sql, этот фильтр выглядит так:
[id_type => [1, 2, 5] ],
что является сокращённой формой для
['id_type IN' => [1, 2, 5] ]
Обратный фильтр (непринадлежность) записывается так:
['id_type NOT IN' => [1, 2, 5] ],
Если же список id зависит от параметров запроса и/или состояния БД, то для формирования фильтра рекомендуется снова воспользоваться функцией sql. Например, фильтр "тип задач — один из относящихся к текущему пользователю" запишется таким образом:
[id_type =>
sql ('users_task_types(id_task_type)' => [
[id_user => $_USER -> {id}],
])
],
Здесь у корневой таблицы указан список выбора из единственного поля (id_task_type). В таком режиме функция sql возвращает ссылку не на список и не на хэш, а на скаляр. При конкатенации этот скаляр ведёт себя как строка с непустым списком id (по аналогии с результатом sql_select_ids), однако он ещё несёт дополнительную информацию о том SQL-запросе, которым был создан, и его параметрах.
Делается это для того, чтобы иметь возможность подставить в скобки после IN либо список цифр через запятую (для MySQL), либо SQL-код подзапроса (для остальных СУБД). Это необходимо, поскольку реализация подзапросов в MySQL недопустимо плоха (оптимизатор не умеет пользоваться некоррелированностью), зато отлично работают IN-списки, а в Oracle, скажем, всё в порядке с подзапросами, но крайне нежелательно раздувать SQL-код длинными списками констант, да и накладные расходы на каждый вызов ощутимые.
Фильтры по подмножеству можно использовать в качестве фильтров по полям присоединённых таблиц. Например, если вы ищете платежи, относящиеся к товарам (voc_goods), названия которых начинаются на $_REQUEST {q}, фильтр запишется следующим образом:
[id_voc_goods =>
sql ('voc_goods(id)' => [
['label LIKE ?%' => $_REQUEST {q}],
])
],
Внимание! При пустом $_REQUEST {q} (вообще sql на единственный явно обозначенный столбец и без единого актуального фильтра) вложенный вызов вернёт undef — специально для того, чтобы отключить внешнее IN-условие. Это может создать недоразумение, если Вы используете внутренний вызов сам по себе. Его легко избежать, если взять за правило всегда выбирать хотя бы 2 поля.
Всюду выше предполагалось, что ссылки идут в одном направлении: от корневой таблицы к присоединённым, то есть на одну запись корневой таблицы приходится не более одной записи результирующей выборки. Сейчас мы научимся преодолевать это ограничение — в результате получатся запросы, где к корневой таблице присоединяются множественные дочерние записи (возможно, в несколько каскадов).
Допустим, нам необходимо составить выборку из таблиц договоров contrats и платежей pays (условие связи pays.id_contrat = contrats.id), где будут присутствовать все договора, удовлетворяющие некоторым условиям, плюс для каждого из них все платежи (возможно, ни одного). Соответствующий вызов выглядит так:
[[sql]] (contrats => [ ... # фильтры ], ['pays']);
Как нетрудно убедиться, всё отличие заключается в том, что имя присоединённой таблицы приведено не в виде строки, а заключено в список. В результате функция sql не будет искать ссылку contrats.id_pay, а установит связь по pays.id_contrat.
Список здесь применён не случайно. Вторым его элементом может быть список фильтров, совершенно аналогичный тому, что имеется у основного запроса. Например, все актуальные (неудалённые) платежи с суммой в заданной вилке будут показаны для
[[sql]] (contrats => [
... # фильтры
], [pays => [
[ [[fake]] => 0],
['total BETWEEN ? AND ?' => [$_REQUEST {from}, $_REQUEST {to}]],
]]);
При непустых значениях $_REQUEST {from} и $_REQUEST {to} соответствующий запрос будет содержать фрагмент
FROM contrats LEFT JOIN pays ON (pays.id_contrat = contrats.id AND pays.fake = 0 AND pays.total BETWEEN ? AND ? )
Условие внутри LEFT JOIN действует только на отбор дочерних записей при фиксированном родительском id и не является фильтром на выборку в целом.
Всюду выше показаны примеры того, как строить запросы с необязательным присоединением дополнительных таблиц к корневой, то есть LEFT JOIN. Иногда имеет смысл использовать жёсткое соединение: INNER JOIN. Чтобы сгенерировать такой код, к имени таблицы следует приписать префикс "-". Если так поступить в последнем примере:
[[sql]] (contrats => [
... # фильтры
], [-pays => [
[ [[fake]] => 0],
['total BETWEEN ? AND ?' => [$_REQUEST {from}, $_REQUEST {to}]],
]]);
то результирующий запрос
FROM contrats INNER JOIN pays ON (pays.id_contrat = contrats.id AND pays.fake = 0 AND pays.total BETWEEN ? AND ? )
существенно изменит смысл: контракты без платежей в указанном промежутке времени в выборку не попадут. То есть фильтры на присоединённую таблицу станут глобальными.
Итак, sql предоставляет 2 способа наложить на выборку условие по присоединённой таблице: IN-фильтр и фильтр при INNER JOIN. Первый вариант хорош, если выбираемое подмножество гарантированно мало (скажем, если присоединяемая таблица — это справочник всего на десяток строк или связная таблица "сотрудник — рабочая группа", где для любому пользователю соответствует максимум строки 3), второй может оказаться оптимальным, если, наоборот, отфильтрованное множество id содержит, допустим, сотни тысяч элементов.
В sql делается всё, чтобы условия связи таблиц вычислялись автоматически. Однако существуют ситуации, в которых явно указать join condition проще и нагляднее, нежели изобретать магическую параметризацию.
Рассмотрим пример. Допустим, в схеме определены таблицы
- dm_representatives (торговые представители)
- dm_supervisors (их начальники)
my $data = sql (dm_representatives => [[id => $_REQUEST {id}]]
, 'dm_supervisors'
, 'users AS representatives ON dm_representatives.id_user'
, 'users AS supervisors ON dm_supervisors.id_user'
);
Заявлять AS-псевдоним в обоих случаях не обязательно. Вообще говоря, для N вхождений одной таблицы необходимо N-1 псевдонимов.
В приведённом примере после ON упомянуто только ссылающееся поле: в этом случае sql приписывает " = $alias.id" автоматически. Впрочем, Вы можете указать здесь произвольные условия, корректные с точки зрения SQL: если там встретится хотя бы один пробельный символ, то ON-выражение будет подставлено в SQL как есть.
INNER JOIN, как и в случае с автоматическим определением связи, задаётся префиксом '-'.
Вот какой вид может принять наш пример, если добавить условие актуальности карточки пользователя-начальника и назвать её $data -> {user}:
my $data = sql (dm_representatives => [[id => $_REQUEST {id}]]
, 'dm_supervisors'
, 'users AS representatives ON dm_representatives.id_user'
, '-users ON dm_supervisors.id_user = users.id AND users.fake = 0'
);
В некоторых запросах встречаются условия, связанные с тем, чтобы для каждой интересующей записи в определённой таблице не было найдено ни одной дочерней строки. Например: найти карточки всех сотрудников (users), которые сейчас не в отпуске (schedule: id_user, is_vacation, dt_start, dt_finish).
Люди, привыкшие писать много вложенных подзапросов, часто употребляют в таких случаях конструкцию NOT EXISTS (SELECT ...). С точки зрения производительности это либо просто плохо, либо совершенно недопустимо: в зависимости от СУБД. Эффективно данная задача решается при помощи комбинации LEFT JOIN и условия IS NULL на поле id присоединяемой таблицы. Это называется ANTI-JOIN.
Функция sql предлагает для такого случая специальный синтаксис аргумента: он выглядит, как NOT EXISTS:
... ], ["NOT EXISTS schedule" => [ [is_vacation => 1], ['dt_start .. dt_finish' => $dt], ]]);
но вместо подзапроса порождает следующее:
SELECT ... FROM users LEFT JOIN schedule ON ( schedule.id_user = users.id AND schedule.dt_start <= ? AND schedule.dt_finish >= ? ) WHERE schedule.id IS NULL AND ...
Вообще "NOT EXISTS" смотрится страшно коряво, и вполне можно позволить себе грамотную форму "DOES NOT EXIST" или сокращённую "DOESN'T EXIST".
Параллельно поддерживается предикат EXISTS, но он особого самостоятельного смысла не имеет, так как равносилен INNER JOIN.
Если вы собираетесь пройти выборку один раз, вычислить значение некоторых переменных и в дальнейшем использовать их вместо исходного списка записей, имеет смысл определить callback-процедуру в духе sql_select_loop:
sql (pays => [
[ id_contrat => $data -> {id} ],
[ ORDER => 'no'],
], sub {
$nos .= '; ' if $nos;
$nos = $i -> {no};
});
Процедура приписывается в конец списка параметров sql и распознаётся там по типу (ссылка на код). Текущая запись доступна внутри callback-процедуры как глобальная переменная $i.
Выше мы вкратце коснулись случая, когда функция sql возвращает ссылку не на список, а на хэш. Происходит это в том случае, когда по построению запроса очевидно, что запись может быть лишь одна: при LIMIT => 1. То же самое мы имеем при указании фильтра по id (это поле в Eludia всегда является первичным ключом):
my $data = sql (tasks => [[id => $id]]);
Если $id == $_REQUEST {id}, то есть на экранах-карточках и в обработчиках действий, то фильтры можно не указывать вовсе:
my $data = sql ('tasks');
Здесь вызов sql выглядит полностью аналогично sql_select_hash. Однако если требуется одновременно извлечь несколько связанных записей, то минимализмом приходится чуть-чуть пожертвовать. Впрочем, это окупается: ведь
my $data = sql (docs => ['id'], 'departments', 'factories', 'groups');
эквивалентно
my $data = sql_select_hash ('docs');
$data -> {department} = sql_select_hash (departments => $data -> {id_department});
$data -> {factory} = sql_select_hash (factories => $data -> {department} -> {id_factory});
$data -> {group} = sql_select_hash (groups => $data -> {factory} -> {id_group});
Заметим, что в данном случае (то есть в режиме извлечения отдельной записи) у привязанных таблиц по умолчанию извлекаются все поля, так что нам не приходится явно заказывать, скажем departments(*) или departments(id, label, id_factory), как пришлось бы делать в режиме выборки списков записей. Это предусмотрено специально для экранов просмотра записей, содержащих сведения об охватывающих объектах (скажем, для карточки платежа — реквизиты договора, приказа о его заключении и т. п.)
Всюду выше предполагалось, что компонентами записей выборки могут быть только поля таблиц. Однако в этом качестве можно использовать произвольные выражения, поддерживаемые синтаксисом SQL. Имена таких полей можно задавать явно при помощи AS:
sql ("users (id, f || ' ' || LEFT(i, 1) || '. ' || LEFT(o, 1) || '.' AS fio)");
Если AS-опция не задана, то имя поля синтезируется автоматически. Например, в результате
sql ('users (UPPER(label))');
единственное поле будет называться upper_label. Вообще имена генерируются так: в выражении находятся все фрагменты, похожие на идентификаторы, после чего они при водятся к нижнему регистру и склеиваются через символ '_'.
В выражениях можно использовать агрегатные функции: такие, как COUNT, SUM, MAX. При этом, если запрашиваются одни лишь агрегаты, то выборка содержит только одну запись:
my $stats = sql ('pays (SUM(total_rur), SUM(total_usd))'); # возвращается ссылка на хэш
А если наряду с агрегатными запрашиваются и обычные скалярные выражения, то по списку последних выполняется GROUP BY и функция sql возвращает список хэшей, аналогично sql_select_all, либо, при указании callback-функции, производит итерацию по выборке, как sql_select_loop:
sql ('users_workgroups(COUNT(*) AS cnt)' => [], 'workgroups(label)', sub {...});
В этом случае по умолчанию ORDER BY совпадает с GROUP BY. При необходимости его можно переопределить псевдофильтром ORDER.
Выражения с агрегатными функциями можно использовать не только как компоненты выборки, но и в фильтрах. Такие фильтры порождают условия не в WHERE-, а в HAVING-части запроса. Например, вот как можно получить список имён рабочих групп с количеством участников более 10:
my $big_groups = sql ('users_workgroups(COUNT(id))' => [
['COUNT(id) >' => 10],
], 'workgroups(label)');
Для сравнения приведём эквивалентный вызов sql_select_all:
my $big_groups = sql_select_all (q {
SELECT
COUNT(users_workgroups.id) AS count_users_workgroups_id
, workgroups.label
FROM
users_workgroups
LEFT JOIN workgroups ON users_workgroups.id_workgroup = workgroups.id
WHERE
users_workgroups.fake = 0
GROUP BY
workgroups.label
HAVING
COUNT(users_workgroups.id) > ?
ORDER BY
workgroups.label
}, 10);
В заключение приведём все описанные варианты вызова функции sql и соответствующие форматы результатов.
Если для корневой таблицы указан список выбора из единственного элемента, отличного от '*', а также фильтр по полю id или LIMIT => 1 то возвращается скаляр (аналогично sql_select_scalar). То же происходит в случае, когда выбирается лишь одно поле и оно является агрегатным (COUNT, SUM и т. п.)
Если для корневой таблицы указан список выбора из единственного неагрегатного элемента, отличного от '*', то возвращается ссылка на скаляр: список id через запятую (аналогично sql_select_ids).
Если указан фильтр по полю id или LIMIT => 1, либо если все поля выборки являются агрегатами, то возвращается ссылка на хэш: единственную запись (аналогично sql_select_hash).
Если в качестве последнего параметра передана ссылка на процедуру, то она вызывается для каждой записи в выборке, аналогично sql_select_loop.
Если указан LIMIT с 2 аргументами (старт и порция), то возвращается ссылка на список (выборка) и дополнительно скаляр: объём выборки без ограничителя (аналогично sql_select_all_cnt).
В остальных случаях возвращается ссылка на список (аналогично sql_select_all).
И последнее: опция $preconf -> {core_debug_sql} включает вывод отладочной информации sql в STDERR. Туда печатаются аргументы вызова и сгенерированные по ним SQL-запросы со списками параметров.