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

Question

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?

Andrew Haines · Accepted Answer · 2013-01-15T17:42:32.440

3

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.

edited Jan 15 '13 at 17:42

answered Jan 03 '13 at 17:42

Andrew Haines

6,574
21
34

Prettier that what I actually came up with. Stole a bit from here [link](http://stackoverflow.com/questions/8045823/how-do-i-write-a-union-chain-with-activerelation) to come up with my answer, but I think I like your better. I'll have to out compact and reduce. – erroric Jan 03 '13 at 19:03
Thanks for the explanation of `reduce` - I will be using this again in the future. – erroric Jan 03 '13 at 19:49
I get a "syntax error, unexpected modifier_if, expecting ']'" right before the if statement in array creation loop? Any pointers are appreciated. – Conor Jan 15 '13 at 17:27
Yeah this was blatantly wrong, sorry. You can't use the `if`s like that at all. I'll update the answer. – Andrew Haines Jan 15 '13 at 17:32
Andy amazing! Thank you for the help, but more importantly the amazing speed of updating the solution. I would have never figured it out; still learning Ruby. Tested and working. – Conor Jan 15 '13 at 17:55

score 0 · Answer 2 · edited May 23 '17 at 12:23

After eventually finding this related post, I cannibalized @bradgonesurfing 's alternate pattern to come to this solution:

def self.by_any (sin, name, dob)
  queries = Array.new
  queries << self.by_sin(sin) unless sin.nil?
  queries << self.by_name(name) unless name.nil?
  queries << self.by_dob(dob) unless dob.nil?

  self.where do
    queries = queries.map { |q| id.in q.select{id} }
    queries.inject { |s, i| s | i }
  end
end

where self.by_sin, self.by_name, and self.by_dob are simple scopes with filters. This produces something along the lines of:

SELECT * 
FROM clients 
WHERE clients.id IN (<subquery for sin>) 
   OR clients.id IN (<subquery for name>) 
   OR clients.id IN (<subquery for dob>)

where the subqueries are only include if their associated value is not nil.

This effectively allows me to union the appropriate scopes together as an ActiveRecord::Relation.

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

2 Answers2

Linked