class Clear::Expression

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.cr

Constant Summary

DATABASE_DATE_FORMAT = "%Y-%m-%d"
DATABASE_DATE_TIME_FORMAT = "%Y-%m-%d %H:%M:%S.%L %:z"

Class Method Summary

Instance Method Summary

Class Method Detail

def self.[](arg) #

A fast way to call self.safe_literal See .safe_literal(x : _)


[View source]
def self.ensure_node!(any) #

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.


[View source]
def self.raw(x : String, *args) #

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

[View source]
def self.raw(__template : String, **tuple) #

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

[View source]
def self.raw_enum(x : String, args) #

See self.raw Can pass an array to this version


[View source]
def self.safe_literal(x : Enumerable(AvailableLiteral)) : String #

Transform multiple objects into a string which is SQL-Injection safe.


[View source]
def self.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.

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"

[View source]
def self.safe_literal(x : _) : String #

Sanitize an object and return a String representation of itself which is proofed against SQL injections.


[View source]
def self.unsafe(x) #

Return unsafe string injected to the query. can be used for example in insert query building


[View source]
def self.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


[View source]

Instance Method Detail

def not(x : Node) #

NOT operator

Return an logically reversed version of the contained Node

Example

Clear::Expression.where { not(a == b) }.resolve # >> "WHERE NOT( a = b )

[View source]
def op(a : Node | AvailableLiteral, op : String, b : Node | AvailableLiteral) #

Because many postgresql operators are not transcriptable in Crystal lang, this helpers helps to write operation expressions:

where { op(jsonb_field, "?", "something") } # << Return "jsonb_field ? 'something'"

[View source]
def raw(x : String, *args) #

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

[View source]
def raw(__template : String, **tuple) #

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

[View source]
def var(*parts) #

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"

[View source]