Здравствуйте,
Подскажите, пожалуйста, как решаются задачи, когда в бизнес-приложении пользователю необходимо показать выборку зависящую от множества фильтров. Например пользователь хочет выбрать все авиа-заказы заказы по определенному производителю, в определенный период времени и т.д. Т.е. на форме много всяких чекбоксов, комбобоксов и т.д. Самых плохой вариант тут, наверное, составлять sql-запрос самому, далее можно использовать ХП с dynamic sql, далее Query Object, ну и ORM.
Может быть кто-нибудь писал Query Object для MySQL или может посоветуете наиболее элегантные, красивые и в то же время простые пути решения данной задачи?

Здравствуйте, MozgC, Вы писали:

MC>Здравствуйте,
MC> Подскажите, пожалуйста, как решаются задачи, когда в бизнес-приложении пользователю необходимо показать выборку зависящую от множества фильтров. Например пользователь хочет выбрать все авиа-заказы заказы по определенному производителю, в определенный период времени и т.д. Т.е. на форме много всяких чекбоксов, комбобоксов и т.д. Самых плохой вариант тут, наверное, составлять sql-запрос самому, далее можно использовать ХП с dynamic sql, далее Query Object, ну и ORM.
MC>Может быть кто-нибудь писал Query Object для MySQL или может посоветуете наиболее элегантные, красивые и в то же время простые пути решения данной задачи?

делаю как раз такое, для финансового приложения, ORM (Хибернейт) прикручивается достаточно легко, с фильтрами, сортировками и т.д. Знакомый из одной крупной компании сказал, что у них подобный мехнанизм на хибернейте переваривает миллионы записей вполне достойно.

Какой объект на клиенте использовать — это пофиг, не пофиг то, какие индексы в базе данных будут использоваться.

Скажем, при наличии фильтра по десятку параметров и потом сортировки по 11ому параметру вполне реальна картина, когда сортировка будет выполняться не по индексу. Если в результате затронуто BLOBовское поле, то такая сортировка под большой нагрузкой (когда таких сортировок бежит много в параллель) будет либо омерзительно тормозить, либо повалит mysqld (если там начнут обламываться malloc из-за исчерпания памяти под sort buffers).

В общем случае на этом движке базы данных, при больших нагрузках и больших таблицах с BLOBами задача крайне тяжела. Имеет смысл вообще выделить BLOBы в отдельную таблицу из 2 столбов (PrimaryKey, BLOB), и к ней обращаться только для доставания BLOBов только по равенству ключа.

При совсем больших таблицах можно и без BLOBов такую радость получить. Возможно, придется делать index intersection на коленке.

Здравствуйте, Maxim S. Shatskih, Вы писали:

MSS>Какой объект на клиенте использовать — это пофиг, не пофиг то, какие индексы в базе данных будут использоваться.

MSS>Скажем, при наличии фильтра по десятку параметров и потом сортировки по 11ому параметру вполне реальна картина, когда сортировка будет выполняться не по индексу. Если в результате затронуто BLOBовское поле, то такая сортировка под большой нагрузкой (когда таких сортировок бежит много в параллель) будет либо омерзительно тормозить, либо повалит mysqld (если там начнут обламываться malloc из-за исчерпания памяти под sort buffers).

MSS>В общем случае на этом движке базы данных, при больших нагрузках и больших таблицах с BLOBами задача крайне тяжела. Имеет смысл вообще выделить BLOBы в отдельную таблицу из 2 столбов (PrimaryKey, BLOB), и к ней обращаться только для доставания BLOBов только по равенству ключа.

MSS>При совсем больших таблицах можно и без BLOBов такую радость получить. Возможно, придется делать index intersection на коленке.

Дело в том, что проблема не в производительности, с производительностью в данном случае все хорошо. Проблема в том, как это красиво написать. Хотелось бы спросить у более опытных людей, как они решают такие задачи. Потому что у меня сейчас как раз самый плохой вариант — SQL запрос собирается в зависимости от того что выбрал пользователь прямо в коде формы. Конечно можно это вынести из кода формы куда-нибудь в отдельный слой, и вызывать типа BlaBla.GetOrdersWithFilter(CustomerID, (int)cbSupplier.SelectedValue, chkDateFilter.Checked ? dtDateFilter.Value : null ... и т.д.), а уже внутри GetOrdersWithFilter собирать SQL-запрос, но намного лучше от этого картина не станет. ORM я не использую, Query Object для mysql у меня нет, динамически собирать запрос внутри ХП, тоже как-то не очень хочется...

Направьте на путь истинный?

MC>Направьте на путь истинный?

А тут любой путь есть истинный делай в соответствиями со своими личными понятиями о красивом, и работать будет одинаково.

Единственное что — очень плохо применять суммирование строк для SQL запроса, если в сумму входят те строки, что получены от юзера или из внешнего мира. Это может создать дыру в безопасности. Для этого лучше использовать параметризованные запросы.

Впрочем, если речь о PHP4, то там их просто для MySQL не существует, и вопрос отпадает

Ой, я даже не указал платформу и тип приложения — .NET, desktop

Здравствуйте, Maxim S. Shatskih, Вы писали:

MSS>А тут любой путь есть истинный делай в соответствиями со своими личными понятиями о красивом, и работать будет одинаково.

Любой путь не истинный. Есть хорошая архитектура, есть плохая архитектура. Понятия конечно субъективные и относительные. Но думаю большинство поймет что я имею в виду. Надо стремиться к более правильной архитектуре. Под правильной архитектурой лично я понимаю такую, которую легко понимать, расширять, повторно использовать.
Идем дальше. Например я решил работу с БД вынести в отдельный слой и использовать ХП. Я следую этой своей архитектуре, пока вот не наталкиваюсь на задачу с большим фильтром, где мне приходится все-таки в слое представления прямо сооружать SQL запрос. Сейчас вот случайно как раз наткнулся у Нильссона в книге на такую задачу, он там предлагает лучшим решением Query Object.

Я вот только не пойму, почему так мало ответов на мой пост? Складывается впечатление, что только я пишу бизнес-приложения и только у меня встречаются большие фильтры, а все остальные обходятся запросами вида SELECT * FROM orders WHERE CustomerID = 42; ?

MSS>Единственное что — очень плохо применять суммирование строк для SQL запроса, если в сумму входят те строки, что получены от юзера или из внешнего мира. Это может создать дыру в безопасности. Для этого лучше использовать параметризованные запросы.

Ну у меня не входят строки полученные от пользователя или из внешнего мира, но даже если входили бы, кроме как суммированием тут не обойтись, потому что я же не знаю какие фильтры выбрал пользователь, и заранее не могу приготовить параметризированный запрос, типа SELECT * FROM orders WHERE CustomerID = ?CustomerID_; (mysql), поэтому только лишь конкатенация.

По поводу ORM — честно скажу, не пробовал, но что-то внутри мне подсказывает, что на данном этапе развития это еще сыро, малопроизводительно, малодокументировано и т.д. Просто кажется что будут возникать проблемы на ровном месте типа "а вот тут блин так медленно работает и такой запрос убогий генерируется" или "а как сделать это?", чтобы обычно бывает с относительно мало развитыми технологиями/продуктами.

Все написанное выше лишь мое дилетантское имхо Прошу камнями не кидать, а если не согласны — объяснить, вразумить. Я весь открыт для обучения

Здравствуйте, MozgC, Вы писали:

MC>Ну у меня не входят строки полученные от пользователя или из внешнего мира, но даже если входили бы, кроме как суммированием тут не обойтись, потому что я же не знаю какие фильтры выбрал пользователь, и заранее не могу приготовить параметризированный запрос, типа SELECT * FROM orders WHERE CustomerID = ?CustomerID_; (mysql), поэтому только лишь конкатенация.

Не понял проблемы.. у меня каждое свойство, по которому делается фильтрация, соответствует (не обязательно =) колонке в базе, причем можно использовать иерархические свойства, например: company.location.country.name, где каждое свойство это бин (обычный класс с аксессорами и мутаторами), эти классы со своими ствойствами могут быть замаплены на разные таблицы. Я передаю в DAO объект, в котором список свойств, по которым производится фильтрация и сортировка, само описание фильтров и сортировки, + пейджинг. В DAO из этого объекта составляю запрос (именно с параметрами) и... собственно все: Hibernate сам определяет, какое свойство какому полю и в какой таблице соответствует. Реализация серверной части заняло порядка двух дней по 4 часа, вместе с тестированием.

MC>По поводу ORM — честно скажу, не пробовал, но что-то внутри мне подсказывает, что на данном этапе развития это еще сыро, малопроизводительно, малодокументировано и т.д. Просто кажется что будут возникать проблемы на ровном месте типа "а вот тут блин так медленно работает и такой запрос убогий генерируется" или "а как сделать это?", чтобы обычно бывает с относительно мало развитыми технологиями/продуктами.

Я не историк, но Hibernate развивается уже года 4, полагаю. NHibernate, конечно, помоложе, но так ведь и он не с нуля писался. Так что про мало развитые технологии вы определенно поторопились. В любом случае, никто не запрещает напрямую слать запросы к базе, вопрос всегда ли это нужно.

MC>Идем дальше. Например я решил работу с БД вынести в отдельный слой и использовать ХП.

Ага, и усложнение порта с MySQL на любую другую базу.

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

Дерьмо останется дерьмом, какой фасад к нему не приляпай.

Здравствуйте, Trean, Вы писали:

MC>>Ну у меня не входят строки полученные от пользователя или из внешнего мира, но даже если входили бы, кроме как суммированием тут не обойтись, потому что я же не знаю какие фильтры выбрал пользователь, и заранее не могу приготовить параметризированный запрос, типа SELECT * FROM orders WHERE CustomerID = ?CustomerID_; (mysql), поэтому только лишь конкатенация.

T>Не понял проблемы..

Проблемы вы не поняли наверное потому что используете ORM. Как раз в данном случае он помогает.

T>Я не историк, но Hibernate развивается уже года 4, полагаю. NHibernate, конечно, помоложе, но так ведь и он не с нуля писался. Так что про мало развитые технологии вы определенно поторопились. В любом случае, никто не запрещает напрямую слать запросы к базе, вопрос всегда ли это нужно.

Возможно я не прав, но время от времени читаю мнения про него, обсуждения, все еще достаточная часть отзывов — негативные или по крайней мере не особо позитивные

Здравствуйте, Maxim S. Shatskih, Вы писали:

MC>>Идем дальше. Например я решил работу с БД вынести в отдельный слой и использовать ХП.

MSS>Ага, и усложнение порта с MySQL на любую другую базу.

Порт с MySQL на любую другую базу в любом случае будет очень болезненным, независимо от того, переносим мы ХП или текст SQL-запросов в программе.

MSS>Заведение кучи промежуточных слоев просто чтобы послать запрос в базу на деле совсем ничему не помогает. Особенно если в этих слоях таки будет использоваться конкатенация юзеровских значений для построения запросов.
MSS>Дерьмо останется дерьмом, какой фасад к нему не приляпай.

Не чтобы просто послать запрос в базу, иерархия такая что методы в DAL выполняют простейшие операции с БД, ну в основном просто вызовы ХП. И в этом есть смысл: в коде формы достаточно просто написать типа cbCustomers.DataSource = DALCustomer.GetAllCustomers(_conn); а не создавать SqlCommand и т.д., повышается повторное использование кода и легкость его последующего сопровождения. Далее в слове бизнес-правил находятся более высокоуровевые методы, которые внутри себя могут вызывать несколько методов из DAL, например операция отмены счета. Так что не считаю это кучей ненужных промежуточных слоев. Или мы говорим о разных вещах? По поводу конкатенации юзеровских значений — этого нет, единственное где сейчас столкнулся с этим это формы с большим набором фильтров. Вот тут получается некрасиво...

Здравствуйте, MozgC, Вы писали:

T>>Не понял проблемы..

MC>Проблемы вы не поняли наверное потому что используете ORM. Как раз в данном случае он помогает.

Почему-то вспоминается поговорка про ежиков и кактусы

T>>Я не историк, но Hibernate развивается уже года 4, полагаю. NHibernate, конечно, помоложе, но так ведь и он не с нуля писался. Так что про мало развитые технологии вы определенно поторопились. В любом случае, никто не запрещает напрямую слать запросы к базе, вопрос всегда ли это нужно.

MC>Возможно я не прав, но время от времени читаю мнения про него, обсуждения, все еще достаточная часть отзывов — негативные или по крайней мере не особо позитивные

Ваше право, но предупреждаю сразу, вам придется написать тоже самое, но самому и за гораздо больший срок (на порядок). От маппинга полей фильтрации на колонки таблиц(ы) вам ни куда не деться. В любом случае удачи, отпишитесь по результату

Здравствуйте, Trean, Вы писали:

T>Почему-то вспоминается поговорка про ежиков и кактусы
Все может быть =)

T>Ваше право, но предупреждаю сразу, вам придется написать тоже самое, но самому и за гораздо больший срок (на порядок). От маппинга полей фильтрации на колонки таблиц(ы) вам ни куда не деться. В любом случае удачи, отпишитесь по результату

Опять же, все может быть. В любом случае человек так устроен, что ему сложно решить что он не прав или что что что он считает правильным на самом деле неправильное или не совсем правильное %) И зачастую нужно ко всему приходить самому, возможно я действительно через какое-то время приду к ORM и все будет хорошо, по крайней мере раньше например я не понимал особого смысла в расслоении вообще, и думал что это заморочки, а сейчас знаю что был не прав. Время покажет. Скажите куда отписаться по результату

Здравствуйте, MozgC, Вы писали:

MC>Опять же, все может быть. В любом случае человек так устроен, что ему сложно решить что он не прав или что что что он считает правильным на самом деле неправильное или не совсем правильное %) И зачастую нужно ко всему приходить самому, возможно я действительно через какое-то время приду к ORM и все будет хорошо, по крайней мере раньше например я не понимал особого смысла в расслоении вообще, и думал что это заморочки, а сейчас знаю что был не прав. Время покажет. Скажите куда отписаться по результату

Прямо сюда в ветку и отпишитесь, думаю другим людям ваш опыт пригодится.

MC>Порт с MySQL на любую другую базу в любом случае будет очень болезненным, независимо от того, переносим мы ХП или текст SQL-запросов в программе.

SQL запросы в программе можно написать портабельными на любой SQL, за исключением функции "текущая дата и время", которая действительно везде по-разному сделана.

На самом деле главное, зачем нужен DAL — это дать возможность рефакторить базу самой малой кровью, с рефакторингом только DAL, а не приложения в целом. А вовсе не за тем, чтобы спрятать ото всех dbConn и язык SQL по религиозным причинам.

Исходя из этой нехитрой истины, становится понятно, что в DAL надо написать класс ComplexFormQuery, и работать с ним примерно так:

q = new DAL.ComplexFormQuery(dbConn);
q.SetOrderBy(DAL.ComplexFormQuery.orderByDate);
q.SetFilter(DAL.ComplexFormQuery.filterByName, strName);
dataObject = q.Execute();

Что-то вроде. Гиморно? да. Но иначе вообще вся суть DAL пропадает.

Здравствуйте, Maxim S. Shatskih, Вы писали:

MC>>Порт с MySQL на любую другую базу в любом случае будет очень болезненным, независимо от того, переносим мы ХП или текст SQL-запросов в программе.

MSS>SQL запросы в программе можно написать портабельными на любой SQL, за исключением функции "текущая дата и время", которая действительно везде по-разному сделана.

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

MSS>На самом деле главное, зачем нужен DAL — это дать возможность рефакторить базу самой малой кровью, с рефакторингом только DAL, а не приложения в целом. А вовсе не за тем, чтобы спрятать ото всех dbConn и язык SQL по религиозным причинам.

DAL создается еще для того чтобы избежать дублирования кода и структурировать проект. Структурирование облегчает понимание и управление.

MSS>Исходя из этой нехитрой истины, становится понятно, что в DAL надо написать класс ComplexFormQuery, и работать с ним примерно так:

MSS>q = new DAL.ComplexFormQuery(dbConn);
MSS>q.SetOrderBy(DAL.ComplexFormQuery.orderByDate);
MSS>q.SetFilter(DAL.ComplexFormQuery.filterByName, strName);
MSS>dataObject = q.Execute();

MSS>Что-то вроде. Гиморно? да. Но иначе вообще вся суть DAL пропадает.

Т.е. возвращаемся к Query Object. Не совсем понял в каком случае вся суть DAL пропадает?

MC>Т.е. возвращаемся к Query Object. Не совсем понял в каком случае вся суть DAL пропадает?

Пропадает, если вместо такого подхода использовать явно создаваемый в самой форме SQL запрос.

Пока мы общаемся, я бы уже свой query object написал бы объем текста примерно такой.

Здравствуйте, Maxim S. Shatskih, Вы писали:

MSS>Пока мы общаемся, я бы уже свой query object написал бы объем текста примерно такой.

Так надо было бы написать для MySQL, поделились бы с народом
На самом деле мне не к спеху, на данный момент у меня в форме конкатенируется запрос, и все работает. Просто я хочу от этого избавиться.

Блин, может все-таки не заморачиваться, а просто вынести сборку SQL в DAL-метод, типа так:

В UI:

IList<Order> orders = OrderAccessor.GetOrdersWithFilter(
    chkCustomer.Checked ? (int)cbCustomer.SelectedValue : 0,
    chkManufacturer.Checked ? (int)cbManufacturer.SelectedValue : 0,
    chkDateFilter.Checked ? (DateTime?)dtDateFilter.Value : null,
    chkOrderType.Checked ? orderTypeFilter : "",
    chkSupplierOrderN.Checked ? Convert.ToInt32(txtSupplierOrderN.Text) : 0);

В DAL:

public static IList<Order> GetOrdersWithFilter(int customerID, int manufacturerID, DateTime? orderDate, string OrderTypeFilter, int supplierOrderN)
{
    string sql = "SELECT * FROM orders WHERE ";
    ...
    if(customerID != 0)
        sql += String.Format(" AND CustomerID = {0} ", customerID.ToString());
    ...
    if(orderDate != null)
        sql += String.Format(" AND OrderDate = '{0}' ", ((DateTime)orderDate).ToString("yyyy-MM-dd"));
    ...
}

и все...
Если потребуется вдруг в нескольких местах программы получать заказы по разным фильтрам, то метод GetOrderWithFilter просто должен позволять указать максимальный набор фильтров, а если они не нужны, то передавать вместо них 0 или null...

Здравствуйте, MozgC, Вы писали:

MC>

MC>public static IList<Order> GetOrdersWithFilter(int customerID, int manufacturerID, DateTime? orderDate, string OrderTypeFilter, int supplierOrderN)
MC>{
MC>    string sql = "SELECT * FROM orders WHERE ";
MC>    ...
MC>    if(customerID != 0)
MC>        sql += String.Format(" AND CustomerID = {0} ", customerID.ToString());
MC>    ...
MC>    if(orderDate != null)
MC>        sql += String.Format(" AND OrderDate = '{0}' ", ((DateTime)orderDate).ToString("yyyy-MM-dd"));
MC>    ...
MC>}
MC>

Чтобы такой ерундой не заниматься, в свое время использовал либу Sql.Net. Работала замечательно (был один небольшой косяк, но даже забыл какой, т.к. обошел легко). NHibernate не катил (хотя я сперва и хотел), т.к. было требование все упаковать в одну сборку. А эту либо я просто вкомпилил в проект и все заработало. Использовал для MS SQL Server,но MySql тоже поддерживается.