Open vladfaust opened 5 years ago
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)
where.null .gt .lt
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 >= ?))
An easier way to do LIKE
queries would also be helpful.
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
Inspired by https://knexjs.org/