Artyom Kalmykov Artyom Kalmykov - 1 year ago 72
Ruby Question

Better query using WHERE IN () when param can be nil

For example we have model TableRow - columns (:account_number, :month, :department, :phone_number). And have a method that returns filtered rows by arrays of this params.
For required params we can use

TableRow.where('account_number IN (?)', param)

Is there best way to add in this query unrequired params (department, phone_number) that can be nill and we should return records with any params in this column?

Answer Source

There are a couple ways to approach this. If you want your query to be static, you can check the literal value of your param with the SQL logic itself:

TableRow.where('COALESCE(:depts) IS NULL OR department IN (:depts)', depts: param)

You can also build up your relation incrementally in Ruby:

relation = TableRow.all
relation = relation.where(department: depts) if depts.present?
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download