Implement Ruby on Rail's hash conditions

[ipundit]

Some of RoR's hash conditions are implemented, others are not, and others are implemented differently. This issue is to decide on a consistent API for replicating these features.

From: https://guides.rubyonrails.org/active_record_querying.html#hash-conditions

1. Range conditions is currently not implemented
   Book.where(created_at: (Time.now.midnight - 1.day)..Time.now.midnight)
   generates
   SELECT * FROM books WHERE (books.created_at BETWEEN '2008-12-21 00:00:00' AND '2008-12-22 00:00:00')

and
Book.where(created_at: (Time.now.midnight - 1.day)..)
generates
SELECT * FROM books WHERE books.created_at >= '2008-12-21 00:00:00'

2. Not conditions are implemented differently
RoR: Customer.where.not(orders_count: [1,3,5])
ActiveRecord:

Customer::not(orders_count: [1,3,5])

This is fine, unless we want not to support the negation of non-where clauses like:

Customer::having()->not(orders_count: [1,3,5])

which is not something RoR supports. Decided that it's not worth supporting.

3. Nullable fields are not tested. I'm not sure if this works:

Customer.create!(nullable_contry: nil)
Customer.where.not(nullable_country: "UK")
=> []
# But
Customer.create!(nullable_contry: "UK")
Customer.where.not(nullable_country: nil)
=> [#<Customer id: 2, nullable_contry: "UK">]

4. Or is not yet implemented and neither is and:
   RoR: Customer.where(last_name: 'Smith').or(Customer.where(orders_count: [1,3,5]))
   RoR: Customer.where(last_name: 'Smith').and(Customer.where(orders_count: [1,3,5]))

But if we keep the API breakage theme with not(), wouldn't this be better?

Customer::where(['last_name' => 'Smith'])->or(['orders_count' => [1,3,5])

But what about?

Customer::where(A)->or(B)->and(C)

is this A or (B and C) or (A or B) and C?

The advantage of RoR's implementation of or is that you can remove the ambiguity:

A or (B and C): Customer.where(A)->or(Customer::where(B)->where(C))
(A or B) and C: Customer.where(A)->or(Customer::where(B))->where(C)

But if you want to be consistent which RoR is not, and be able to negate OR'd and AND'ed clauses, then it should be:

Customer::not(Customer::where(A))

so you can do:

Customer::not(Customer::where(A)->or(Customer::where(B)->where(C)))

and perhaps:

Customer::not(Customer::having(A)->or(Customer::having(B)->having(C)))

if it's worth implementing. It's not.

[shmax]

Way to deep dive! I'll also mention that we're also missing the ability to pass another Relation instance to where (and maybe some others?).

My vote is to revisit this stuff post 2.0. What do you say?

[ipundit]

Decide on what to do with not() now so that we don't have to deprecate it in the future if we need to change the implementation. I think we should solidify the spec now, and defer implementation after 2.0, unless I get too itchy and just do it.

[shmax]

Sure, sounds good. Feel free to open a PR.

[ipundit]

But what is the spec for not()?

Customer::not(Relation) or Customer:not(string) as it is now? And what about or and and?

And does not() only affect where, or having too?

[shmax]

I don't know. Did you ever get around to getting set up with a local rails project and a debugger? Easiest way to answer those kinds of questions might be to tinker around...

[shmax]

If you're pretty sure not is off-spec, then we can just comment it out for new and deal with it post-2.0, as you suggested. Wouldn't bother me in the slightest.

[shmax]

And what about or and and?

Does Rails really have that? I wonder how they deal with associativity and all that kind of thing.

[ipundit]

This is Rails: Customer.where.not(orders_count: [1, 3, 5])
It's weird. It's the only place where there's an empty where()

This is ActiveRecord:

Customer::not(orders_count: [1,3,5])

So it's different than rails, but it makes more sense than requiring an empty where(), especially since or() and and() are implemented in ways that don't require an empty where().

So the way RoR implements not, or, and and are mutually inconsistent with each other, and then ActiveRecord does something again differently with it's implementation of not(). I think this is a case where it makes sense to break from RoR to at least be internally consistent. But if you want to slavishly keep to the RoR spec as much as possible, then the current implementation of not() is off spec because it's not preceded by an empty where()

[shmax]

That is weird. There's nothing semantically wrong with an empty where statement, but I don't know why they require one to precede not. Must be some quirk of their implementation. Anyway, I really don't have any opinion on this. I threw not in there as a lark because I thought it would be an easy win, but apparently not. If you want to rejigger it, by all means do so. We're still waiting on array shape comments before we can release 2.0, so I think you have time. I suspect and and or are going to be a nasty can of worms and I have no plans to work on them, but if you're interested there may be time for that, as well.

[ipundit]

I've implemented User::not(Relation) while keeping the existing support for not() intact. This means the following now works:

Author::not(Author::where('author_id = (?)', 4)->not("name = 'Bill Clinton'));

which generates WHERE !((author_id = 4) AND !(name = 'Bill Clinton'))

There is no need for an empty where() in this implementation.

Also, and() works too:

$model = Author::where(['name' => 'Bill Clinton'])->and("mixedCaseField = 'Bill'")->find(3);
$this->assertEquals('Bill Clinton', $model->name);

All that's left is to implement or(string|array<string>|Relation) in the same way. This is a consistent API now. or() is harder than and() Defer to after 2.0.

[shmax]

Closing due to inactivity, but feel free to reopen.