- 投稿日:2021-01-05T23:37:58+09:00
【初学者必見】Railsで「複数のカラムからの」検索機能を実装してみた。
はじめに
あけましておめでとうございます?
Railsで定番の検索機能。
検索機能に関しての記事はQiita上でもたくさんあるが、どれも一つのテーブルの中の一つのカラムから検索する方法ばかり。(あとは、もう古くなったform_tagが使われていたり)
でも実際は一つのテーブルの中の複数のカラムから検索したいケースもあるはず。
例えば、db/schema.rbActiveRecord::Schema.define(version: 2020_12_18_025546) do create_table "agents", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8", force: :cascade do |t| t.string "last_name", null: false t.string "first_name", null: false t.string "last_name_kana", null: false t.string "first_name_kana", null: false t.string "company_name", null: false t.string "company_location", null: false t.bigint "user_id", null: false t.datetime "created_at", precision: 6, null: false t.datetime "updated_at", precision: 6, null: false t.index ["user_id"], name: "index_agents_on_user_id" end end上記のagentsテーブルは「氏」と「名」と「氏のフリガナ」と「名のフリガナ」が独立しているので、agentを特定する際に一つのカラムから検索することはできません。
ましてやこの場合は一つの検索フォームから会社名(company_name)だったり会社の所在地(company_location)からagentを特定できればもっと便利になるはずです。順序
- Viewに検索フォームを配置する
- searchアクションのルーテインングを設定する
- 検索するためのメソッド(searchメソッド)をモデルに定義する ←ココ重要
- searchアクションをコントローラーに定義する
- 検索結果画面のViewを作成する
では早速やっていきましょう。
1. Viewに検索フォームを配置する
検索フォームを作りたいViewの中に検索フォームを書いていきます。
app/views/agents/index.html.erb<%= form_with(url: search_agents_path, local: true, method: :get, class: "field has-addons") do |form| %> <%= form.text_field :keyword, placeholder: "営業マンを検索する", class: "input" %> <%= form.submit "検索", class: "button is-info" %> <% end %>※ class名はCSSフレイムワークのBULMAを使っています。
↓以下のように検索ボックスが作れました!
2. searchアクションのルーテインングを設定する
searchアクションはRailsのデフォルトである7つのアクション(index, show, new, create, edit, update, destroy)以外なので”search”と言う名前でアクションを定義します。
自分で定義したアクションにはmemberまたはcollectionというオプションを使う必要があります。
詳細は ココで確認してみてください。
今回は:idを関連付ける必要が無いので、collectionを使います。config/routes.rbRails.application.routes.draw do devise_for :users root to: 'homes#top' resources :agents, only: [:index, :show, :new, :create] do resources :reviews, only: [:index, :create, :edit, :update, :destroy] collection do #←ココ get 'search' #←ココ end #←ココ end end3. 検索するためのメソッド(searchメソッド)をモデルに定義する ココ重要
whereメソッドとLike句を使います。
whereメソッドとは
モデルが使用できる、ActiveRecordメソッドの1つ。
モデル名.where(条件)のの形で引数部分に条件を指定すること、テーブル内の「条件に一致したレコードのインスタンス」を配列の形で取得できます。引数の条件には、「検索対象となるカラム」を必ず含めなければなりません。Like句とは
曖昧な文字列で検索しても、テーブルの対象のカラムから検索したいもの探し出せるようにするために使用するSQLのクエリです。whereメソッドの条件の中で使います。
一つのカラムから検索する場合は、以下のような構文をモデル内で用います。
モデル名.where('探し出したいカラム名 LIKE(?)', "%#{search}%")
複数のカラムから検索したい場合は、(例として3つのカラムから探したい時)
モデル名.where('探し出したいカラム名 LIKE(?) OR 探し出したいカラム名 LIKE(?) OR 探し出したいカラム名 LIKE(?)', "%#{search}%", "%#{search}%", "%#{search}%")
のような構文になります。
「探し出したいカラム名 LIKE(?)」を「OR」で繋いで、カラムの数の分だけ「"%#{search}%"」を書くことによって複数のカラムから検索をすることができます。具体的には、以下のようにsearchメソッドをモデル内で定義しました。
app/models/agent.rbclass Agent < ApplicationRecord with_options presence: true do validates :user_id validates :company_name validates :company_location with_options format: { with: /\A(?:\p{Hiragana}|\p{Katakana}|[ー-]|[一-龠々])+\z/ } do validates :first_name validates :last_name end with_options format: { with: /\A[ァ-ヶー-]+\z/ } do validates :last_name_kana validates :first_name_kana end end def avg_score if reviews.empty? 0.0 else reviews.average(:score).round(1).to_f end end def review_score_percentage if reviews.empty? 0.0 else reviews.average(:score).round(1).to_f / 5 * 100 end end #↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ココ def self.search(search) if search != "" Agent.where('last_name LIKE(?) OR first_name LIKE(?) OR last_name_kana LIKE(?) OR first_name_kana LIKE(?) OR company_name LIKE(?) OR company_location LIKE(?)', "%#{search}%", "%#{search}%", "%#{search}%", "%#{search}%", "%#{search}%", "%#{search}%") else Agent.all end end #↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑ココ has_many :reviews, dependent: :destroy belongs_to :user endif文を用いて、検索結果が一つも無い場合は全てのagentを出力するようにしました。
4. searchアクションをコントローラーに定義する
以下のようにsearchアクションをコントローラーに追加します。
app/models/agent.rbdef search @agents = Agent.search(params[:keyword]) end検索フォームから送られて来ているパラメーターである:keywordを引数にします。
5. 検索結果画面のViewを作成する
新しくsearch.html.erbを作成することで、検索結果を表示するviewを作成します。
コントローラーで定義した@agentsをeach文を用いて全て表示していきます。app/views/agents/search.html.erb<div class="section pb-0"> <div class="container"> <div class="columns is-centered"> <div class="column is-6"> <%= form_with(url: search_agents_path, local: true, method: :get, class: "field has-addons") do |form| %> <%= form.text_field :keyword, placeholder: "営業マンを検索する", class: "input" %> <%= form.submit "検索", class: "button is-info" %> <% end %> <div> 次の検索結果を表示しています:<span class="has-text-weight-bold is-size-4"> <%= params[:keyword] %></span> </div> </div> </div> </div> </div> <section class="section"> <div class="container"> <div class="columns is-centered"> <div class="column is-5"> <% @agents.each do |agent| %> <div class="card mb-6"> <header class="card-header"> <p class="card-header-title"> <%= agent.last_name %> <%= agent.first_name %> <span class="has-text-weight-light is-italic is-size-7">(<%= agent.last_name_kana %> <%= agent.first_name_kana %>)</span> </p> </header> <div class="card-content"> 所属会社: <%= agent.company_name %><br> 場所: <%= agent.company_location %><br> <div class="content"> <div class="content average-score"> <div class="star-rating mb-2"> <div class="star-rating-front" style="width: <%= agent.review_score_percentage %>%">★★★★★</div> <div class="star-rating-back">★★★★★</div> </div> <div class="average-score-display ml-3 pt-2"> <%= agent.avg_score %>点(<%= agent.reviews.count %>件のレビュー) </div> </div> </div> </div> <footer class="card-footer"> <%= link_to agent_reviews_path(agent), class: "button card-footer-item" do %> レビューを見る <% end %> <%= link_to agent_path(agent), class: "button card-footer-item" do %> レビューを書く <% end %> </footer> </div> <% end %> <div class="is-centered"> <%= link_to new_agent_path, class: "button is-primary mt-3" do %> 新しい営業マンを追加する <% end %> </div> </div> </div> </div>※ class名はCSSフレイムワークのBULMAを使っています。
↓以下のような検索結果画面ができました。
これは検索ボックスに「木」と入力して出た検索結果です。
最後に
自分がRailsで検索機能を実装していてあったら良いなと思うものを書いてみました。
いかがでしたでしょうか。
間違っている点やわかりにくい点があったらお気軽に教えてください!
では次回の投稿でお会いしましょう。
- 投稿日:2021-01-05T22:04:04+09:00
PowerAutomateにてデータベース挿入時、自動採番を補う
- 投稿日:2021-01-05T19:37:25+09:00
[MySQL]データ検索をする
はじめに
今回はデータの検索について忘れないように
メモしていきます。
SELECT構文を使用して検索していきます。
こちらを使用
WHERE
LIKE
AND演算子
OR演算子
NOT演算子
BETWEEN演算子
IN演算子
GROUP BY
BINARY
DESC
LIMIT
OFFSET
NULL, NOT NULL
使ってみる!
今回はこのようなテーブルを用意しましたのでデータを抽出していきます。
mysql> select * from sample; +----+---------+-----------------+-------+ | id | name | message | point | +----+---------+-----------------+-------+ | 1 | taro | こんにちは | 3 | | 2 | michiko | こんばんは | 55 | | 3 | sumi | おはよう | 3 | | 4 | jiro | こんにちは | 7 | | 5 | saburo | おはよう | 10 | +----+---------+-----------------+-------+ 5 rows in set (0.01 sec)
まずテーブルの中の全てのデータを抽出したい場合は
「*」を使用します。全てのカラムを選択してくれます。SELECT * FROM posts; --* 全てのカラム特定のものを取り出したい場合は
mysql> SELECT id, message FROM sample; +----+-----------------+ | id | message | +----+-----------------+ | 1 | こんにちは | | 2 | こんばんは | | 3 | おはよう | | 4 | こんにちは | | 5 | おはよう | +----+-----------------+ 5 rows in set (0.00 sec)「WHERE」 を使う
WHEREの後に条件をつけると検索したいものを指定することができます。
今回は「point」が10ポイント以上のものを検索します。mysql> SELECT * FROM sample WHERE point >= 10; +----+---------+-----------------+-------+ | id | name | message | point | +----+---------+-----------------+-------+ | 2 | michiko | こんばんは | 55 | | 5 | saburo | おはよう | 10 | +----+---------+-----------------+-------+ 2 rows in set (0.00 sec)文字も同じように検索することができます。
「こんにちは」を検索します。mysql> SELECT * FROM sample WHERE message = 'こんにちは'; +----+------+-----------------+-------+ | id | name | message | point | +----+------+-----------------+-------+ | 1 | taro | こんにちは | 3 | | 4 | jiro | こんにちは | 7 | +----+------+-----------------+-------+ 2 rows in set (0.00 sec)「AND」 を使う
「WHERE」の後ろの条件が二つとも合ったものを検索します。
mysql> SELECT * FROM sample WHERE point >= 8 AND point <= 20; +----+--------+--------------+-------+ | id | name | message | point | +----+--------+--------------+-------+ | 5 | saburo | おはよう | 10 | +----+--------+--------------+-------+ 1 row in set (0.00 sec)「BETWEEN」 を使用すると上記のものと同じものが検索されます。
(書き方は違いますが出てくるものは同じ)
mysql> SELECT * FROM sample WHERE point BETWEEN 8 AND 20; +----+--------+--------------+-------+ | id | name | message | point | +----+--------+--------------+-------+ | 5 | saburo | おはよう | 10 | +----+--------+--------------+-------+ 1 row in set (0.00 sec)「NOT」 を使用すると逆の条件のものが抽出されます。
mysql> SELECT * FROM sample WHERE point NOT BETWEEN 10 AND 20; +----+---------+-----------------+-------+ | id | name | message | point | +----+---------+-----------------+-------+ | 1 | taro | こんにちは | 3 | | 2 | michiko | こんばんは | 55 | | 3 | sumi | おはよう | 3 | | 4 | jiro | こんにちは | 7 | +----+---------+-----------------+-------+ 4 rows in set (0.00 sec)「OR」 を使う
「7」 もしくは 「10」 の pointのものを検索します。
mysql> SELECT * FROM sample WHERE point = 7 OR point = 10; +----+--------+-----------------+-------+ | id | name | message | point | +----+--------+-----------------+-------+ | 4 | jiro | こんにちは | 7 | | 5 | saburo | おはよう | 10 | +----+--------+-----------------+-------+ 2 rows in set (0.00 sec)「IN」 を使用すると上記と同じ条件のものが検索されます。
mysql> SELECT * FROM sample WHERE point IN (7, 10); +----+--------+-----------------+-------+ | id | name | message | point | +----+--------+-----------------+-------+ | 4 | jiro | こんにちは | 7 | | 5 | saburo | おはよう | 10 | +----+--------+-----------------+-------+ 2 rows in set (0.00 sec)こちらも 「NOT」 を使用すると逆の条件のものが抽出されます。
mysql> SELECT * FROM sample WHERE point NOT IN (7, 10); +----+---------+-----------------+-------+ | id | name | message | point | +----+---------+-----------------+-------+ | 1 | taro | こんにちは | 3 | | 2 | michiko | こんばんは | 55 | | 3 | sumi | おはよう | 3 | +----+---------+-----------------+-------+ 3 rows in set (0.00 sec)「LIKE」 を使う
「LIKE」 を使うと特殊な記号も使用することができます。
「
%
」パーセント記号 → 任意の文字列(空白文字列を含む)(1文字以上)「
_
」アンダースコア記号 → 任意の1文字はじめの文字が「お」のものを検索してみます。
mysql> SELECT * FROM sample WHERE message LIKE 'お%'; +----+--------+--------------+-------+ | id | name | message | point | +----+--------+--------------+-------+ | 3 | sumi | おはよう | 3 | | 5 | saburo | おはよう | 10 | +----+--------+--------------+-------+ 2 rows in set (0.00 sec)英語の場合は「BINARY」を使用すると大文字と小文字を区別してくれます。
mysql> SELECT * FROM sample WHERE name LIKE BINARY 's%'; +----+--------+--------------+-------+ | id | name | message | point | +----+--------+--------------+-------+ | 3 | sumi | おはよう | 3 | | 5 | saburo | おはよう | 10 | +----+--------+--------------+-------+ 2 rows in set (0.00 sec) --大文字だと・・・ mysql> SELECT * FROM sample WHERE name LIKE BINARY 'S%'; Empty set (0.00 sec) --空ですね。「%」を使用して間に希望の文字が入っているものを検索する
部分一致や
後方一致などで検索することができます。--部分一致 mysql> SELECT * FROM sample WHERE message LIKE '%ば%'; +----+---------+-----------------+-------+ | id | name | message | point | +----+---------+-----------------+-------+ | 2 | michiko | こんばんは | 55 | +----+---------+-----------------+-------+ 1 row in set (0.00 sec) --後方一致 mysql> SELECT * FROM sample WHERE message LIKE '%う'; +----+--------+--------------+-------+ | id | name | message | point | +----+--------+--------------+-------+ | 3 | sumi | おはよう | 3 | | 5 | saburo | おはよう | 10 | +----+--------+--------------+-------+ 2 rows in set (0.00 sec)「_」を使用して文字数を限定して検索します。
3番目に「に」のついたものを検索します。mysql> SELECT * FROM sample WHERE message LIKE '__に%'; +----+------+-----------------+-------+ -- ↑アンダースコア二つ | id | name | message | point | +----+------+-----------------+-------+ | 1 | taro | こんにちは | 3 | | 4 | jiro | こんにちは | 7 | +----+------+-----------------+-------+ 2 rows in set (0.00 sec)「ORDER BY」 を使う
検索結果の順番を並び替えたい場合に使用します。
「ORDER BY」の後に指定したいものを記述するとまずは小さい順に並び変わります。
mysql> SELECT * FROM sample ORDER BY point; +----+---------+-----------------+-------+ | id | name | message | point | +----+---------+-----------------+-------+ | 1 | taro | こんにちは | 3 | | 3 | sumi | おはよう | 3 | | 4 | jiro | こんにちは | 7 | | 5 | saburo | おはよう | 10 | | 2 | michiko | こんばんは | 55 | +----+---------+-----------------+-------+ 5 rows in set (0.00 sec)さらに一番後ろに「DESC」と記述すると大きい順に並べてくれます。
mysql> SELECT * FROM sample ORDER BY point DESC; +----+---------+-----------------+-------+ | id | name | message | point | +----+---------+-----------------+-------+ | 2 | michiko | こんばんは | 55 | | 5 | saburo | おはよう | 10 | | 4 | jiro | こんにちは | 7 | | 1 | taro | こんにちは | 3 | | 3 | sumi | おはよう | 3 | +----+---------+-----------------+-------+ 5 rows in set (0.00 sec)さらにーーー、後ろにもう一つカラムを指定すると、
今回の場合は「point」が大きい順に並んでおり、次に指定した「message」のpointが「3」の部分が あいうえお順で並べられているのがわかります。
mysql> SELECT * FROM sample ORDER BY point DESC, message; +----+---------+-----------------+-------+ | id | name | message | point | +----+---------+-----------------+-------+ | 2 | michiko | こんばんは | 55 | | 5 | saburo | おはよう | 10 | | 4 | jiro | こんにちは | 7 | | 3 | sumi | おはよう | 3 | | 1 | taro | こんにちは | 3 | +----+---------+-----------------+-------+ 5 rows in set (0.00 sec)「LIMIT」 を使用すると検索する数が指定できます。
--条件を指定せず mysql> SELECT * FROM sample LIMIT 3; +----+---------+-----------------+-------+ | id | name | message | point | +----+---------+-----------------+-------+ | 1 | taro | こんにちは | 3 | | 2 | michiko | こんばんは | 55 | | 3 | sumi | おはよう | 3 | +----+---------+-----------------+-------+ 3 rows in set (0.00 sec) --条件を増やしてさらに数を指定 mysql> SELECT * FROM sample ORDER BY point DESC, message LIMIT 3; +----+---------+-----------------+-------+ | id | name | message | point | +----+---------+-----------------+-------+ | 2 | michiko | こんばんは | 55 | | 5 | saburo | おはよう | 10 | | 4 | jiro | こんにちは | 7 | +----+---------+-----------------+-------+ 3 rows in set (0.00 sec)「OFFSET」 を使用して検索したい場所指定します。
--「message」三つを2番目から抽出したい --(0からで数えます。) mysql> SELECT * FROM sample ORDER BY point DESC, message LIMIT 3 OFFSET 2; +----+------+-----------------+-------+ | id | name | message | point | +----+------+-----------------+-------+ | 4 | jiro | こんにちは | 7 | --[2]番目 | 3 | sumi | おはよう | 3 | --[3]番目N | 1 | taro | こんにちは | 3 | --[4]番目 +----+------+-----------------+-------+ 3 rows in set (0.00 sec)次のようにすると上記と同じ意味になります。
--先頭から数えて2番目のものを三つ取り出す mysql> SELECT * FROM sample ORDER BY point DESC, message LIMIT 2, 3; +----+------+-----------------+-------+ | id | name | message | point | +----+------+-----------------+-------+ | 4 | jiro | こんにちは | 7 | | 3 | sumi | おはよう | 3 | | 1 | taro | こんにちは | 3 | +----+------+-----------------+-------+ 3 rows in set (0.00 sec)ちなみに「NULL」を検索したい場合
SELECT * FROM sample WHERE point != 7 OR point IS NULL; -- とか SELECT * FROM sample WHERE point IS NOT NULL; --NULL以外のものを抽出& 「COUNT」 を使って数を数える
mysql> SELECT COUNT( * ) FROM sample; +------------+ | COUNT( * ) | +------------+ | 5 | +------------+ 1 row in set (0.00 sec)まとめ
基本的なものを学習しました。
早く慣れて確認しなくても検索できるようにしたいです
参考
- 投稿日:2021-01-05T02:41:28+09:00
(自分用)MySQLあれこれ
項目
- コマンドあれこれ(DB作成,切替,テーブル・DB一覧,SQLの状態確認)
- ユーザ作成と権限付与
- VSCodeでMySQLに接続する時にエラーが出た場合
- etc
1.コマンドあれこれ(DB作成,切替,テーブル・DB一覧,SQLの状態確認)
SQL-- DB作成 CREATE DATABASE ~~~; -- DB切替 USE ~~~; -- テーブル・DB一覧 show databases; show table; -- 状態確認 status;2. ユーザ作成と権限付与
ユーザ作成
SQLcreate user 'ユーザ名'@'ホスト名' identified by 'パスワード'; -- ホスト名は色々と決められる -- @"IPアドレス"でそのIPのみからログイン可能に -- @"localhost"はそのままローカルホスト -- @"%"でどのホストからでもログイン可能 -- ログイン mysql -u ユーザ名 -p -- or mysql -u ユーザ名 -h ホスト名 -p権限付与
SQL-- ルートでログインして GRANT ALL PRIVILEGES ON 使うDB名.* TO 付与するユーザ名@そのホスト名 IDENTIFIED BY 'パスワード'; -- この後作成したユーザで入り直せば成功している筈3.VSCodeでMySQLに接続する時にエラーが出た場合
- VSCodeでMySQLの拡張機能に接続しようとしたら
Client does not support authentication ~~
ってエラーが出たSQL-- ルートでログインして ALTER USER "使うユーザ名"@"ホスト名" IDENTIFIED WITH 'mysql_native_password' BY "パスワード"; -- これでもう一度1から接続し直したら出来た!4.etc