День добрый.
Задача: надо придумать универсальный механизм, который будет строить SQL запросы, на вход которому будут подаваться поля и критерии отбора объектов. Должна поддерживаться вложенность полей и критериев.
Пример.
Для простоты пусть будет одна древовидная таблица TestTable(Id, ParentId, Name).
Надо выбрать все поля всех записей, у которых Name = 'Раз', но при этом есть дочерние записи у которых Name = 'Два' (тоже все поля), у которых есть дочерние, к которых Name = 'Три' (но тут, например, выбрать только поле Name и Id).
Механизм должен быть универсальным и выполняться одним запросом.
Пока пришло в голову использовать запрос вида:
select t1.*,
(select t4.*,
(select t5.Id,
t5.Name
from TestTable t5
where t5.Id = t4.Id
for xml auto, type)
from TestTable t4
where t4.Id = t1.ParentId
for xml auto, type)
from TestTable t1
where t1.Name = 'Раз'
and exists (select t2.Id
from TestTable t2
where t2.Id = t1.ParentId
and t2.Name = 'Два'
and exists (select t3.Id
from TestTable t3
where t3.Id = t2.Id
and t3.Name = 'Три'))
for xml auto, type
Интересует кривизна (в SQL я не силен) и производительность данного варианта (при грамотно настроенных индексах).
Аноним 95 wrote:
> Задача: надо придумать универсальный механизм, который будет строить SQL > запросы, на вход которому будут подаваться поля и критерии отбора > объектов. Должна поддерживаться вложенность полей и критериев.
Универсальные запросы -- это плохие запросы.
В общем запросы либо НЕ универсальные, либо НЕ работают.
А>Задача: надо придумать универсальный механизм, который будет строить SQL запросы А>Для простоты пусть будет одна древовидная таблица TestTable(Id, ParentId, Name).
мехнизмы с универсальностью в рамках одной системы приходилось делать, но чтобы прям ваааапще универсально — как-то нереально.
тот же пример, который вы взяли для простоты — при таких запросах (когда надо проверять дочерние от дочерних) я не стал бы использовать такой способ хранения дерева (лучше правое-левое плечо или транзитивное замыкание), следовательно универсальный механизм по отношению к моим древовидным структурам уже будет должен строить другой запрос.
ту универсальность, которую я себе устраивал, заключалась в следующем (шаблон Builder по-моему): разбиваем БД на сущности (в самом простом случае одна сущность — одна таблица, но иногда и посложнее), описываем в виде классов, которые знают специфику своих сущностей (в каких таблицах хранятся и как эти таблицы между собой компонуются, какие хинты когда применять). Сверху ряд классов для группировки сущностей и для группировок групп. Каждый класс умеет: строить свою часть sql-запроса и анализировать критерии для выяснения какое поле нужно "выталкивать" наверх. Далее в нужном месте начинаем склеивать эти классы вместе, передавать им критерии и получать от них sql-запросы.
на деле выходит, что написать всё это уже долго, а писать запросы в терминах своих сущностей не всегда удобно и понятно (sql как-то ближе всё-таки) — но это смотря как писать, если толково подойти, то выйдет проще, наверное.
но у меня на входе было порядка десяти критериев, а у каждого их них в среднем по четыре значения — итого 400 больших, сильно и не очень различающихся запросов. Универсальность показалась выгодной, вот и применил. Но в каждом конкретном случае надо смотреть отдельно — иногда, думаю, проще сгруппировать сходные запросы и уже в их рамках управлять фильтрами и колонками.
Вы пытаетесь создать велосипед.
Может быть стоит воспользовать какими-либо готовыми решениями генерирующими SQL на выходе? Ну к примеру в случае если пишете на .NET, то типа Linq2Sql/SubSonic/NHibernate и т.д.?
По какими причинам вы хотите сделать свой велосипед?
