CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-5
Cannon Moyer
Cannon Moyer

Reputation: 3164

Rails - Query Associated Models

I feel like this is rather simple, but nothing I've found seems to work. I'm using a PostgreSQL database with Rails 5. I need to run a query that finds all Products that contain a string of text in the name column or that same string is found in an associated model. Here is my model structure. 

class NormalBrand < ApplicationRecord
    has_many :normal_models
end

class NormalModel < ApplicationRecord
    belongs_to :normal_brand 
    has_many :products
end

class Product < ApplicationRecord
    belongs_to :normal_model
end

Each of the models has a field called "name". I am trying to create a query in my Products controller that finds all of the Products where the string of text is found in any of the 3 models "name" column. Something like this...

@products = Product.where("name like lower(?)", "%#{value}%").or.where("normal_model.name like lower(?)", "%#{value}%").or.where("normal_model.normal_brand.name like lower(?)", "%#{value}%")

I know the above query is completely wrong and I should have some sort of joins statement, but that's where I need help. Thanks in advance.

Upvotes: 0

Views: 1631

Answers (1)

Deepak Mahakale
Deepak Mahakale

Reputation: 23661

Join normal_model and normal_brand and then you can query on all of three tables

@products = 
  Product
  .joins(normal_model: :normal_brand)
  .where("products.name like lower(?)", "%#{value}%")
  .or.where("normal_models.name like lower(?)", "%#{value}%")
  .or.where("normal_brands.name like lower(?)", "%#{value}%")

Or simply in one raw where 

@products = 
  Product
  .joins(normal_model: :normal_brand)
  .where("products.name      LIKE lower(:term) OR
          normal_models.name LIKE lower(:term) OR
          normal_brands.name LIKE lower(:term)", term: "'%#{value}%'")

Upvotes: 5

Related Questions