Search for a null value using Ransack 'eq' predicate

Question

Using the 'eq' predicate with a blank value, Ransack will cancel out that predicate. Which is obviously handy to have an "All" option in your select.

But what if I want to add an option in my <select> for null values too? In order words how to produce the SQL query SELECT * FROM spree_orders WHERE order_cycle_id = NULL using the 'eq' predicate.

My test code (with results) is below. What I would like is to filter out the Orders where order_cycle_id == nil

Spree::Order.search(order_cycle_id_eq: nil).result.map(&:order_cycle_id)
Spree::Order Load (2.2ms)  SELECT "spree_orders".* FROM "spree_orders" 
 => [nil, 1, nil, nil, nil, nil, nil, nil, 1, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, 4, nil, 4, nil, nil, nil, nil] 

The second option is what I'd like to add to my select. I've tried 'null', 'nil', nil, etc..

<select id="q_order_cycle_id_eq" name="q[order_cycle_id_eq]">
  <option value="">All</option>
  <option value="nil">No Order Cycle</option>
  <option value="1">Order Cycle 1</option>
  <option value="2">Order Cycle 2</option>
</select>

Fleshings

Are you asking what to put instead of nil in Spree::Order.search(order_cycle_id_eq: nil) or are you asking what to put instead of ??? in <option value="???">? They don't necessarily have to be the same. Sure, it would be nice, but you can always have <option value="none"> and then have if params[order_cycle_id_eq] == 'none' and pass something else. I don't know what Ransack is though. – Burch

Both: the option value would be passed through to the value in .search(order_cycle_id_eq: value). I've updated my question to consistently use nil as the example. – Fleshings

Answer 1

Try this in console:

Spree::Order.search(order_cycle_id_null: true)

In html view set and then in your controller change ransack query

Aeroplane

That's how I ended up doing a work around: something like "if params[:order_cycle_id] == 'null' then order_cycle_id_null: true" – Fleshings

Answer 2

I know this is an old question but I came across a similar issue and found in the docs that you can actually do an IS NULL query, which helped in my case.

User.ransack(first_name_null: 1).result.to_sql

>> SELECT "users".* FROM "users"  WHERE "users"."first_name" IS NULL

Dyanna