Необходим шаблон для динамического поиска по нескольким таблицам sql
Я ищу шаблон для выполнения динамического поиска по нескольким таблицам.
У меня нет контроля над устаревшей (и плохо спроектированной) структурой таблиц базы данных.
Рассмотрим сценарий, аналогичный поиску резюме, когда пользователь может захотеть выполнить поиск по любым данным в резюме и получить обратно список резюме, которое соответствует их критериям поиска. Любое поле можно искать в любое время и в сочетании с одним или несколькими другими полями.
Фактический запрос sql создается динамически в зависимости от того, в каких полях выполняется поиск. Большинство решений, которые я нашел, связаны со сложными блоками if, но я не могу не думать, что должно быть более элегантное решение, поскольку к настоящему времени это уже решенная проблема.
Да, поэтому я начал динамическое построение sql в коде. Кажется ужасным. Если я действительно попытаюсь поддержать запрошенную возможность запроса любой комбинации любого поля в любой таблице, это будет один МАССИВНЫЙ набор операторов if. дрожь
Думаю, я читал, что COALESCE работает только в том случае, если ваши данные не содержат NULL. Это верно? Если так, не пойдем, так как у меня повсюду значения NULL.
Ответы 3
Насколько я понимаю (и я также являюсь тем, кто писал против ужасной устаревшей базы данных), не существует такой вещи, как динамические предложения WHERE. Это НЕ было решено.
Лично я предпочитаю генерировать динамический поиск в коде. Делает тестирование удобным. Обратите внимание: когда вы создаете свои sql-запросы в коде, не объединяйте вводимые пользователем данные. Используйте свои @variables!
Единственная альтернатива - использовать оператор COALESCE. Допустим, у вас есть следующая таблица:
Users
-----------
Name nvarchar(20)
Nickname nvarchar(10)
и вы хотите выполнить поиск по имени или псевдониму. Следующий запрос сделает это:
SELECT Name, Nickname
FROM Users
WHERE
Name = COALESCE(@name, Name) AND
Nickname = COALESCE(@nick, Nickname)
Если вы не хотите что-то искать, просто передайте ноль. Например, передача «brian» для @name и null для @nick приводит к оценке следующего запроса:
SELECT Name, Nickname
FROM Users
WHERE
Name = 'brian' AND
Nickname = Nickname
Оператор coalesce превращает null в оценку идентичности, которая всегда истинна и не влияет на предложение where.
Поиск и нормализация могут противоречить друг другу. Так что, вероятно, первым делом нужно получить какое-то «представление», которое показывает все поля, которые можно искать, как одну строку с одним ключом, дающим вам резюме. тогда вы можете бросить что-то вроде Lucene перед этим, чтобы получить полный текстовый индекс этих строк, способ, который работает, вы запрашиваете у него «x» в этом представлении, и он возвращает вам ключ. Это отличное решение, рекомендованное самим Джоэлом в подкасте в течение первых 2 месяцев IIRC.
Вам нужно что-то вроде СфинксПоиск (для MySQL) или Apache Lucene.
Как вы сказали в своем примере, давайте представим себе резюме, которое будет состоять из нескольких полей:
- Элемент списка
- Имя,
- Адрес,
- Образование (это может быть отдельный стол) или
- Опыт работы (может вырасти до отдельной таблицы, где каждая строка представляет предыдущую работу)
Таким образом, поиск слова во всех этих полях с WHERE быстро превращается в очень длинный запрос с несколькими JOINS.
Вместо этого вы можете изменить свою систему ссылок и думать о целом резюме как о едином документе, и вы просто хотите искать в указанном документе.
Вот где работают такие инструменты, как Sphinx Search. Они создают ПОЛНЫЙ ТЕКСТОВЫЙ индекс вашего «документа», а затем вы можете запросить sphinx, и он вернет вам, где в базе данных была найдена эта запись.
Действительно хорошие результаты поиска.
Не беспокойтесь о том, что эти инструменты не являются частью вашей СУБД, это избавит вас от многих головных болей, связанных с использованием подходящей модели «Документы» вместо неправильной модели «ТАБЛИЦЫ» для этого приложения.