class Clear::Expression
- Clear::Expression
- Reference
- Object
Overview
Clear's Expression engine
The goal of this module is to offer the most natural way to write down your query in crystal.
If you're familiar with Sequel on Ruby, then here you have !
Instead of writing:
model_collection.where("created_at BETWEEN ? AND ?", 1.day.ago, DateTime.local)
You can write:
model_collection.where { created_at.between(1.day.ago, DateTime.local) }
or even:
model_collection.where { created_at.in?(1.day.ago..DateTime.local) }
(Note for the later, it will generate created_at > 1.day.ago AND created_at < DateTime.local
)
Limitations
Due to the use of missing_method
macro, some case can be confusing.
Existing local variable / instance method
id = 1
model_collection.where { id > 100 } # Will raise an error, because the expression is resolved by Crystal !
# Should be:
id = 1
model_collection.where { var("id") > 100 } # Will works
Usage of AND / OR
And/Or can be used using the bitwises operators &
and |
.
Due to the impossibility to reuse ||
and &&
, beware the operator precendance
rules are changed.
# v-- This below will not works, as we cannot redefine the `or` operator
model_collection.where { first_name == "yacine" || last_name == "petitprez" }
# v-- This will works, but beware of the parenthesis between each terms, as `|` is prioritary on `==`
model.collection.where { (firt_name == "yacine") | (last_name == "petitprez") }
# ^-- ... WHERE first_name = 'yacine' OR last_name == ''
Defined in:
clear/expression/expression.crConstant Summary
-
DATABASE_DATE_FORMAT =
"%Y-%m-%d"
-
DATABASE_DATE_TIME_FORMAT =
"%Y-%m-%d %H:%M:%S.%L %:z"
Class Method Summary
-
.[](arg)
A fast way to call
self.safe_literal
See.safe_literal(x : _)
-
.ensure_node!(any)
This method will raise error on compilation if discovered in the code.
-
.raw(x : String, *args)
In case the name of the variable is a reserved word (e.g.
-
.raw(__template : String, **tuple)
In case the name of the variable is a reserved word (e.g.
-
.raw_enum(x : String, args)
See
self.raw
Can pass an array to this version -
.safe_literal(x : Enumerable(AvailableLiteral)) : Enumerable(String)
Transform multiple objects into a string which is SQL-Injection safe.
-
.safe_literal(x : Time, date : Bool = false) : String
Safe literal of a time return a string representation of time in the format understood by postgresql.
-
.safe_literal(x : _) : String
Sanitize an object and return a
String
representation of itself which is proofed against SQL injections. -
.unsafe(x)
Return unsafe string injected to the query.
-
.where(&) : Node
Return a node of the expression engine This node can then be combined with others node in case of chain request creation
where {...}.where {...}
through the chaining engine
Instance Method Summary
-
#not(x : Node)
NOT
operator -
#op(a : Node | AvailableLiteral, b : Node | AvailableLiteral, op : String)
Because many postgresql operators are not transcriptable in Crystal lang, this helpers helps to write the expressions:
-
#raw(x : String, *args)
In case the name of the variable is a reserved word (e.g.
-
#raw(__template : String, **tuple)
In case the name of the variable is a reserved word (e.g.
-
#var(*parts)
Use var to create expression of variable.
Class Method Detail
This method will raise error on compilation if discovered in the code. This allow to avoid issues like this one at compile type:
id = 1
# ... and later
User.query.where { id == 2 }
In this case, the local var id will be evaluated in the expression engine. leading to buggy code.
Having this method prevent the code to compile.
To be able to pass a literal or values other than node, please use #raw
method.
In case the name of the variable is a reserved word (e.g. #not
, #var
, #raw
)
or in case of a complex piece of computation impossible to express with the expression engine
(e.g. usage of functions) you can use then raw to pass the String.
BE AWARE than the String is pasted AS-IS and can lead to SQL injection if not used properly.
having { raw("COUNT(*)") > 5 } # SELECT ... FROM ... HAVING COUNT(*) > 5
where { raw("func(?, ?) = ?", a, b, c) } # SELECT ... FROM ... WHERE function(a, b) = c
In case the name of the variable is a reserved word (e.g. #not
, #var
, #raw
)
or in case of a complex piece of computation impossible to express with the expression engine
(e.g. usage of functions) you can use then raw to pass the String.
BE AWARE than the String is pasted AS-IS and can lead to SQL injection if not used properly.
having { raw("COUNT(*)") > 5 } # SELECT ... FROM ... HAVING COUNT(*) > 5
where { raw("func(:a, :b) = :c", a: a, b: b, c: c) } # SELECT ... FROM ... WHERE function(a, b) = c
Transform multiple objects into a string which is SQL-Injection safe.
Safe literal of a time return a string representation of time in the format understood by postgresql.
If the optional parameter date
is passed, the time is truncated and only the date is passed:
Example
Clear::Expression[Time.local] # < "2017-04-03 23:04:43.234 +08:00"
Clear::Expression[Time.local, date: true] # < "2017-04-03"
Sanitize an object and return a String
representation of itself which is proofed against SQL injections.
Return unsafe string injected to the query.
can be used for example in insert
query building
Return a node of the expression engine
This node can then be combined with others node
in case of chain request creation where {...}.where {...}
through the chaining engine
Instance Method Detail
NOT
operator
Return an logically reversed version of the contained Node
Example
Clear::Expression.where { not(a == b) }.resolve # >> "WHERE NOT( a = b )
Because many postgresql operators are not transcriptable in Crystal lang, this helpers helps to write the expressions:
where { op(jsonb_field, "something", "?") } # << Return "jsonb_field ? 'something'"
In case the name of the variable is a reserved word (e.g. #not
, #var
, #raw
)
or in case of a complex piece of computation impossible to express with the expression engine
(e.g. usage of functions) you can use then raw to pass the String.
BE AWARE than the String is pasted AS-IS and can lead to SQL injection if not used properly.
having { raw("COUNT(*)") > 5 } # SELECT ... FROM ... HAVING COUNT(*) > 5
where { raw("func(?, ?) = ?", a, b, c) } # SELECT ... FROM ... WHERE function(a, b) = c
In case the name of the variable is a reserved word (e.g. #not
, #var
, #raw
)
or in case of a complex piece of computation impossible to express with the expression engine
(e.g. usage of functions) you can use then raw to pass the String.
BE AWARE than the String is pasted AS-IS and can lead to SQL injection if not used properly.
having { raw("COUNT(*)") > 5 } # SELECT ... FROM ... HAVING COUNT(*) > 5
where { raw("func(:a, :b) = :c", a: a, b: b, c: c) } # SELECT ... FROM ... WHERE function(a, b) = c
Use var to create expression of variable. Variables are columns with or without the namespace and tablename:
It escapes each part of the expression with double-quote as requested by PostgreSQL.
This is useful to escape SQL keywords or .
and "
character in the name of a column.
var("template1", "users", "name") # "template1"."users"."name"
var("template1", "users.table2", "name") # "template1"."users.table2"."name"
var("order") # "order"