Tiny Query Language - TQL

In this manual you can learn about the language which is used to generate SQL queries, called Tiny Query Language. The language is designed such that you as a developer have several mechanisms to re-use code and write queries in an easy way, yet without losing the expressiveness and efficiency of SQL.

JSON

All code is specified as a JSON structure. In addition you can add comments both in the /* .. */ and // .. style. If you like you can leave out the quotes from dictionary keys, as long as they are single words without dashes. So for example this is allowed:

{
	key: "value"
}

But suppose the key is called "my-key", you have to write it like this:

{
	"my-key": "value"
}

Select query syntax

The syntax of a select query has the following structure:

/**
 * Comments to describe the query
 *
 */
{
	from: "table",
	select: [
		"this.field1",
		"this.field2",
		"this.field3"
	],
	where: [
		"condition1",
		"condition2",
		"condition3"
	],
	groupBy: [
		"this.field1",
		"this.field2",
		"this.field3"
	],
	orderBy: [
		"this.field1",
		"this.field2",
		"this.field3"
	],
	having: [
		"condition1",
		"condition2",
		"condition3"
	]
}

The keyword this refers to the table you specify by 'from'. We will come back to that later.

Like in SQL you can leave out 'where', 'groupBy', 'orderBy' and 'having' if they don't apply (actually you can even leave out 'from' as long as you don't use 'this' in the query). These elements can be specified both as an array or just as a single string if you one have one field or condition. In the case of 'where' and 'having' an array will be handled as an AND condition. So for example:

	where: [
		"condition1",
		"condition2",
		"condition3"
	]

will be compiled to:

	WHERE 
		(condition1) AND 
		(condition2) AND 
		(condition3)

The array notation is the recommended notation if there is more than one condition, but if you have just one condition or more complex conditions involving OR, you can just write it like this:

	where: "(condition1) and ((condition2) or (condition3))"

In general you should try to avoid OR conditions since they can slow down queries dramatically.

In the example below you can see a full example of a query both in TQL and in SQL.

{
	from: "messages",
	select: [
		"this.id",
		"this.date",
		"this.subject",
		"this.text"
	],
	where: [
		"this.date >= '20150101'",
		"this.date < '20150201'"
	]
}

This query is compiled to

select
  m.`id` as 'id',
  m.`date` as 'date',
  m.`subject` as 'subject',
  m.`text` as 'text'
from
  `messages` as m
where
  (m.`date` >= '20150101') and
  (m.`date` < '20150201')

So far there is not so much of a difference between TQL and SQL. It will become more interesting when joins and aliases are used

Model

Queries are defined in separate files named [queryID].json. The model is defined in file named _model.json. You can use this to define joins and aliases which you can use in your queries.

The structure of the model is as follows:

/**
 * Model 
 *
 */
{
	table1: {
		...
	},
	
	table2: {
		...
	},
	
	table3: {
		...
	}
}

So the model is just the list of the tables which are in your database and for each table you have some definitions. The defintions do not have to be complete - not every table has to be in it and not every possible link between tables has to be defined. You can just define the things you need for your queries.

In the following sections we will show you the things you can define in your model.

Join definitions

You might have noticed that you can only specify one table in the 'from' definition of a select query. This table is called the root-table of the query. From the root-table you can do joins to other tables through a dot notation, which we will show in the following example.

Suppose you have a table messages and a table users and each message has two references to the users table, namely the sender and the receiver of a message. In a select query you can use these references as follows:

{
	from: "messages",
	
	select: [
		"this.id",
		"this.subject",
		"this.text",
		"this.sender.id",
		"this.sender.name",
		"this.receiver.id",
		"this.receiver.name"
	]	
}

In the model you define what 'sender' and 'receiver' mean:

/**
 * Model 
 *
 */
{
	messages: {
	
		sender: {
			join: "inner",
			table: "users",
			on: "this.sender_fk = that.id"
		},
		
		receiver: {
			join: "inner",
			table: "users",
			on: "this.receiver_fk = that.id"
		}
	}
}

In the join-field you specify the type of join (left, right, inner, outer). The table-field is used for the table to join with. In the on-part you specify the join condition, like in SQL. The difference is that you use the keywords this and that. In this case 'this' refers to the table 'messages' and 'that' refers to the table 'users'. So 'sender_fk' is a field of 'messages' and 'id' is a field of table 'users'. You might also use an array for the on-part, in case you have more than one AND-conditions (same as where and having of select queries).

Given this model and query, the compiler would create the following SQL query:

select
  m.`id` as 'id',
  m.`subject` as 'subject',
  m.`text` as 'text',
  u.`id` as 'sender.id',
  u.`name` as 'sender.name',
  u2.`id` as 'receiver.id',
  u2.`name` as 'receiver.name'
from
  (
    `messages` as m
    inner join 
    `users` as u2
    on  m.`receiver_fk` = u2.`id`
  )
  inner join 
  `users` as u
  on  m.`sender_fk` = u.`id`

Asterisk * definition

In the model you can specify a custom definition for *. This might be handy in situations in which you select the same fields over and over again.

/**
 * Model 
 *
 */
{
	messages: {
		"*": [
			"this.id",
			"this.date",
			"this.subject",
			"this.text",
			"this.sender.id",
			"this.sender.name",
			"this.receiver.id",
			"this.receiver.name"
		]
	}
}

The query from the previous example can now be written as follows:

{
	from: "messages",
	select: "this.*"
}

It's recursive so, you can use the * inside a *-definition. For example:

/**
 * Model 
 *
 */
{
	messages: {
		"*": [
			"this.id",
			"this.date",
			"this.subject",
			"this.text",
			"this.sender.*" 	// sender and receiver refer to 'users'
			"this.receiver.*"
		]	
	},
	
	users: {
		"*": [
			"this.id",
			"this.name",
			"this.email"
		]	
	}
}

In the select queries you can add additional fields as well which are not defined by the *

{
	from: "messages",
	select: [ 
		"this.*",
		"this.otherfield1",
		"this.otherfield2"
	]
}

Inline SQL

One of the powerful features of TinyQueries is that you can use any SQL expression inside TQL queries. This will ensure you won't loose any expressiveness you are used to when writing SQL queries manually.

Suppose you want to have the fullname of a user which is just first name and last name concatenated. In MySQL you can do this:

{
	from: "users",
	
	select: [
		"this.id",
		"concat( this.first_name, ' ', this.last_name ) as 'fullname'"
	]
}	

Which is compiled to:

select
  u.`id` as 'id',
  concat( u.`first_name`, ' ', u.`last_name` ) as 'fullname'
from
  `users` as u

You can use SQL expressions as well inside the definitions of where, having, orderBy, groupBy and on.

Field aliases

Suppose you want to re-use the field 'fullname' (see previous section) for other queries you can define a field-alias in the model:

/**
 * Model 
 *
 */
{
	users: {
		fullname: "concat( this.first_name, ' ', this.last_name )"
	}
}

The query will then become like this:

{
	from: "users",
	
	select: [
		"this.id",
		"this.fullname"
	]
}	

Query parameters

Query parameters can be specified as follows:

{
	params: {
		year: "int"
	},
	from: "messages",
	select: [
		"this.id",
		"this.date",
		"this.subject",
		"this.text"
	],
	where: "YEAR( this.date ) = :year"
}

Note that YEAR( . ) is a build-in MySQL function to extract the year-part of a date.

What about update, insert and delete?

If you do an update, insert or delete most of the times it only concerns just one record of a table. For these operations you don't have to write queries yourself - you can just use the PHP methods DB::update, DB::insert and DB::delete which are straightforward to use. See the PHP section for more details.

For more complex updates or inserts which involve more than one table and/or records we are still working on a generic solution, since not all SQL constructs for this are available for all dialects like MS SQL, MySQL, Postgres etc.

Summary

If you understand the language constructs which are described in this manual you will have a good basis for using TinyQueries. Of course, like any new language you learn, you have to play around with it and see wether it fits your needs.