Enrich with `where_null`, `where_in` etc. methods

[vladfaust]

Inspired by https://knexjs.org/

[vladfaust]

Proposed methods:

where_in(field: Enumerable)
where_null(field)
where_gt(field: Comparable)
where_gte(field: Comparable)
where_lt(field: Comparable)
where_lte(field: Comparable)

Also remove and_*, not_* and or_* methods and replace with .and, .or, .not.

User
  .where_in(status: [:active, :pending])
  .and.where_gte(:age, 18)
  .or.not.where_null(:allow_adult_content)

SELECT * FROM users 
WHERE (status IN (?, ?)) 
AND (WHERE age > 18) 
OR NOT (WHERE allow_adult_content IS NULL)

[pynixwang]

where.null .gt .lt

[vladfaust]

Agreed.

.where(id: 42) # id = 42
.where # For chaining

.not(id: 42) # NOT id = 42
.not # For chaining
.not(&block : self -> _) # See below

.and(id: 42) # (AND) id = 42
.and # For chaining
.and(&block : self -> _) # See below

.or(id: 42) # (OR) id = 42
.or # For chaining
.or(&block : self -> _) # See below

.is_null(:foo) # foo IS NULL
.is_not_null(:foo) # foo IS NOT NULL

.eq(id: 42) # id = 42 (for convenience)
.in(id: {1, 2}) # id IN ?, ?
.gt(id: 1) # id > 1
.gte(id: 1) # id >= 1
.lt(id: 100) # id < 100
.lte(id: 100) # id <= 100

Should add nested conditions to wrap them with parenthesis:

.or do |x|
  typeof(x) # => self
end

Examples:

User.where.is_not_null(:bio)

WHERE (bio IS NOT NULL)

User
  .where.in(status: [:pending, :activated])
  .or(&.is_null(:violation).and.gte(:reputation, 100))

WHERE (status IN ?, ?) OR ((violation IS NULL) AND (reputation >= ?))

[Blacksmoke16]

An easier way to do LIKE queries would also be helpful.

[vladfaust]

How should we deal with such a bug?

User.where.join(posts: true) # Forgot to put the continuation after .where

A. Raise in runtime upon building the query, something like Cannot append empty WHERE clause B. Ignore .where call and just build the query (assuming there is anything else in the callchain) C. Return to https://github.com/onyxframework/sql/issues/84#issuecomment-480494802 proposal