Squeel syntax for field = value OR field IS NULL in a loop

Question

I'm trying to recreate the following syntax using squeel by using a loop:

WHERE season = "value" OR season IS NULL

I was able to get this far:

where do
  ((season == entry["season"]) | (season == nil)) &
  ((episode == entry["episode"]) | (episode == nil)) &
  ((imdb == entry["imdb"]) | (imdb == nil)) &
  (YEAR(theatrical) == year)

However, the problem is I want to do it in a loop so I don't have to do conditionals for when the values are empty (for example if imdb doesn't have a value I wouldn't even use that line).

I have this:

entry = { "imdb"=>"0364725", "media"=>"DVD", "season"=>"1", ...}
entry.each do |k,v|
    ((k == v) | (k == nil))
  end
end

But it does not put the field IS NULL bit in the SQL.

If it's not possible with Squeel, might it be done with ActiveRecord?

Ultimately, I'm looking for a way to avoid having conditionals for those fields in the SQL. As I said above, if the field is empty I don't list them at all, but if it has a value I need to do the whole field = value OR field IS NULL bit.

Any help would be greatly appreciated.

Edit:

The whole code I'm trying to use at the moment is this:

matches_found = 
includes(:dvd_director, :dvd_upc, :dvd_series).limit(1).
where do
  (title == title_to_search) &
  entry.each { |k,v| ((k == v) | (k == nil)) }
end

Note that everything after the | is getting ignored whether I use __send__(k) or not.

More info: One very strange thing is that if I use anything other than the k,v it gets ignored. For example:

    entry.each { |k,v| ((k == "bob") | (k == v)) }

"Bob" gets ignored! and the value (v) gets put into the SQL without the OR statement.

Did you miss some lines in the code? It seems your question boils down to: "how to use attribute names from variables with squeel?": `table.send(var)` — tokland, Nov 01 '12 at 17:50
No, that's not what I'm asking, see the edits in the original question to see that one of the options in the `OR` statement is getting ignored and I don't understand why. — kakubei, Nov 02 '12 at 11:44

tokland · Answer 1 · 2012-11-01T18:27:50.937

0

If I understood correctly, you need __send__:

where do
  (title == title_to_search) & entries.map do |k, v| 
    (__send__(k) == v) | (__send__(k) == nil)
    # or: __send__(k) >> [v, nil]
  end.inject(:&)
end

https://github.com/ernie/squeel/wiki/Tips-and-tricks

edited Nov 01 '12 at 18:27

answered Nov 01 '12 at 17:48

tokland

66,169
13
144
170

Can I remove the table command and still use `.send`? I'll try it. Simply because I have joins in there that would complicate things if I have to specify the table. The joins are taken care of with a `.includes` – kakubei Nov 01 '12 at 17:55
This doesn't work for me. With or without the `__send__` it is ignoring everything after the `|`. If I list them explicitly (as in the example in the original question), it works, but once I put it in a loop, the OR part gets ignored. – kakubei Nov 01 '12 at 18:05
are you trying it with the map+inject? it's weird, that works for me here. – tokland Nov 01 '12 at 18:07
No, no map. Let me post the whole code I'm using in the original question. – kakubei Nov 01 '12 at 18:18
a `each` generates no expression, try `map` + `inject` like I show in the answer. – tokland Nov 01 '12 at 18:21
Copy/pasting your code gives me this error: `ActiveRecord::StatementInvalid: Mysql2::Error: FUNCTION doghouse_data.inject does not exist: SELECT (joins...) WHERE ((`dvdpedia`.`title` = 'Underdog' AND inject(`map`.`&`))) LIMIT 1` – kakubei Nov 01 '12 at 18:38

score 0 · Accepted Answer · answered Nov 02 '12 at 12:23

In the end, the only solution I could come up with was to add nil into the array itself for Squeel to process it properly:

entry.each do |k,v| 
   # we add a nill value so Squeel will do the OR field IS NULL
   entry[k] = [v, nil] if k == "upc" || k == "asin" || k == "freebase" || k == "imdb" 
end

This is ugly but it works. Now I'm sure there is a nicer way to write all those or's in the 3rd line but that's another question :)

Squeel syntax for field = value OR field IS NULL in a loop

2 Answers2