CODEWITHSUNDEEP
rubyransack
David Cook
David Cook

Reputation: 563

Search for a null value using Ransack 'eq' predicate

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>

Upvotes: 6

Views: 8715

Answers (2)

Kurt Peniket
Kurt Peniket

Reputation: 21

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

Upvotes: 0

alexey
alexey

Reputation: 137

Try this in console:

Spree::Order.search(order_cycle_id_null: true)

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

Upvotes: 9

Related Questions