# Terminal
docker compose up
bin/rails g migration add_fulltext_to_books

# docker-compose.yml
version: '3'
services:
  mysql:
    image: mysql:8.0.30
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_USER: username
      MYSQL_PASSWORD: password
      MYSQL_DATABASE: template_development
    ports:
      - 3306:3306
    command: --bind-address=0.0.0.0 --authentication_policy=mysql_native_password
    volumes:
      - mysql:/var/lib/mysql
volumes:
  mysql:

# views/books/index.html.erb
<%= form_with url: books_path, method: :get, class: "row g-3" do %>
  <div class="col-auto">
    <%= text_field_tag :q, params[:q], class: "form-control" %>
  </div>
  <div class="col-auto">
    <%= submit_tag "Search", class: "btn btn-primary" %>
  </div>
<% end %>

<td><%= highlight(book.title, params[:q].to_s.split(" ")) %></td>
<td><%= highlight(book.content, params[:q].to_s.split(" ")) %></td>

# books_controller.rb
def index
  # @books = Book.all

  # @books = if params[:q]
  #   Book.where("title LIKE :search OR content LIKE :search", { search: "%#{params[:q]}%" })
  # else
  #   Book.all
  # end

  @books = if params[:q]
    Book.find_by_sql(["
      SELECT id, title, content,
        MATCH(title) AGAINST (:search IN BOOLEAN MODE) AS rel1,
        MATCH(content) AGAINST (:search IN BOOLEAN MODE) AS rel2
      FROM books
      WHERE MATCH(title,content) AGAINST (:search IN BOOLEAN MODE)
      ORDER BY (rel1*1.5)+(rel2) DESC", { search: params[:q] }])
  else
    Book.all
  end
end

# db/migrate/20230129032817_add_fulltext_to_books.rb
class AddFulltextToBooks < ActiveRecord::Migration[7.0]
  def up
    execute "CREATE FULLTEXT INDEX fulltext_title_and_content ON books (title, content)"
    execute "CREATE FULLTEXT INDEX fulltext_title_on_books ON books (title)"
    execute "CREATE FULLTEXT INDEX fulltext_content_on_books ON books (content)"
  end

  def down
    execute "DROP INDEX fulltext_title_and_content ON books"
    execute "DROP INDEX fulltext_title_on_books ON books"
    execute "DROP INDEX fulltext_content_on_books ON books"
  end
end