Make advanced Search with Inner Join Tables

Question

I have a question. I have two tables with an NN relationship. They are perfectly well linked in base (verified by the console with a Tips.find(1).categories). The two tables are Tip and Category. My objective is to create a search form that allows you to select a category, and to display all the Tip related to that category. I found a method that does this, it works for base links (i. e. if for example there was a category_id in the Tip table, which is not the case here). So I have to find a way to write the following search condition:

Tip.rb

class Tip < ApplicationRecord

has_many :category_to_tips
  has_many :categories, through: :category_to_tips

  has_many :comments

  belongs_to :creator, class_name: "User"

  has_many :likes, dependent: :destroy

  def self.search(params)
      tips = Tip.where("(tips) LIKE ?", "%#{params[:search]}%") if params[:search].present?
      tips
  end
end

Tip_controller :

def index
   @tip = Tip.all
   @tip = Tip.joins(:categories).search(params[:search])
 end

form in index.html.erb :

    <%= form_tag tips_path, :method => 'get' do %>
  <p>
    <%= text_field_tag :search, params[:search] %>
    <%= submit_tag "Search", :name => nil %>
  </p>
    <% end %>

And when I run my form, i have this error :

PG::SyntaxError: ERREUR:  erreur de syntaxe sur ou près de « . »
LINE 1: ...ry_to_tips"."category_id" WHERE ((Category.find(2).tips) LIK...
                                                             ^
: SELECT "tips".* FROM "tips" INNER JOIN "category_to_tips" ON "category_to_tips"."tip_id" = "tips"."id" INNER JOIN "categories" ON "categories"."id" = "category_to_tips"."category_id" WHERE ((Category.find(2).tips) LIKE '%2%')

I hope I've been clear and I thank you in advance! :)

Answer 1

If I am reading it correct you are looking for a category so that you can display all the tips related to the category. Why don't you just do this in youre controller.

@tip = Category.where("name LIKE ?", "#{params[:search]}%").includes(:tips).map(&:tips).uniq

name would be the database field from category you are searching on.