CODEWITHSUNDEEP
sqlruby-on-railsrubyjoinwhere-clause
Alejo Amiras
Alejo Amiras

Reputation: 67

SQL Chain (joins and wheres) - Ruby

I'm trying to make a search filter, and all params are the names of the check boxes so they are arrays filled with id values.

@sql_query = ''

if filters_benefit_type.nil? == false
  @sql_query = 'id_benefit_type = (:benefit_types)'
end

if filters_category.nil? == false
  if filters_benefit_type.nil? == false
    @sql_query = @sql_query + ' AND '
  end
  @sql_query = @sql_query + 'id_subcategory = (:company_subcategorys)'
end

if filters_zone.nil? == false
  if filters_category.nil? == false
    @sql_query = @sql_query + ' AND '
  end
  @sql_query = 'zone = (:zones)'
end

@companys = Company
  .joins(:subsidiary)
  .joins(:benefit)
  .where(@sql_query, 
    :benefit_types => filters_benefit_type, 
    :company_subcategorys => filters_category, 
    :zones => filters_zone
  )

And this throws me the following error:

Mysql2::Error: Operand should contain 1 column(s):

Any ideas ? I guess I'm doing the .joins and .where wrong...

Upvotes: 0

Views: 211

Answers (1)

Martin M
Martin M

Reputation: 8638

Try chaining Relations:

@companies = Company.scoped
@companies = @companies.where(id_benefit_type: filter_benefit_type) if filter_benefit_type.present?
@companies = @companies.where(id_subcategory: filter_category) if filter_category.present?
@companies = @companies.where(zone: filter_zone) if filter_zone.present?

as long as the filters are empty or array of integers!

where(column: [1,2,3]) gives the condition where column in (1,2,3)

Addition

the details of the where parts depend on the relationships of your models.
The column names did'n reflect that (at least, not in RoR style).
If i.e. Company belongs_to :benefit_type, foreign_key: "id_benefit_type", my first example would work (the table of the companies would have a column id_benefit_type).

If Company has_and_belongs_to_many :benefits, and Benefit has an attribute id_benefit_type, you would need a join:

@companies = @companies.joins(:benefits).where(:benefits => {id_benefit_type: filter_benefit_type}) if filter_benefit_type.present?

Upvotes: 1

Related Questions