Can you add clauses in a where block conditionally when using Squeel?

To start, I'm using Rails v3.2.9 with Squeel 1.0.13 and here's what I'm trying to do:

I want to search for a client using any of three pieces of identifying information - name, date of birth (dob), and social insurance number (sin). The result set must include any record that has any of the identifier - an OR of the conditions. I have done this in Squeel before and it would look something like:

scope :by_any, ->(sin, name, dob){ where{(client.sin == "#{sin}") | (client.name =~ "%#{name}%") | (client.dob == "#{dob}")} }

This works fine as long as I provide all of the identifiers. But what if I only have a name? The above scope results in:

SELECT "clients".* FROM "clients" WHERE ((("clients"."sin" IS NULL OR "clients"."name" ILIKE '%John Doe%') OR "clients"."dob" IS NULL))

This includes the set of clients where sin is null and the set of clients where dob is null along with the requested set of clients with a name like 'John Doe'.

So enter my attempt to conditionally add clauses to the where block. At first, I tried to check the values using the nil? method:

def self.by_any (sin, name, dob)
  where do
    (clients.sin == "#{sin}" unless sin.nil?) |
    (clients.name =~ "%#{name}" unless name.nil?) |
    (clients.dob == "#{dob}" unless dob.nil?)
  end

which results in:

SELECT "clients".* FROM "clients" WHERE ('t')

raising many other questions, like what's the deal with that 't', but that's a tangent.

Short of writing the where clause for each permutation, is there a way I can conditionally add clauses?

Solution

So, this isn't the prettiest thing ever, but it does what you're after.

def self.by_any(sin, name, dob)
  where do
    [
      sin.presence && clients.sin == "#{sin}",
      name.presence && clients.name =~ "%#{name}",
      dob.presence && clients.dob == "#{dob}"
    ].compact.reduce(:|)
    # compact to remove the nils, reduce to combine the cases with |
  end
end

Basically, [a, b, c].reduce(:f) returns (a.f(b)).f(c). In this case f, the method invoked, is the pipe, so we get (a.|(b)).|(c) which, in less confusing notation, is (a | b) | c.

It works because, in Squeel, the predicate operators (==, =~, and so on) return a Predicate node, so we can construct them independently before joining them with |.

In the case where all three are nil, it returns all records.