Chetan Datta Chetan Datta - 1 year ago 60
Ruby Question

Search table fields along with parent table using Foreign Key

I have 4 search fields in my index page of Employee table -

id, due_date, employee_full_name, company_name

Here, in
, I am checking if search parameters are present

search_parameters =
{:id => params[:id], :due_date => params[:due_date],
:employee_full_name => params[:employee_full_name]}
.select { |key,value| value.present?}

I am searching the columns of Employee table and it is working fine

@employees_search = Employee
.where(search_parameters, params[:id].to_i, params[:due_date],

Here I am searching 'company_name' from Company table with
as the foreign key. This is also working fine.

@company_search = Employee
.where(company_id: Company
.where(company_name: params[:company_name]))

But I need to combine the above 2 queries so that user can search all 4 fields together. Somewhat like the below code.

@employees_search = Employee
.where(search_parameters, params[:id].to_i, params[:due_date],
company_id: Company
.where(company_name: params[:company_name]))

the above query just gives search result of employee table and not the combined result. What is wrong with the queries?

Answer Source

Try this query using the active record querying using the or query by giving all the fields,

@employees_search =  Employee
            .where("id = ? or due_date = ? or employee_full_name = ? 
            or company_id in (?)", params[:id],params[:due_date],params[:employee_full_name],
            Company.where('company_name = ?',params[:company_name]).pluck(:id))

Search all the fields in the Employee table using the '= ?' syntax and when coming to the company_id there may be multiple companies with the same name so use in (?) syntax, even if it works for single company. This will give you all the employees with the values from the params and also including the employees who belong to that company.

This may solve your issue.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download