ActiveRecord conditionals for variable existence inside a where block

Question

I had this bit of code using Squeel which worked perfectly:

.where {
  [
    entry[:imdb].presence && imdb == entry[:imdb],
    entry[:asin].presence && asin == entry[:asin],
    entry[:upc].presence  && dvd_upc.upc == entry[:upc]
  ].compact.reduce(:|)
}

This would only create the query for the column if the entry[:variable_name] was present, otherwise it would ignore it, very handy. Produced a query like the following:

"SELECT * FROM `table`  WHERE ((`table`.`imdb` = 3026824 OR `table`.`asin` = 'B00E5PHTR8'))"

The great thing about this block is that I don't have to worry if the variable exists or not, if there is only 1 of the variables, the query will not use an OR statement, etc. This is a lot harder to write with regular conditionals without making a big mess.

Also note that the whole point of this query is to not use column = NULL when the variable is not present. Doing a search for column = NULL will retrieve a bunch of useless and wrong hits.

However, Squeel is no longer maintained and has all sorts of failing issues with ActiveRecord 4.1 (and Rails 4.1 by extension). So the only choice I have if I want to use AR 4.1.1 and above is to get rid of all the squeel queries I had.

My question is, is this possible to do with an AR .where block? I can't seem to find an answer other than no, it's not possible.

score 1 · Answer 1 · answered May 12 '14 at 09:15

In the end, I came up with this:

unique_query = main_query.where(
      [
          entry[:imdb].presence && "imdb = '#{entry[:imdb]}'",
          entry[:asin].presence && "asin = '#{entry[:asin]}'",
          entry[:upc].presence &&  "relation_table.upc = #{entry[:upc]}"
      ].compact.join(' or ')
  )

It's not great because I've got the variable inside the query which is open to SQL injection but I couldn't come up with a better way. At least the source of the query is controlled (it's from an app) so the risk of SQL injection is minimal.

Again, it's not a great solution, but it's the best I could come up with. If anyone has a better solution I'd love to hear it.

score -2 · Answer 2 · answered May 08 '14 at 10:16

-2

Assuming you have Table model and corresponding 'table' in db, above query in SQL can be achieved in rails by -

Table.where('(imdb = ?) OR (asin = ?) OR (upc = ?)', entry[:imdb], entry[:asin], entry[:upc])

answered May 08 '14 at 10:16

Kundan Pandit

412
6
17

That is incorrect. Your query would produce this: `"SELECT table.* FROM table WHERE ((imdb = '3026824') OR (asin = 'B00E5PHTR8') OR (upc = NULL))"` note that `upc = NULL` is **not** the same as not searching for upc! Same with the other variables. The whole point is not to use `column = NULL` – kakubei May 08 '14 at 10:31
I think the purpose that rows with NULL values should not be retrieved, can be served with following if not exactly: – Kundan Pandit May 09 '14 at 13:42
table = Table.arel_table Table.where(table[:imdb].eq(entry[:imdb]).or(table[:asin].eq(entry[:asin]).or(table[:upc].eq(entry[:upc])))).having(table[:imdb].not_eq(nil).and(table[:asin].not_eq(nil).and(table[:upc].not_eq(nil)))) – Kundan Pandit May 09 '14 at 13:51
Thanks, but that's really hard to read and produces a very cumbersome query like so: `SELECT `table`.* FROM `table` WHERE ((`table`.`imdb` IS NULL OR `table`.`asin` = '6305736650')) HAVING `table`.`imdb` IS NOT NULL;` – kakubei May 12 '14 at 09:09
It actually results in a bad query, returning a number of useless possibilities because you first search for imdb = null then you negate that search. Runing an `explain` on it in SQL shows the problem: `rows: 167334`. – kakubei May 12 '14 at 09:15

ActiveRecord conditionals for variable existence inside a where block

2 Answers2