11.1 Динамические средства поиска
Наверное, чаще всего поиск, осуществляемый в базах данных, направлен на возвращение строки или строк, в которых столбец соответствует заданному значению. Запрос может быть сформулирован следующим образом: вывести всех сотрудников, у которых хобби фотография.
Для подобного поиска во многих других языках и платформах нужно составлять SQL-запросы. Active Record делает это за нас, используя динамические возможности языка Ruby.
Например, при составлении отчетов Увлеченность хобби и Популярность хобби мы уже использовали поисковые методы Active Record.
<%= Sotr.find_each do |sotr| %>
<%= Sotr.where("id = ?", params[:q]).find_each do |sotr| %>
Кроме вышеуказанных использованных функций поиска можно использовать следующие методы класса: find_by_(), find_last_by_() или find_all_by_(), в результате чего Active Record преобразует их в поисковые функции, используя оставшуюся часть имени метода как определитель выбранного столбца. Таким образом, вызов: Sotr.find_by_name("Иван") в действительности преобразуется Active Record в следующий код: Sotr.where(name: "Иван").first. Точно так же вызовы find_all_by_xxx и find_last_by_xxx подставляют, соответственно, вызовы all() и last() вместо подразумеваемого вызова first().
SQL и Active Record
Один из способов установки заменяемых элементов заключается в расстановке в коде SQL одного или нескольких вопросительных знаков. Первый вопросительный знак будет заменен вторым элементом массива, второй — третьим, и т. д. Например, можно это представить в виде:
name = params[:name]
pos = Sotr.where(["name = ? and hobby = 'photo'", name])
Можно также использовать и поименованные заменяемые элементы. Каждый такой элемент должен иметь форму :name, а значение, которое его заменит, должно быть предоставлено в виде хэша, ключи которого соответствуют именам элементов взапросе:
name = params[:name]
pay_type = params[:hobby]
pos = Sotr.where("name = :name and hobby = :hobby", hobby: hobby, name: name)
Также можно использовать оператор like:
Sotr.where("namelike ?", params[:name]+"%")
Узнав о том, как определяются условия, давайте обратим внимание на различные методы, поддерживаемые реляционным объектом класса ActiveRecord::Relation, начиная с first() и all().
Как вы уже, наверное, догадались, first() возвращает первую строку из предоставленной информации. Если предоставлены пустые данные, этот метод возвращает nil. Следуя той же логике, метод all() возвращает все строки в виде массива. Объект класса ActiveRecord::Relation также поддерживает многие методы объектов Array, такие как each() и map(). При этом он сначала в неявном виде вызывает метод all().
Важно понять, что запрос не вычисляется, пока не используется один из этих методов. Это позволяет нам изменять запрос несколькими способами путем вызова дополнительных методов до создания этого запроса. Теперь давайте посмотрим на эти методы.
order
SQL не гарантирует, что строки будут возвращены в каком-то определенном порядке, пока этот порядок не будет конкретно указан в запросе. Метод order() позволяет нам указать критерии, которые обычно добавляются после ключевых слов order by. Например: Sotr.where(name: 'Иван').order("date, shipped_atDESC"), гдеDESCуказывает на порядок убывания.
limit
Вызов метода limit() позволяет ограничивать количество возвращаемых строк. Обычно при использовании метода ограничения возникает желание определить порядок сортировки, гарантирующий соответствующие результаты. Например, следующий код вернет первых десять сотрудников с именем Иван: sotr = Sotr.where(name: 'Иван').limit(10).
select
По умолчанию ActiveRecord::Relation извлекает из используемой базы данных все имеющиеся столбцы, применяя к ней инструкцию вида select* from.... Отменить это положение можно с помощью метода select() с передачей строки, которая появится на месте символа * в инструкции select. Этот метод позволяет ограничить возвращаемые значения в том случае, если требуется вполне определенный набор данных, имеющихся в таблице.
joins
Метод joins() позволяет определить перечень дополнительных таблиц, присоединяемых к таблице, используемой по умолчанию. Он вставляет параметр в SQL-код сразу же после имени таблицы, связанной с моделью, и перед любыми условиями поиска, определяемыми первым параметром. Синтаксис присоединения зависит от типа используемой базы данных.
group
Метод group() добавляет оператор group by к SQL-коду, сгенерированному методомfind().
Получение статистики столбцов
Rails имеет возможность выполнять статистические операции над значениями столбца:
average(:param) - среднеезначение
maximum(:param) – максимум
minimum(:param) – минимум
sum(:param) – сумма
count – общий подсчет
Написание SQLкода
Каждый из рассмотренных методов вносит свой вклад в конструирование полной строки SQL-запроса. А метод find_by_sql() позволяет вашему приложению получить полный контроль над этим процессом. Он допускает использование одного аргумента, содержащего SQL-инструкцию select (или содержащего массив, состоящий из кода SQL и значений заменяемых элементов, как для метода find()), и возвращает (возможно, пустой) массив объектов модели из результирующего множества. Свойствами в этих объектах модели будет набор из столбцов, возвращенных запросом. Для возвращения всех столбцов таблицы обычно используется форма select *, но это необязательно.
sotr = Sotr.find_by_sql("select name, fam from sotrs")
Метод find_by_sql() также может использоваться для создания объектов модели, содержащих данные, извлеченные из столбцов. Если для задания имен в результирующем множестве воспользоваться SQL-синтаксисом asxxx, эти имена будут задействованы в качестве имен свойств.
Как и в случае применения условий, методу find_by_sql можно передать массив, содержащий в качестве первого элемента строку с заменяемыми элементами. Остальная часть массива должна быть либо хэшем, либо перечнем значений для подстановки.
Sotr.find_by_sql(["select * from sotrs where date> ?",params[:date]])
В прежние времена при работе с Rails специалисты часто прибегали к использованию метода find_by_sql(). Сейчас те аргументы, которые были добавлены к основному методу find(), позволяют обойтись без обращения к этому низкоуровневому методу.
Рассмотрим формирование сложных запросов с параметром, где пользователь задает необходимую характеристику для поиска.
1) Создадим первый отчет, который при вводе пользователем наименования должности, выводит на экран все ФИО сотрудников, которые занимают данную должность.
Пишем в консоли команду для создания нового контроллера:
rails g controller Search_dlzh index search
Разберем команду. Search_dlzh – это название контроллера, index и search это методы внутри контроллера, причем названия их подобраны не спроста, поскольку index и search будут в дальнейшем являться страницами.
Созданный файл контроллера представлен на рисунке 11.1. Путь: /app/controllers/search_dlzh_controller.rb
Рисунок 11.1 – Код контроллера Search_dlzh
В коде видно как Rails автоматически создал два метода для контроллера. Пока они пустые, однако, уже будут отлично работать для открытия страниц index и search.
Далее откроем файл представления /app/views/search_dlzh/ index.html.erb
Это html страничка, которая обрабатывает код Rails, об этом можно судить по формату после html “erb”. Сотрите код находящийся в файле, он генерируется по умолчанию, и пропишем свой:
<h1>Отчет поиск сотрудников по должности</h1>
<%= link_to 'Главная страница', controller: 'home_page' %>
<br><br>
<%= form_tag("search", method: "get") do %>
<%= label_tag(:q, "Введите должность:") %>
<% dlzh_array = Dlzh.all.map { |dlzh| [dlzh.d_name] } %>
<%= select_tag(:q, options_for_select(dlzh_array)) %>
<%= submit_tag("Искать") %>
<% end %>
Данный код создает форму для поиска и он похож на создаваемые ранее для отчетов популярности хобби и увлеченности хобби сотрудниками. Опишем подробнее метод формирования выпадающего списка select_tag. Строка dlzh_array = Dlzh.all.map { |dlzh| [dlzh.d_name] } формирует массив из имеющихся в БД записей таблицы dlzh, а именно поля, содержащего наименование должности – dlzh.d_name. В select_tag данный массив указывается как опции для выбора.
Сохраните файл.
Перейдем к файлу поиска. Путь /app/views/search_dlzh /search.html.erb
Снова очистите имеющийся в нем по умолчанию код и напишем свой. Для формирования запроса к базе данных можно использовать 2 метода: используя Active Record или прописать полный sql код. Рассмотрим 2 данных метода. Код метода для Active Record представлена ниже:
<h1>Отчет поиск сотрудников по должности</h1>
<%= link_to 'Главнаястраница', controller: 'home_page' %> |
<%= link_to 'Назад', controller: 'search_dlzh' %>
<br><br>
<table border="1">
<th>Код</th>
<th>Фамилия</th>
<th>Имя</th>
<th>Отчество</th>
<%= Dlzh.select("sotrs.id, sotrs.s_fam, sotrs.s_name, sotrs.s_otch, dlzhs.d_name").joins("inner join sotrs on dlzhs.id = sotrs.dlzh_id").where("dlzhs.d_name = ?", params[:q]).find_each do |sotr| %>
<tr>
<td><%= sotr.id %></td>
<td><%= sotr.s_fam %></td>
<td><%= sotr.s_name %></td>
<td><%= sotr.s_otch %></td>
</tr>
<% end %>
Примечание: Несмотря на то, что сотрудники выбираются из таблицы sotrs, в коде используется конструкция Dlzh.select, так как таблицы sotrs и dlzh имеют левое соединение.
Также, для отображения отчета на главной странице не забываем прописать в файле /app/views/home_page/index.html.erb ссылку на контроллер (рисунок 11.2).
Рисунок 11.2 – Код /home_page/index.html.erb
Запускаем сервер и открываем отчет. В результате страница index. html.erb имеет вид как на рисунке 11.3 и результат запроса имеет вид как на рисунке 11.4.
Рисунок 11.3 – Страница index
Рисунок 11.4 – Результат выполнения запроса (страница search)
Второй вариант предусматривает выполнения запроса посредством sql кода. Для этого код файла /app/views/search_dlzh/search.html.erb должен иметь вид:
<h1>Отчет поиск сотрудников по должности</h1>
<%= link_to 'Главнаястраница', controller: 'home_page' %> |
<%= link_to 'Назад', controller: 'search_dlzh' %>
<br><br>
<table border="1">
<th>Код</th>
<th>Фамилия</th>
<th>Имя</th>
<th>Отчество</th>
<%= Sotr.find_by_sql(["select sotrs.id, sotrs.s_fam, sotrs.s_name, sotrs.s_otch, dlzhs.d_name from dlzhs inner join sotrs on dlzhs.id = sotrs.dlzh_id where dlzhs.d_name = ?", params[:q]]).each do |sotr|%>
<tr>
<td><%= sotr.id %></td>
<td><%= sotr.s_fam %></td>
<td><%= sotr.s_name %></td>
<td><%= sotr.s_otch %></td>
</tr>
<% end %>
В результате чего страница search будет иметь аналогичный вид, за исключением хэш-строки, которая также содержит результат выполнения запроса.
2) Создадим второй отчет, который при вводе пользователем интересующего возраста, выводит на экран все ФИО сотрудников, которым столько же или больше целых лет.
Пишем в консоли команду для создания нового контроллера:
rails g controller Search_age index search
Далее откроем файл представления /app/views/index_age/index.html.erb и изменим его код:
<h1>Отчет поиск сотрудников по возрасту</h1>
<%= link_to 'Главная страница', controller: 'home_page' %>
<br><br>
<%= form_tag("search", method: "get") do %>
<%= label_tag(:q, "Введите возраст:") %>
<%= number_field_tag(:q, in: 1..100, step: 1) %>
<%= submit_tag("Искать") %>
<% end %>
Перейдем к файлу поиска. Путь /app/views/search_age /search.html.erb и изменим его код (метод Active Record):
<h1>Отчет поиск сотрудников по возрасту</h1>
<%= link_to 'Главная страница', controller: 'home_page' %> |
<%= link_to 'Назад', controller: 'search_age' %>
<br><br>
<table border="1">
<th>Код</th>
<th>Фамилия</th>
<th>Имя</th>
<th>Отчество</th>
<%= Sotr.select("sotrs.id, sotrs.s_fam, sotrs.s_name, sotrs.s_otch").where("ceil((current_date-sotrs.s_date)/365) >= ?", params[:q]).find_each do |sotr| %>
<tr>
<td><%= sotr.id %></td>
<td><%= sotr.s_fam %></td>
<td><%= sotr.s_name %></td>
<td><%= sotr.s_otch %></td>
</tr>
<% end %>
Поясним код: команда ceil – команда sql для postgresql, которая выполняет округление числа в меньшую сторону; current_date – команда postgresql для определения текущей даты. Таким образом, происходит наждочение разницы дат – текущей и даты рождения сотрудника, результат которой возвращается в днях. После чего полученный результат делится на 365 для перевода в годы и осуществляется округление.
Также, для отображения отчета на главной странице не забываем прописать в файле /app/views/home_page/index.html.erb ссылку на контроллер (рисунок 11.5).
Рисунок 11.5 – Код /home_page/index.html.erb
Запускаем сервер и открываем отчет. В результате страница index имеет вид как на рисунке 11.6 и результат запроса имеет вид как на рисунке 11.7.
Рисунок 11.6 – Страница index
Рисунок 11.7 – Результат выполнения запроса (страница search)
Второй вариант предусматривает выполнения запроса посредством sql кода. Для этого код файла /app/views/search_age/search.html.erb должен иметь вид:
<h1>Отчет поиск сотрудников по возрасту</h1>
<%= link_to 'Главная страница', controller: 'home_page' %> |
<%= link_to 'Назад', controller: 'search_age' %>
<br><br>
<table border="1">
<th>Код</th>
<th>Фамилия</th>
<th>Имя</th>
<th>Отчество</th>
<%= Sotr.find_by_sql(["select sotrs.id, sotrs.s_fam, sotrs.s_name, sotrs.s_otch from sotrs where ceil((current_date-sotrs.s_date)/365) >= ?", params[:q]]).each do |sotr| %>
<tr>
<td><%= sotr.id %></td>
<td><%= sotr.s_fam %></td>
<td><%= sotr.s_name %></td>
<td><%= sotr.s_otch %></td>
</tr>
<% end %>
3) Создадим третий отчет, который при вводе пользователем диапазона дат, выводит на экран все ФИО сотрудников, родившихся в данный период.
Пишем в консоли команду для создания нового контроллера:
rails g controller Search_date index search
Далее откроем файл представления /app/views/search_date/index.html.erb и изменим его код:
<h1>Отчет поиск сотрудников по дате рождения</h1>
<%= link_to 'Главнаястраница', controller: 'home_page' %>
<br><br>
<%= form_tag("search", method: "get") do %>
<%= label_tag(:start, "Введите начальную дату:") %>
<%= date_select(:start, "written_on", discard_year: true) %>
<br><br>
<%= label_tag(:finish, "Введите конечную дату:") %>
<%= date_select(:finish, "written_on", discard_year: true) %>
<br><br>
<%= submit_tag("Искать") %>
<% end %>
Поясним код: тег date_select имеет конструкцию date_select(object_name, method, options = {}, html_options = {}), который возвращает набор выбранных тегов (один за год, месяц и день), предварительно выбранный для доступа к указанному атрибуту на основе даты (идентифицирован по методу) для объекта, назначенного шаблону (идентифицированного объектом). В данном случае выбран метод written_on. Также прописана опция discard_year, которая имеет значение true, означающая, что в селекторе не будет отображаться тег для года.
Для передачи переменных :start, :finish в запрос необходимо преобразовать их тип и извлечь выбранные месяц и число. Для этого в файле /app/controllers/search_date_controller.rb необходимо прописать следующее:
def search
@month_s = Date.civil(params[:start]["written_on(1i)"].to_i,params[:start]["written_on(2i)"].to_i, params[:start]["written_on(3i)"].to_i).month
@day_s = Date.civil(params[:start]["written_on(1i)"].to_i,params[:start]["written_on(2i)"].to_i, params[:start]["written_on(3i)"].to_i).day
@month_f = Date.civil(params[:finish]["written_on(1i)"].to_i,params[:finish]["written_on(2i)"].to_i, params[:finish]["written_on(3i)"].to_i).month
@day_f = Date.civil(params[:finish]["written_on(1i)"].to_i,params[:finish]["written_on(2i)"].to_i, params[:finish]["written_on(3i)"].to_i).day
end
Данный код переводит переменную в формат даты (Date.civil). Где каждый параметр селектора даты переводится в числовой тип (params[:start]["written_on(1i)"].to_i). written_on(1i) – означает, что из селектора даты берется компонент года, written_on(2i) – компонент месяца, written_on(3i) – компонент дня. После чего извлекается нужный компонент (месяц или день) – команда .month или .day.
Далее перейдем к файлу поиска. Путь /app/views/search_date /search.html.erb и изменим его код (метод Active Record):
<h1>Отчет поиск сотрудников по дате рождения</h1>
<%= link_to 'Главнаястраница', controller: 'home_page' %> |
<%= link_to 'Назад', controller: 'search_date' %>
<br><br>
<table border="1">
<th>Код</th>
<th>Фамилия</th>
<th>Имя</th>
<th>Отчество</th>
<th>Дата рождения</th>
<%= Sotr.select("sotrs.id, sotrs.s_fam, sotrs.s_name, sotrs.s_otch, sotrs.s_date").where("date_part('month', sotrs.s_date) >= ? and date_part('day', sotrs.s_date) >= ? and date_part('month', sotrs.s_date) <= ? and date_part('day', sotrs.s_date) <= ?", @month_s, @day_s, @month_f, @day_f).find_each do |sotr|%>
<tr>
<td><%= sotr.id %></td>
<td><%= sotr.s_fam %></td>
<td><%= sotr.s_name %></td>
<td><%= sotr.s_otch %></td>
<td><%= sotr.s_date %></td>
</tr>
<% end %>
В данном коде команда date_part извлекает необходимую часть даты (год, месяц или день); является командой postgresql.
Также, для отображения отчета на главной странице не забываем прописать в файле /app/views/home_page/index.html.erb ссылку на контроллер (рисунок 11.8).
Рисунок 11.8 – Код /home_page/index.html.erb
Запускаем сервер и открываем отчет. В результате страница index имеет вид как на рисунке 11.9 и результат запроса имеет вид как на рисунке 11.10.
Рисунок 11.9 – Страница index
Рисунок 11.10 – Результат выполнения запроса (страница search)
Второй вариант предусматривает выполнения запроса посредством sql кода. Для этого код файла /app/views/search_date/search.html.erb должен иметь вид:
<h1>Отчет поиск сотрудников по дате рождения</h1>
<%= link_to 'Главнаястраница', controller: 'home_page' %> |
<%= link_to 'Назад', controller: 'search_date' %>
<br><br>
<table border="1">
<th>Код</th>
<th>Фамилия</th>
<th>Имя</th>
<th>Отчество</th>
<th>Дата рождения</th>
<%= Sotr.find_by_sql(["select sotrs.id, sotrs.s_fam, sotrs.s_name, sotrs.s_otch, sotrs.s_date from sotrs where date_part('month', sotrs.s_date) >= ? and date_part('day', sotrs.s_date) >= ? and date_part('month', sotrs.s_date) <= ? and date_part('day', sotrs.s_date) <= ?", @month_s, @day_s, @month_f, @day_f]).each do |sotr|%>
<tr>
<td><%= sotr.id %></td>
<td><%= sotr.s_fam %></td>
<td><%= sotr.s_name %></td>
<td><%= sotr.s_otch %></td>
<td><%= sotr.s_date %></td>
</tr>
<% end %>