class

Lustra::Expression

Lustra'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 == ''

Constants

DATABASE_DATE_FORMAT = "%Y-%m-%d"

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

Class methods

[](arg)

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

Source

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.

Source

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

Source

raw(__template : String, **tuple)

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

Source

raw_enum(x : String, args)

See self.raw Can pass an array to this version

Source

safe_literal(x : Array(AvailableLiteral)) : Array(String)

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

Source

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

Lustra::Expression[Time.local]             # < "2017-04-03 23:04:43.234 +08:00"
Lustra::Expression[Time.local, date: true] # < "2017-04-03"

Source

safe_literal(x : _) : String

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

Source

unsafe(x)

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

Source

where

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

Source

Instance methods

not(x : Node)

NOT operator

Return an logically reversed version of the contained Node

Example

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

Source

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:

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

Source

raw(x : String, *args)

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

Source

raw(__template : String, **tuple)

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

Source

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"

Source

Lustra's Expression engine

Limitations

Existing local variable / instance method

Usage of AND / OR

Constants

Class methods

Example

Instance methods

Example

Nested types