Query

SQL query is composed from special variables containing expression what is translated to connceted SQL dialect. Variables are described on URL or in conf and they are combined. Variable names and syntax is designed to be as short and similar to SQL as possible. Ecaped URL characters are avoided as much as possible.

Variables are:

  • select
  • where
  • order
  • group
  • having
  • limitlimit or limit,offset
  • ljoin – left join
  • rjoin – right join
  • cjoin – cross join
  • join – full join

Examples are assuming this schema.

CREATE TABLE users(id integer, firstname varchar, lastname varchar, active boolean);
CREATE TABLE roles(id integer, rolename varchar);
CREATE TABLE user_role(user_id integer, role_id integer);

Simple examples (SQL sentences are not verbatim to Zazler composed sentences):

  • /db/users.json?select=id,firstname
    SELECT id, firstname FROM users
  • /db/users.json?where=id=2
    SELECT id, firstname, lastname FROM users WHERE id = 2
  • /db/users.json?select=id-1,substr(firstname,1,1)
    SELECT id - 1, substr(firstname, 1, 1) FROM users

Things to remember

  • Well-known operators allowed: !, +, -, *, /, (), =, !=, <, <, <=, >=, %
  • Special keywords: null, true, false
  • AND and OR are different.
    • | means OR
    • : means AND
  • Additional operators
    • == becomes IS DISTINCT FROM
    • !== becomes IS NOT DISTINCT FROM
  • For most operators there are also functions available (and, or, not, sub, div etc)
  • @ means "AS" (used in select and joins)
  • Common functions: isnull(expr), notnull(e), not(e), between(whatExpr,A,B), in(what,a,b,c…), like(what,likeMatch)
  • Casting: cast.int(e)int can be str, double, time, date, datetime
  • For strings, Zazler tries to replace it with fieldname, then variable and lastly use it as a string.
  • To query SELECT DISTINCT add opts=distinct to query.
  • Spaces are not allowed
  • For where and having you can use multiple parameters giving meaningless names with dot: where.foo. All where.* are joined with AND parameter.

Examples

  • users.json?select=id@pkey,a@a&a=i%20and%20d
    SELECT id AS pkey, 'i and d' AS "i and d" FROM users
  • users.json?select=id>100:active&where=like(firstname,N)&N=L%25
    SELECT id > 1 AND active FROM users WHERE firstname LIKE 'L%
    '
  • users.json?where=in(id,2,3)|!between(id,10,20)
    SELECT * FROM users WHERE id IN (2,3) OR NOT (id BETWEEN 10 AND 20)
  • users.json?select=concat(firstname,spc,lastname)&spc=%20
    SELECT concat(firstname, ' ', lastname) FROM users
  • users.json?select=concat(firstname,spc,lastname)&spc=%20
    SELECT concat(firstname, ' ', lastname) FROM users

Ordering

Ordering uses also expressions. You may add as many ordering prefernces as you want. There are two special functions for ordering: asc and desc.

  • users.json?order=firstname
    SELECT * FROM users ORDER BY firstname
  • users.json?order=desc(lastname),id
    SELECT * FROM users ORDER BY lastname DESC, id

Limit

Limit has no expression. It may be only limit or limit,offset

  • users.json?limit=4
    SELECT * FROM users LIMIT 4
  • users.json?limit=4,10
    SELECT * FROM users LIMIT 4 OFFSET 10

Joins

Tablename is in variable name and variable value is joining condition: join.tablename=expr. Additionaly you may add AS to tablename with @.

  • users.json?join.user_role=user_role.user_id=users.id
    SELECT * FROM users JOIN user_role ON user_role.user_id = users.id
  • users.json?ljoin.user_role@ur=ur.user_id=user.id
    SELECT * FROM users LEFT JOIN user_role AS ur ON ur.user_id = user.id

Joins are in SQL in same order as they appear in variables.

For cjoin.tablename you still have to provide expression though it has no meaning (it is considered as bug).

All joined table fields can be used in any expression providing tablename table.field. If two or more joined tables have same fieldname and tablename is not given in expression first table is in joining list is used.