[Feature][ActiveRecord] `where.or` query method

[Vicente Romero Calero]

Consider the following simple query:

SELECT * FROM authors WHERE first_name = 'John' OR last_name = 'Smith'

With ActiveRecord you can write it like this:

Author.where(first_name: 'John').or(Author.where(last_name: 'Smith'))

It’s annoying that you need to pass in a whole relation to the `or` function. It would be nicer to have something shorter and cleaner. So, I propose to add a `where.or` query method, similar to the existing `where.not`. This new method would take params in the same way `where` does, but instead of using AND as a keyword to join the predicates, it would use OR. The query above would be built as follows:

Author.where.or(first_name: 'John', last_name: 'Smith')

I’m not sure if I’m missing something fundamental that makes this feature difficult to implement. I’ve actually implemented a working version and I’d be happy to push a PR if the feedback is positive.

Besides the technical challenges that may arise, do you think it’s a useful feature?

Thanks!

[Mladen Ilic]

I agree, current approach is not the most convenient one. Have you considered how would you handle more complex queries, ones where each OR operand is more than a single equality comparison? 

For example:

SELECT * FROM authors WHERE (first_name = 'John' OR last_name = 'Smith') OR (company LIKE '%rails' AND dob BETWEEN '...' AND '...') 

Best,

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-co...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-core/125f930d-73ef-4dab-b353-77a8f2e4280a%40googlegroups.com.

[Rolandas Barysas]

I think the idea of making `or` simpler is welcome, but I'm not fond of this implementation. Passing multiple parameters implies that we're using them together (like `.not()`), but in this case it implicitly uses parameters separately (using as an or, that is).

I think something like this would be more readable:

Author.where(first_name: 'John').or(last_name: 'Smith')

Right now in your particular case you can write like this, though I assume you're trying to avoid using SQL at all:

Author.where('first_name = ? OR last_name = ?', 'John', 'Smith')

[Kevin Deisz]

For more complex queries, so syntactic sugar can make this a little more palatable:

Author.some.complex.scopes.then { |relation| relation.where(first_name: 'John').or(relation.where(last_name: 'Smith')) }.more.complex.scopes

Even better is just extracting that to its own named scope,

scope :named, ->(first_name, last_name) { where(first_name: first_name).or(where(last_name: 'Smith')) }

Author.some.complex.scopes.named(first_name, last_name).more.complex.scopes

I get that that doesn't solve your concern with the API, but it makes it more readable for sure.

To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-core/3cb23264-3d5a-4b27-b6b4-b541a08e8b99%40www.fastmail.com.

--

Kevin D. Deisz

CTO, CultureHQ

[Andrew Kaspick]

I usually resort to using Arel for more complex queries; not as straight forward at first, but very powerful I find.

To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-core/CALzDYJ0hufhkNgEQvwDYd1nA9LBzrnU_4tWK%2B2KPpd80xi_b9A%40mail.gmail.com.

[T.J. Schuck]

> I usually resort to using Arel for more complex queries; not as straight forward at first, but very powerful I find.

Please note that Arel is a part of Rails' private API, so its direct usage is not encouraged, since it could change in breaking ways at any time.

(See the Rails docs on what constitutes private/public API, since it's not as simple as `private` scope: https://guides.rubyonrails.org/api_documentation_guidelines.html#method-visibility — Arel is even used for the example of "don't use this private thing".)

To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-core/CAEUKiYHAX6NaAVVawBonnqEEWfUyyfjxUXDTz_pM09OmOBRoHg%40mail.gmail.com.

[Vicente Romero Calero]

My initial thought was more around having a new query method (where.or) to solve the specific problem I explained on my first post. However, I've been thinking about it and I now believe that it's worth trying to tackle the whole "OR problem".

The problem

Creating where clauses more complicated than simple chained AND conditions could be challenging with ActiveRecord. If you need to add an OR condition to a query, you need to use the or method which expects a Relation object as parameter. That means that you have to specify the model you are using and call where again. That's not great. This problem gets multiplied if you have a query with multiple OR conditions as you have to chain them by using or.

My wish list

The missing functionality exposed on the previous point gets captured in the next list of 2 wishes:

1. I want to be able to choose the keyword (AND or OR) to be used to chain a new condition (or series of conditions) with any previous ones.
2. I want to be able to choose the keyword (AND or OR) to be used to join a list of conditions.

My proposal

I propose the following new methods and variations of the where method to solve the problem:

• where.and() to chain with AND.
• where.or() to chain with OR.
• where(:and, hash_conditions) to join hash_conditions with AND.
• where(:or, hash_conditions) to join hash_conditions with OR.

Obviously, this proposal should't break any of the existing behaviour and the where method should still work in the way it does now. Specifically, where.and is an alias of where, and where(:and, hash_conditions) should work exactly the same as where(hash_conditions).

What are your thoughts on this?

Thanks!

To unsubscribe from this group and stop receiving emails from it, send an email to rubyonra...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-core/125f930d-73ef-4dab-b353-77a8f2e4280a%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group.

To unsubscribe from this group and stop receiving emails from it, send an email to rubyonra...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-core/3cb23264-3d5a-4b27-b6b4-b541a08e8b99%40www.fastmail.com.

--

Kevin D. Deisz

CTO, CultureHQ

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group.

To unsubscribe from this group and stop receiving emails from it, send an email to rubyonra...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-core/CALzDYJ0hufhkNgEQvwDYd1nA9LBzrnU_4tWK%2B2KPpd80xi_b9A%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group.

To unsubscribe from this group and stop receiving emails from it, send an email to rubyonra...@googlegroups.com.