Query DSL

My favorite part. Jennifer allows you to build lazy evaluated queries with chaining syntax. But some of them could be only at the and of a chain (such as #first, #find or #pluck) because they trigger request.

WHERE

.all creates empty request for a model it is invoked on.

Contact.all

Specifying where clause is really flexible. Method accepts block which presents WHERE clause of request (or it’s part - you can chain several #where and they will be concatenated using AND).

To specify table column use #c method which accepts string parameter as a name. As it was mentioned earlier after declaring model attributes, you can use their names inside of block: _field_name if it is for current table and ModelName._field_name if you need to reference a column field_name of the table related to ModelName model. Also there you can specify attribute of some model or table using underscores: _some_model_or_table_name__field_name - model/table name is separated from field name by __. You can specify relation in space of which you want to declare condition using double _ at the beginning and block. Several examples:

Contact.where { c("id") == 1 }
Contact.where { _id == 1 }
Contact.join(Address) { Contact._id == _contact_id }
Contact.relation(:addresses).where { __addresses { _id > 1 } }
Contact.where { _contacts__id == 1 }

Also you can use primary to mention primary field:

Passport.where { primary.like("%123%") }

Supported operators:

Operator	SQL variant
`==`	`=`
`!=`	`!=`
`<`	`<`
`<=`	`<=`
`>`	`>`
`>=`	`>=`
`=~`	`REGEXP`, `~`
`&`	`AND`
`\|`	`OR`
`xor`	`XOR`

Also there are shortcuts for AND, OR and XOR operators to emit extra brackets around operands and wraps the result into them:

Post.where { _active & (_likes > 10) } # WHERE posts.active AND users.likes > 10

Post.where { and(_active, _likes > 10) } # WHERE (posts.active AND users.likes > 10)

Operator-like methods:

Method	SQL variant
`regexp`	`REGEXP`, `~` (accepts `String`)
`not_regexp`	`NOT REGEXP`
`like`	`LIKE`
`ilike`	`ILIKE` for pg and `LIKE` for mysql
`not_like`	`NOT LIKE`
`is`	`IS` and provided value
`not`	`NOT` and provided value (or as unary operator if no one is given)
`in`	`IN`
`between`	`BETWEEN`

Postgres specific:

Method	SQL variant
`contain`	`@>`
`contained`	`<@`
`overlap`	`&&`

Also Jennifer supports json field path methods for criteria: Criteria#take (also accessible as Criteria#[]) and Criteria#path.

If you need to fetch the first matched record

Contact.find_by!({:name => "John", :surname => "Doe"}) # #<Contact name: "John", surname: "Doe">

MySQL

For mysql both take and path methods behave in the same way.

There are 2 supported cases:

WHERE field_name->"$.selector"

could be specified using

where { _field_name["$.selector"]}

WHERE field_name->"$[1]"

can be specified as:

where { _field_name.take(1) }

PostgreSQL

#path method use #> operator
#take method use -> operator

Tips

all regexp methods accepts string presentation of regexp;
use parenthesis with binary operators (& and |);
nil given to != and == will be transformed to IS NOT NULL and IS NULL;
is and not operator accepts next values: nil, :unknown, true and false;
ANY and ALL statement allow to path nested query;
you can also use query instance (wrapped into Grouping object) as condition argument

Several examples:

Contact.where { (_id > 40) & _name.regexp("^[a-d]") }
Contact.where { and(_id > 40, _name.regexp("^[a-d]")) }

Address.where { _contact_id.is(nil) }

nested_query = Address.all.where { _main }.select(:contact_id)
Contact.all.where { _id == any(nested_query) }

Contact.all.where { _id.in(g(Contact.all.where { _name.like("%ohn") })) }

Raw query

To specify exact SQL query use #sql method:

# it behaves like regular criterion
Contact.where { sql("age > ?",  [15]) & (_name == "Stephan") }

Query will be inserted “as is”. Prefer to avoid such usage but it allows to use database specific functions and features. By default given SQL statement is surrounded with brackets, to avoid them - pass false as 2nd (or 3rd) argument.

#sql also excepts prepared arguments. To mark place to be replaced with db specific argument placeholder(? for mysql and $ notation for postgres) use common crystal %s:

Contact.where { _name == sql("lower(%s)", ["Sam"], false) }

which will be transformed to:

SELECT contacts.*
FROM contacts
WHERE contacts.name = lower($1)

Complex logical condition

To design some complex logical expression like a & (b | c) & d use ExpressionBuilder#g method:

Contact.where do
  (_id > 0) & g(_name.like("%asd%") | _age > 15) & (id < 100)
end

Functions

There is special mechanism to define SQL functions like CURRENT_DATE, ABS or CONCAT. There is already a predefined list of such functions so you can use them in the expression builder context:

Contact.all.where { ceil(_balance) > 10 }

Here is the list of such functions:

lower
upper
current_timestamp
current_date
current_time
now
concat
abs
ceil
floor
round

To define own function:

Jennifer::QueryBuilder::Function.define("ceil", arity: 1) do
  def as_sql(generator)
    "CEIL(#{operand_sql(operands[0], generator)})"
  end
end

It is necessary to define #as_sql method, which returns function SQL. #operands is an array of given function arguments. #operand_sql is a helper method to automatically parse how a given argument should be inserted to the SQL.

Smart arguments parsing

Next methods provide flexible api for passing arguments:

#order
#reorder
#group
#select

They allows pass argument (tuple, named tuple or hash - depending on context) of String, Symbol or Cryteria. String arguments will be parsed as plain SQL (RawSql) and Symbol - as Criteria.

SELECT

Raw SQL for SELECT clause could be passed into #select method. This have the highest priority during SELECT clause composing of SQL request.

Contact
  .select("COUNT(id) AS count")
  .group("name")
  .having { sql("COUNT(id)") > 1 }
  .pluck(:count)

Also #select accepts block where all fields could be specified and aliased:

Contact
  .select { [sql("COUNT(id)").alias("count")] }
  .group("name")
  .having { sql("count") > 1 }
  .pluck(:count)

It is important to note that currently it is impossible to pass Jennifer::QueryBuilder::Condition to #select. In other words Contact.select { [(_age * 2).alias("age")] } isn’t allowed. As a workaround you can temporary use #sql: Contact.select { [sql("age * 2").alias("age")] }.

JOIN

To join another table you can use join method passing model class or table name (String) and join type (default is :inner).

field = "contact_id"
table = "passports"
Contact.join(Address) { Contact._id == _contact_id }.join(table) { c(field) == _id }

Query, built inside of block, will passed to ON section of JOIN. Current context of block is joined table.

Also there is two shortcuts for left and right joins:

Contact.left_join(Address) { _contacts__id == _contact_id }
Contact.right_join("addresses") { _contacts__id == c("contact_id") }

For now Jennifer provide manual aliasing as second argument for #join and automatic when using #eager_load and #with methods. For details check out the code.

Relation

To join model relation (has_many, belongs_to and has_one) pass it’s name and join type:

Contact.relation("addresses").relation(:passport, type: :left)

Relation eager loading

Actual eager load

To automatically join some relation and get it from db use #eager_load and pass relation name:

Contact.eager_load("addresses")

If there are several eager_load with same table - Jennifer will auto alias tables.

Includes (preload)

To load all related objects after main query being executed use #includes method (or it’s alias #preload):

Contact.includes(:addresses)

GROUP BY

Contact.group("name", "id").pluck(:name, :id)

#group allows to add columns for GROUP BY section. If passing arguments are tuple of strings or just one string - all columns will be parsed as current table columns. If there is a need to group on joined table or using fields from several tables use next:

Contact
  .select { [_addresses__street, _contacts__name] }
  .relation("addresses")
  .group(addresses: ["street"], contacts: ["name"])
  .results

HAVING

Contact.group("name").having { _age > 15 }

#having allows to add HAVING part of query. It accepts block same way as #where does.

EXISTS

Contact.where { _age > 42 }.exists? # returns true or false

#exists? check is there is any record with provided conditions. Can be only at the end of query chain - it hit the db.

DISTINCT

Adds DISTINCT keyword of at the very beginning of SELECT statement

Contact.distinct # Array(Contact) with unique attributes (all)

UNION

To make common SQL UNION you can use #union method which accepts other query object. But be careful - all selected fields should have same name and type.

Address
  .select(:contact_id)
  .where { _street.like("%St. Paul%") }
  .union(
    Profile
      .select(:contact_id)
      .where { _login.in(["login1", "login2"]) }
  )
  .results

In this example you can’t use regular #to_a because resulted records are not an address neither profile so they couldn’t be mapped to any model. That’s why only Jennifer::Record could be obtained (which is done by #results).

WITH

You can specify common table expression (even recursive):

Jennifer::Query["cte"].with(
  "cte",
  Jennifer::Query[""]
    .select("1 as n")
    .union(
      Jennifer::Query["cte"].select("1 + n AS n").where { _n < 5 },
      true
    ),
  true
)

None

If at some point you decides to make query to return empty result set - use next:

q = Contacts.where { _age > 19 }
q.none
q.where { _name.like("Jo%") }
q.to_a

But be careful - all further chainable method calls will continue modify the object - only db call will be avoided.