Для чего используется фильтрация в базах данных

SQL-Урок 4. Фильтрация данных (WHERE)

В большинстве случаев необходимо получать не все записи, а только те, которые соответствуют определенным критериям. Поэтому для осуществления фильтрации выборки в SQL есть специальный оператор WHERE.

1. Простое фильтрование оператором WHERE.

Давайте из нашей таблицы, например, отберем записи, относящиеся только к определенному товару. Для этого мы укажем дополнительный параметр отбора, который будет фильтровать значение по колонке Product.

Пример запроса для отбора текстовых значений:

SELECT * FROM Sumproduct WHERE Product = ‘Bikes’

Как видим, условие отбора взято в одинарные кавычки, что является обязательным при фильтровании текстовых значений. При фильтровании числовых значений кавычки не нужны.

Пример запроса для отбора числовых значений:

SELECT * FROM Sumproduct WHERE Amount > 40000 ORDER BY Amount

В таблице ниже указан перечень условных операторов, поддерживаемых SQL:

2. Фильтрация по диапазону значений (BETWEEN).

SELECT * FROM Sumproduct WHERE Amount BETWEEN 1000 AND 2000

Очередность сортировки будет зависеть от порядка расположения полей в запросе. То есть, в нашем случае сначала данные будут посортированы по колонке Amount, а затем по City.

3. Выборка пустых записей (IS NULL).

В SQL существует специальный оператор для выборки пустых записей (NULL). Пустой записью считается любая ячейка в таблице, в которую не введены какие-либо символы. Если в ячейку введен 0 или пробел, то считается, что поле заполнено.

SELECT * FROM Sumproduct WHERE AmountIS NULL

В примере выше, мы нарочно удалили два значения в поле Amount, чтобы продемонстрировать работу оператора NULL.

4. Расширенное фильтрации (AND, OR).

Язык SQL не ограничивается фильтрацией по одному условию, для собственных целей вы можете использовать достаточно сложные конструкции для выборки данных одновременно по многим критериям. Для этого в SQL есть дополнительные операторы, которые расширяют возможности оператора WHERE. Такими операторами являются: AND, OR, IN, NOT. Приведем несколько примеров работы данных операторов.

SELECT * FROM Sumproduct WHERE Amount > 40000 AND City = ‘Toronto’

SELECT * FROM Sumproduct WHERE Month= ‘April’ OR Month= ‘March’

Давайте объединим операторы AND и OR. Для этого сделаем выборку велосипедов (Bikes) и коньков (Skates), которые были проданы в марте (March).

SELECT * FROM Sumproduct WHERE Product = ‘Bikes’ OR Product = ‘Skates’ AND Month= ‘March’

Видим, что в нашу выборку попало за много значений (кроме марта (March), также январь (January), февраль (February) и апрель (April)). В чем же причина? А в том, что SQL имеет приоритеты выполнения команд. То есть оператор AND имеет более высокий приоритет, чем оператор OR, поэтому сначала были отобраны записи с коньками, которие проданные в марте, а потом все записи, касающиеся велосипедов.

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

SELECT * FROM Sumproduct WHERE (Product = ‘Bikes’ OR Product = ‘Skates’) AND Month= ‘March’

5. Расширенная фильтрация (оператор IN).

SELECT * FROM Sumproduct WHERE ID IN (4, 12, 58, 67)

Оператор IN выполняет ту же функцию, что и OR, однако имеет ряд преимуществ:

6. Расширенная фильтрация (оператор NOT).

SELECT * FROM Sumproduct WHERE NOT City IN (‘Toronto’, ‘Montreal’)

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

Источник

Фильтры данных на ASP.NET MVC и Entity Framework

Очень часто в различных веб-приложениях мы работаем с данными, выбранными из таблиц БД. И нередко необходимо предоставлять пользователю возможность фильтровать эти данные. Можно, конечно, для каждого случая собирать данные с формы в ручную и в ручную создавать соответствующий запрос под них. Но что если у нас 20 разных страниц, представляющих те, или иные данные? Обрабатывать в ручную все долго и не удобно, а поддерживать еще хуже. Моя версия решения данной проблемы на ASP.NET MVC + Entity Framework под катом.

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

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

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

Имея модель фильтра мы можем выбрать из БД данные, удовлетворяющие значениям, хранящимся в этой модель. Сделать это можно вот так

Ну и конечно же код вьюхи, которая будет отображать форму, которую пользователю необходимо заполнить, что бы отфильтровать данные.

Filters этот то же хранилище фильтров, которое использовали при выборке из БД. Лично я его получаю с помощью Unity.

Ну и все, больше нам ничего не понадобится. Пользователь будет видеть перед собой примерно вот такую форму

И набор данных, который соответствует этой форме.

Теперь перейдем к описанию всего того, что обслуживает вышеописанный код. Начнем мы с модели данных для фильтров. По скольку для каждого представления данных на сайте фильтры могут быть разные и соответственно разные модели, необходимо определить ее интерфейс, который будем реализовывать. Модель для фильтра должна уметь генерировать expression для выборки из коллекций данных entity framework и должна уметь устанавливать свое начальное состояние, по этому интерфейс, а вернее абстрактный класс, получился следующий:

Использую здесь именно абстрактный класс потому, что хотелось всегда держать контэйнер Unity под рукой. По этому свойство IocContainer можно смело убирать и превращать эти два класса в обычные интерфейсы.

Теперь, когда у нас есть модель, нам нужно ее где то хранить. Рассмотрим теперь интерфейс необходимого нам хранилища.

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

Вот базовая реализация интерфейса IFilterFactory.

Хранить объект класса в DefaultFilterFactory приходится в рамках сессии, что бы сохранять выбранные пользователем значения. Для этого я использую Unity с дописанным lifemanager-ом для сессий ASP.NET MVC, Вы можете использовать любой другой DI-фрэймворк или же работать с объектом самостоятельно. Так же можно написать другую реализацию интерфейса IFilterFactory, которая будет использовать для хранения xml например или же БД, тут фантазия может быть безгранична…

Дальше необходимо каким то образом собрать модель из запроса пользователя и поместить ее в хранилище. Для этого будем использовать специальный сборщик модели (тут каюсь, не додумал, грамотней было бы его унаследовать от IModelBinder и использовать как любой другой сборщик моделей в MVC)

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

(ExpandController — расширенный базовый Controller, FlashMessanger — надстройка над TempData, не даю пояснений на этот счет, так как это совсем другая история)

Как видно из кода, в контроллере два действия: одно занимается обработкой значений, второе их сбросом. Оба, после окончания своей миссии, возвращают пользователя на ту страницу, от куда пришел запрос. И на ней пользователь уже сможет увидеть отфильтрованный набор интересующих данных.

Написав всю бизнес-логику задачи, теперь можно заняться отображением всего этого безобразия для пользователя. По скольку пока эти фильтры я применял всего в одном проекте, то html мне генерирует хелпер, и соответственно гибкости разметки нет почти никакой. Если у Вас есть идеи как можно решить эту проблему, буду рад их слышать в комментариях.

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

Пожалуй на этом все. Теперь вернемся к тем минусам, о которых я говорил в начале:

1) Если пользователь откроет страницу, затем оставит ее на время, достаточное для того, что бы истекло время сессии, после чего заполнит форму фильтров и отправит ее, то его ждет разочарование. Система не сможет найти тип фильтра по его guid. Как решить проблему, пока не придумал, но активно размышляю на этот счет

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

3) Для метода Reset класса FilterModel можно написать реализацию по умолчанию, в которой все свойства, помеченные InputTypeAttribute будут установлены в нулевые значения.

4) Я не уверен в абсолютной правильности архитектуры всего этого.

Большое Вам спасибо, что дочитали мою статью до конца! Буду крайне признателен Всем, кто оставит свое мнение и замечания.

UPDATE
Как верно подметили в комментариях

В этом случае выражение фильтра будет трансформироваться в SQL запрос.

Источник

Использование выражений для фильтрации данных из БД

Статья основана на ответе в StackOverflow. Начну с описания проблемы, с которой я столкнулся. Есть несколько сущностей в базе данных, которые нужно отображать в виде таблиц на UI. Для доступа к базе данных используется Entity Framework. Для этих таблиц есть фильтры, по полям этих сущностей. Нужно написать код для фильтрации сущностей по параметрам.

Например, есть 2 сущности User и Product.

Допустим, что нам нужно фильтровать пользователей по имени, а продукты по названию. Мы пишем методы для фильтрации каждой сущности.

Сразу же замечаем, что эти два метода почти идентичны и отличаются только свойством сущности, по которому фильтруются данные. Если у нас есть десятки сущностей, в каждой из которых есть десятки полей, по которым нужна фильтрация, то это приводит к некоторым трудностям: сложность в поддержке кода, бездумное копирование и как следствие медленная разработка и высокая вероятность ошибок. Перефразируя Фаулера, начинает попахивать. Хотелось бы вместо дублирования кода написать что-то более универсальное. Наример:

К сожалению, если мы попытаемся выполнить фильтрацию

то словим ошибку «Test method ExpressionTests.ExpressionTest.TestFilter threw exception:
System.NotSupportedException: The LINQ expression node type ‘Invoke’ is not supported in LINQ to Entities.». Потому что

Выражения

Попробуем разобраться что пошло не так.

Метод Where принимает параметр типа Expression >. Т.е. Linq работает не с делегатами, а с деревьями выражений, по которым строит SQL запросы.

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

При отладке можно увидеть структуру этого выражения (красным отмечены ключевые свойства)

Получается примерно такое дерево

Дело в том, что когда мы передаём делегат как параметр, то формируется другое дерево, в котором вместо обращения к свойству сущности происходит вызов метода Invoke у параметра(делегата). Когда Linq пытается построить SQL запрос по этому дереву, он не знает как интерпретировать метод Invoke и выбрасывает исключение NotSupportedException.

Таким образом нашей задачей является заменить обращение к свойству сущности (часть дерева, которая выделена красным) на выражение, передаваемое через параметр. Попробуем:

Теперь мы видим ошибку «Method name expected» уже на этапе компиляции.

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

The Visitor

После недолгого гугления я обнаружил решение похожей проблемы на StackOverflow.

Для работы с выражениями есть специальный класс ExpressionVisitor, который использует паттерн Visitor. Его суть в том, что он обходит все узлы дерева выражения в порядке разбора синтаксического дерева и позволяет их модифицировать либо вернуть вместо них другой узел. Если ни сам узел ни его дочерние узлы не поменялись, то возвращается исходное выражение.

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

Теперь мы можем вставить одно выражение в другое

Осталось написать визитор, который заменит в дереве выражений вызов метода Call на его параметр:

Теперь мы можем подменить наш маркер.

В отладке видим, что выражение получилось не совсем такое как мы ожидали. Фильтр всё еще содержит метод Invoke.

Дело в том, что выражение parameterGetter и выражение finalFilter используют два разных аргумента. Поэтому нам нужно подменить аргумент в parameterGetter на аргумент из finalFilter. Для этого напишем еще один визитор.

В итоге получаем вот такой код:

Теперь всё работает так как надо и мы, наконец-то, можем написать наш метод фильтрации

Послесловие

Подход с подстановкой выражений можно использовать не только для фильтрации, но и для сортировок и вообще для любых запросов к БД.

Также этот метод позволяет хранить выражения вместе с бизнес логикой отдельно от самих запросов к базе.

Источник