Fork me on GitHub

introduction

An article by Gaspard Bucher

SQLiss (SQL Introspective, Simple and Secure) is the language used in zafu to perform database queries. Also see querybuilder, as we made this available as separate gem.

In zena, queries return lists with the requested objects that define the new context.

The queries are used in unknown resolution. For example, the following query returns a list just containing a single note (limit 1), which is the second node found (offset 1). We then enter the context of that “second note found” by entering the first node in the list, and render its title.

<p do='notes in site limit 1 offset 1'>
  <r:first do='title'/>
</p>

Queries are also used in attributes like the select='' on context:

<r:context find='all' select='icons from recipients from letters in project'>...</r:context>

or using a more direct way:

<ul do='icons from recipients from letters in project'>...</ul>

Basic syntax

A SQLiss query is made of methods, scopes, filters and group/order clauses.

methods

The methods can refer to classes, roles or relations. For example, to get all images in the current node, you would use:

images

This moves the context inside the list of all images inside the current node (this is the default scope). To get another class of objects or objects with a given role, you just write the class in downcase.

letters

methods can also refer to relations. These can be static relations defined in zena (parent, children, project, section or root) or custom relations (recipient, favorites, icon, etc). See relations to get an overview of what relations are.

icon

This moves from the current node in the node defined by the relation “icon”.

scopes

Scopes are used to select from which bucket the nodes are fetched. The default scope is the current node’s children. The scope appears after in keyword. Available scopes are:

  • self current node’s children (the default)
  • parent parent’s children (get all siblings)
  • project anywhere in the current project (not below a child project)
  • section anywhere in the current section (not below a child section)
  • sub_nodes anywhere below the current node (any descendant)
  • home anywhere below the domain’s homepage node
  • site anywhere in the current site (below root = all nodes)

An example would be to get all images in the current site:

images in site

Get all posts in the current project:

posts in project

Get all documents under the current node:

documents in sub_nodes

filters

Filters let you add clauses to precisely define the nodes you want to retrieve. For example, you might want to get only the images with a name starting with an “a” (% is the wildcard character in like queries):

images where name like "a%" in site

The scope is not mandatory, but if you use one it must come after the filters.

For information on filtering and combining queries see filters.

group / order

These clauses should be familiar to the people used to sql. They define the order in which the nodes are fetched and if they should be grouped. Note that if you group the nodes, you only get the first node of each group. If you just need display grouping, you should use group.

Example of an order clause:

posts in project order by log_at desc

Example of a group clause:

posts in project group by log_at:year

Notice the special function “year” attached to the “log_at” attribute.

Until we write down all the documentation on SQLiss, we encourage you to have a look at the querybuilder unit tests and zena integration tests (query_node).

Practical usage

Here is an example of a query used to show the “todos” of a person if these are not done (property is null) or if it has been closed in the last two days:

todos
  where done is null or updated_at >= #{date} - 2 days
from visitor
order by created_at desc

from

As you can see in the last example above you can use the from keyword to link queries. For example, you might want to query for the icons (portraits) of your friends:

icons from friends

You might want to fetch all the documents from your favorites:

documents from favorites

It’s that easy…

select

The select clause allows to save intermediate fields or calculated results into virtual fields.

It is used when you have multiple “from” clauses and you want to reuse the nested results later without repeating the queries, or if you want to collect statistical data.

In this example, we get the clients (receipients from invoices) and the totals invoiced per client in a given year, starting with the client with the largest total:

recipients
from invoices
  select sum(amount) as tot_invoiced, title as i_num
  where data.year = #{params[:year]}
in site
group by id
order by tot_invoiced desc

You can then use “tot_invoiced” and “i_num” like a property of the “recipients” (customers) node context in Zafu:

<r:each_group>
  <r:first do='title'/>: <r:first do='tot_invoiced'/> (<r:each join=', ' do='i_num'/>)
</r:each_group>

See Select and having clauses in SQLiss.

You can also specify the type of the selected field.

posts select updated_at + 1 month as review_at:time where ...

You can then use “review_at” as a Time object.

having

This lets you filter a clause after the query is executed. This is slower then normal filtering but it lets you use statistical data in the filter clause. For example, to find the customers to whom you sent more then 10’000.- of invoices in a given year:

recipients
from invoices
  select amount.sum as tot_amount
  where date.year = #{params[:year]}
  in site
group by id
having tot_amount > 10000

See Select and having clauses in SQLiss.

paginate

This clause is used to paginate content using an url parameter:

posts in section limit 5 paginate p

This will set the query offset depending on the url parameter “p”.

functions

You can use some functions with the dot syntax:

pages where log_at.year > now.year

comments

  1. leave a comment