introduction

SQLiss (SQL Introspective, Simple and Secure) is the language used in zafu to perform queries. Queries are used to move from one context to another.

The queries are used in tags like unknown or 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>

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 in zenadmin 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. Other scopes are:

• self current node’s children (the default)
• parent parent’s children (get brothers and sisters)
• project within the current project
• section within the current section
• site anywhere in the current site

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

select

The select clause is only needed when you have multiple “from” clauses and you want to reduce the number of queries or you need to work with statistical data. For example, you might want to get the total invoiced for a client in a given year:

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

You can then use “tot_invoiced” as a field on the “recipient” (customer) node in Zafu:

<r:tot_invoiced/>

See Select and having clauses in SQLiss.

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 in a>

images where name like "a%" in site

The scope is mandatory, but if you use one it must come after the filters. Read more on filters here.

functions

You can use some functions with the dot syntax:

pages where log_at.year > now.year

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 tests files.

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

usage

This is an example of a query used to show the “todos” of a person if these are not done (custom_a field 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…