Ransack: Handle `not_eq` with null rows

[avit]

Given this query in raw SQL:

SELECT x FROM records WHERE x != 'abcd'

For records where the x value is null, SQL simply ignores the row. If I really want to find _all_ rows where the value is not "abcd", I need to specify:

SELECT x FROM records WHERE x != 'abcd' OR x IS NULL

From the front-end, having to add the "or null" in the form UI is not desirable. Could Ransack consider adding "or null" as an option for the negative predicates, or is there a better workaround?

I tried to wrap my head around possibly adding a replacement not_eq predicate to my Ransack::Configuration. I got close with this:

config.add_predicate("not_eq", arel_predicate: "not_eq_any", compounds: false, formatter: proc { |v| [nil, v] })

But this needs to flip the "nil" to "IS NOT NULL", which I don't think is possible in a not_eq_any which maps everything to !=, including various ways of defining "NOT NULL".

[avit]

I have a pull request to rails/arel#165 to handle NOT correctly.

Then, I would like to move not_eq and does_not_match from AREL_PREDICATES and add them as derived predicates to handle NULLs:

['not_eq', {:arel_predicate => 'eq_any', :formatter => proc { |v| [Arel::Nodes::Not.new(v), nil] }}]
['does_not_match', {:arel_predicate => 'matches_any', :formatter => proc { |v| [Arel::Nodes::Not.new(v), nil] }}]

Any thoughts on this approach?

[radar]

I like the idea. :+1:

[rilian]

+1

not_eq: true does not work on columns with true/false/null :(

[mkralla11]

+1 definitely.

[avit]

Still waiting on arel. @ernie or @tenderlove need to review rails/arel#165

[mkralla11]

This may not be the correct place to ask this, but @avit , is there a arel predicate that returns ALL values in a given table, including null? I'm doing some pretty unique customization to the reporting functionality of our sites, and one of the features I am building needs to include creating a custom predicate via the config.add_predicate Ransack initializer that returns ALL values in the a given table. I can't use the 'match' predicate with a formatter that contains '%' because it will return all values that have any value, whereas I also need null values. I've also attempted a couple other visions with no luck: :arel_predicate => 'eq_any', :formatter => proc {|v| [nil, true, false]}, etc, but this does not work either. Maybe I'm missing something obvious that you could point out in order to config.add_predicate that will return all values in my table, including nil.

[rilian]

@mkralla11 is it possible for you that if the case when you need all, just perform

foos = Foo.all

?

[avit]

@mkralla11 Ideally you'd just completely remove the condition for that attribute so there is no "where..."

[ernie]

Yup. The "predicate" for matching anything in a column is to not have a predicate against that column at all.

[mkralla11]

Yes, I guess I need to explain myself and my overall intentions. In ransack, if you do a dynamic search form, it only builds the filter fields that are being 'filtered' or 'used.' That is a big issue in my case for my specific functionality, because in order to have that specific filter field 'show-up' after the search is made, you need to have had a predicate selected and a value in the filter text_field. You may ask yourself, "but why do you want the filter field to even show up if you are not going to actually 'filter' anything out?'. My answer to that is that I am adding additional functionality to each 'filter-field' to include a check-box that is intended to help display only certain columns of the given db table depending on whether it was checked or not. I do recognize that ransack was not intended to help 'display' your results, it really doesn't care how you do that. But with the functionality I am adding (and I am very close to completing) I need a actual predicate that returns 'all', so that when a search is executed, that filter field with the 'return-all' predicate is actually 'built' in my view. I hope this all makes sense. And dissapointing, a simple foos = Foo.all is not what I'm looking for, but thanks @rillian .

[mkralla11]

Also, if you have understood my goals and intentions, you probably have recognized why I am pushing for the not_eq arel correction to return null values as well. This would work in my case particularly because all null values would automatically be returned with this pred, and I could :format the incoming value to be some bogus string that I know will not eq anything in my db. Essentially, all values are returned.

[ernie]

Arel should not be changing the meaning of a predicate in that way, I don't think. Arel is about representing SQL abstract syntax trees and converting them to identical SQL. While IS NULL and IS NOT NULL are kind of special cases WRT equality I'm not sure the "correction" you're pushing for would really be a correction to Arel.

[avit]

@ernie, are you responding to @mkralla11's question or the original ticket? We got a little derailed here.

Are you in disagreement about changing Ransack's definition of not_eq and does_not_match predicates to include NULLs, as shown in my comment https://github.com/ernie/ransack/issues/123#issuecomment-14307700, above?

Either way I think the rails/arel#165 is valid, it doesn't change the meaning of any predicates there.

[mkralla11]

Right, I understand, the intended purposes of the not_eq changes are simple to all include null values. I understand that I am trying to abuse a side-effect/alternative-use for the not_eq change. And I agree, the real solution to my particular case would be to change/extend ransack, not arel, to include the ability to config.add_predicate :arel_predicate => ' ', (meaning you did not specify a predicate) and then ransack would take that empty string with an if statement that would then, in turn, not use an arel predicate, and simple do a Foo.all. So really, my goal was not to make more work for myself or anyone else, and simply use the side-effect of the :not_eq that was already in the process of being built. Kill two birds with one code, if you will.

[ernie]

@avit Sorry, I got confused by the "not_eq arel correction" line.

[markedmondson]

@avit, your new derived predicates need one for not_in too.

[avit]

The maintainers seem to be against handling double-negatives upstream in rails/arel#165. Not sure where that leaves this issue. @ernie, do you have any input on a good solution here?

[ernie]

@avit I know I wrote Ransack, but it's been ages since I used it. :)

However, when you add a predicate, Ransack will automatically create the _any variant. See https://github.com/ernie/ransack/blob/master/lib/ransack/configuration.rb#L27-L35

Have you tried that, instead of trying to create the _any one yourself?

[avit]

_any is just for compounds, and would render a different UI with multiple inputs for the user. Compounds aren't what we want for a simple "x does not equal y" form input. It's only when the {p: "not_eq"} is passed from Ransack to Arel that we need to derive it, so it adds "OR y IS NULL".

Ransack defined two lists for translating to Arel:

• AREL_PREDICATES are passed directly to Arel, for simple values
• DERIVED_PREDICATES are an intermediate step for translating Ransack's special ones into Arel

https://github.com/ernie/ransack/blob/master/lib/ransack/constants.rb#L6-L21

Here's how I've monkey patched these to override not_eq so it adds "OR NULL" before passing it to Arel:

https://gist.github.com/avit/6646003

(Works great.)

On the right hand side is an array of two values: one a normal value, and one negated using Not. Currently, it's impossible to pass these mixed values to Arel since it does the wrong thing with the negated one. The Arel patch just lets us normalize eq(not(x)) into not_eq(x), which fixes it.

Without adding that feature to Arel, the only solution I see here is to tear out the 'arel_predicate' field since we can't dispatch to it blindly, avoid using Arel's *_(any|all) predicates since they don't work with mixed values, and rework Ransack to do the individual node construction with appropriate Not node switching, finally joined with .reduce(&:or).

[ernie]

Ugh. Yeah. This is just a bad idea. :/

[ernie]

To elaborate: the kinds of things folks try to coerce Ransack into doing were not things I designed it for. I'd say if you want to do this kind of stuff you're better off monkey patching for your specific app. :(

[avit]

Since Ransack deals with UI, it's surprising when users pick "does not equal", and it doesn't find all the records where the value is absent. Certainly, Arel shouldn't change this meaning in SQL, but I would argue that it's the expected case when dealing with forms.

Anyway, let's disagree about "bad ideas" amicably. Hooray for monkey patches, this is too small for a proper fork. :smile:

[ncri]

Just stumbled upon this issue too. Intuitively, when I say "give me all records with col_x not eq xyz" I should obviously get all records where col_x is not eq xyz _including null records_. I didn't ask for "give me all records with col_x not eq xyz and col_x not eq null".

Thanks for the patch @avit, but it doesn't work for me (dropped the code from the gist in an initializer).

[Genkilabs]

As an aside, I had a need to work with a legacy DB that used nil as a valid value. This meant the client wanted checkboxes in the search which included NULL as a checkable option. Problem is, in_any will resolve to :some_field = NULL rather than :some_field IS NULL, if you include NULL in the options.

My solution was to add an option into the list of checkboxes on the form called with the value 'nil' as a String, then use this custom predicate:

config.add_predicate 'eq_any_or_nil',
                         :arel_predicate => 'eq_any',
                         :formatter => proc {|val|
                            "nil" == val ? nil : val
                         },
                         :validator => proc {|val|
                            val.present? && !val.blank?
                         },
                         :compounds => false,
                         :wants_array => true

While it's not the answer to this issue exactly, I hope it helps others who arrive here.

[avit]

I think this was addressed in #370. I should confirm before closing.

[ajporterfield]

An alternate solution for this is to monkey patch Arel::Predications in an arel.rb initializer with something like...

module Arel
  module Predications
    def not_eq_or_null(other)
      left = not_eq(other)
      right = eq(nil)
      left.or(right)
    end
  end
end

Then add the new predicate in a ransack.rb initializer.

Ransack.configure do |config|
  config.add_predicate 'not_eq_or_null', arel_predicate: 'not_eq_or_null'
end

That is producing the correct SQL for me.

2.1.6 :001 > User.search(last_name_not_eq_or_null: 'Smith').result.to_sql
=> "SELECT \"users\".* FROM \"users\" WHERE (\"users\".\"last_name\" != 'Smith' OR \"users\".\"last_name\" IS NULL)" 

[seanfcarroll]

Closed due to inactivity