module Lustra::SQL::Query::Where

Overview

Feature WHERE clause building. each call to where method stack where clause. Theses clauses are then combined together using the AND operator. Therefore, query.where("a").where("b") will return a AND b

Direct including types

Defined in:

lustra/sql/query/where.cr

Instance Method Summary

Instance Method Detail

def clear_wheres #

Clear all the where clauses and return self


[View source]
def not(&) #

Build SQL where NOT condition using the Expression engine. This is equivalent to wrapping the condition in NOT(...).

query.where.not { id == 1 }        # WHERE NOT (id = 1)
query.where.not { active == true } # WHERE NOT (active = true)

[View source]
def not(conditions : NamedTuple | Hash(String, Lustra::SQL::Any)) #

Build SQL where NOT condition using a NamedTuple or Hash.


[View source]
def not(template : String) #

Build custom SQL where NOT condition. Beware of SQL injections!

query.where.not("id = 1") # WHERE NOT (id = 1)

[View source]
def not(template : String, *args) #

Build SQL where NOT condition using a template string with positional parameters.

query.where.not("id = ?", 1) # WHERE NOT (id = 1)

[View source]
def not(template : String, **tuple) #

Build SQL where NOT condition using a template string.

query.where.not("id = :id", id: 1) # WHERE NOT (id = 1)

[View source]
def not(**tuple) #

Build SQL where NOT condition using a NamedTuple. This will negate each condition in the tuple.

query.where.not({active: true})  # WHERE NOT (active = true)
query.where.not({id: [1, 2, 3]}) # WHERE NOT (id IN (1,2,3))

[View source]
def or(node : Lustra::Expression::Node) #

Build SQL where OR condition using the Expression engine.

query.where { id == 1 }.or { id == 2 } # WHERE (id = 1) OR (id = 2)
query.where.or { active == true }      # WHERE active = true

[View source]
def or(&) #

Build SQL where OR condition using the Expression engine.

query.where { id == 1 }.or { id == 2 }

[View source]
def or(conditions : NamedTuple | Hash(String, Lustra::SQL::Any)) #

Build SQL where OR condition using a NamedTuple or Hash.


[View source]
def or(template : String, *args) #

Build SQL where OR condition using a template string with positional parameters.

query.where { id == 1 }.or("status = ?", "active")

[View source]
def or(template : String, **tuple) #

Build SQL where OR condition using a template string.

query.where { id == 1 }.or("status = :status", status: "active")

[View source]
def or(**tuple) #

Build SQL where OR condition using a NamedTuple.

query.where { id == 1 }.or(status: "active") # WHERE (id = 1) OR (status = 'active')

[View source]
def where(node : Lustra::Expression::Node) #

Build SQL #where condition using a Lustra::Expression::Node

query.where(Lustra::Expression::Node::InArray.new("id", ['1', '2', '3', '4']))
# Note: in this example, InArray node use unsafe strings

If useful for moving a where clause from a request to another one:

query1.where { a == b } # WHERE a = b
query2.where(query1.wheres[0]) # WHERE a = b

[View source]
def where(&) #

Build SQL #where condition using the Expression engine.

query.where { id == 1 }

[View source]
def where(conditions : NamedTuple | Hash(String, Lustra::SQL::Any)) #

Build SQL #where condition using a NamedTuple. this will use:

  • the = operator if compared with a literal
query.where({keyword: "hello"}) # WHERE keyword = 'hello'
  • the IN operator if compared with an array:
query.where({x: [1, 2]}) # WHERE x in (1,2)
  • the >= and <= | < if compared with a range:
query.where({x: (1..4)})  # WHERE x >= 1 AND x <= 4
query.where({x: (1...4)}) # WHERE x >= 1 AND x < 4
  • You also can put another select query as argument:
query.where({x: another_select}) # WHERE x IN (SELECT ... )

[View source]
def where(template : String) #

Build custom SQL #where beware of SQL injections!

where("ADD_SOME_DANGEROUS_SQL_HERE") # WHERE ADD_SOME_DANGEROUS_SQL_HERE

[View source]
def where(template : String, *args) #

Build SQL #where condition using a template string and interpolating ? characters with parameters given in a tuple or array.

where("x = ? OR y = ?", 1, "l'eau") # WHERE x = 1 OR y = 'l''eau'

Raise error if there's not enough parameters to cover all the ? placeholders


[View source]
def where(template : String, **tuple) #

Build SQL #where interpolating :keyword with the NamedTuple passed in argument.

where("id = :id OR date >= :start", id: 1, start: 1.day.ago)
# WHERE id = 1 AND date >= '201x-xx-xx ...'

[View source]
def where(**tuple) #

[View source]