Tutorial TinyQueries

Query parameters

If you want to add parameters to a query you can use the colon notation, for example :userID. All parameters you use in a query should be defined by params. For example:

		{
			"params":
			{
				"name": { "type": "string" }
			},
			"select":
			[
				"id",
				"name",
				"email"
			],
			"from": "users",
			"where": "name like :name",
			"orderBy": "name"
		}
		

You can execute the query as follows:

		$output = $db->get("myQuery", "a%");
		

• If there is more than one parameter, you should provide the get() method with an associative array, like: $db->get("myQuery", array( "name" => "a%", "email" => "..." ));
• Possible parameter types are string, int, float and array.
• If the type is array, the array which is supplied as parameter value will be converted to a comma separated list which can be used in SQL IN expressions. For example: "where": "name IN (:name)"
• You can add default values by adding a default field, like "name": { "type": "string", "default": "a%" }.

Nesting queries - a(b(c))

Suppose you have a database containing a table users and a table posts. Each user can have one or more posts; table posts has a field 'user_fk' which points to a user id. Suppots you want to get each user and for each user you want their posts. For this you can write the following queries.

		{
			"keys":
			{
				"userID": "id"
			},
			"select":
			[
				"id",
				"name",
				"email"
			],
			"from": "users",
			"orderBy": "name"
		}
		

		{
			"keys":
			{
				"userID": "user_fk"
			},
			"select":
			[
				"id",
				"date",
				"title",
				"message"
			],
			"from": "posts",
			"orderBy": "date"
		}		
		

Note that the queries have a common key "userID". In the users-query userID refers to the table field "id" which is the primary key. In the posts-query userID refers to the table field "user_fk" which is a foreign key. Now you can just do the following call to nest posts inside of users:

		$output = $db->get("users(posts)");
		

This will result in the following output:

		[
			{
				"id": "2",
				"name": "Japie",
				"email": "japie@hotmail.com",
				"posts": [
					{
						"id": "3",
						"date": "2014-09-08 08:23:00",
						"title": "Animals",
						"message": "I'm amazed about frogs."
					}
				]
			},
			{
				"id": "4",
				"name": "John",
				"email": "john@hotmail.com",
				"posts": []
			},
			{
				"id": "1",
				"name": "Jopie",
				"email": "jopie@hotmail.com",
				"posts": [
					{
						"id": "1",
						"date": "2014-09-01 09:04:00",
						"title": "My First Post",
						"message": "I feel so excited"
					},
					{
						"id": "2",
						"date": "2014-09-03 13:10:00",
						"title": "My Second Post",
						"message": "My second post already, amazing!"
					}
				]
			},
		]		
		

Attach queries - a+b+c

Sometimes you want to extend a query with additional data. For example you want a list of users and for each user you want some statistics, like the number of posts they have created and the last time they created a post. For simple cases you can just add some count fields and do some grouping to get the desired result. But often queries tend to become too complex (too many joins), or you need to group in more than one way which makes it impossible to do the job in one query. Then you might use views and join the original query with views, but in general this is bad for performance.

TinyQueries has a fast algorithm for this kind of joining. You create two queries, one with the basic user info, and one which does the statistics:

		{
			"keys":
			{
				"userID": "id"
			},
			"select":
			[
				"id",
				"name",
				"email"
			],
			"from": "users",
			"orderBy": "name"
		}		
		

		{
			"keys":
			{
				"userID": "user_fk"
			},
			"select":
			[
				"max(date) as 'latestPost'",
				"count(*) as 'postCount'"
			],
			"from": "posts",
			"groupBy": "user_fk"
		}
		

To combine them you just do:

		$output = $db->get("users+activity");
		

This will result in the following output:

		[
			{
				"id": "2",
				"name": "Japie",
				"email": "japie@hotmail.com",
				"user_fk": "2",
				"latestPost": "2014-09-08 08:23:00",
				"postCount": "1"
			},
			{
				"id": "4",
				"name": "John",
				"email": "john@hotmail.com"
			},
			{
				"id": "1",
				"name": "Jopie",
				"email": "jopie@hotmail.com",
				"user_fk": "1",
				"latestPost": "2014-09-03 13:10:00",
				"postCount": "2"
			}
		]
		

Please take note of the following when attaching queries:

• Note that the order is important, so a+b is NOT the same as b+a. a is the 'base query' and b is attached to it.
• The naming of the queries is important. The second query should have the first query name as prefix: users.activity. This naming convention is introduced to create a clear structure within your set of queries.
• Use attach if you don't want to get empty (NULL) fields caused by left joins.

Merge queries - a|b|c

The merge operation is comparable with an outer join. All records of all queries will be in the merged output; records which have the same value for the key field are combined.

The merge operation is especially handy to prevent use of OR in the WHERE clause, which in general dramatically slows down performance. Suppose you want to select users by searching on name or email address. Normally you would do something like ... WHERE name LIKE :search OR email LIKE :search.

		{
			"keys":
			{
				"userID": "id"
			},
			"params":
			{
				"search": { "type": "string" }
			},
			"select":
			[
				"id",
				"name",
				"email"
			],
			"where": "name LIKE :search"
		}
		

		{
			"keys":
			{
				"userID": "id"
			},
			"params":
			{
				"search": { "type": "string" }
			},
			"select":
			[
				"id",
				"name",
				"email"
			],
			"where": "email LIKE :search"
		}
		

These queries can be merged using the pipe symbol |:

		// Get all users for which name or email starts with 'a':
		$output = $db->get("users.name|users.email", "a%");
		

Please take into account the following guidelines for using merge:

• Do NOT use merge if the number of output records of the queries might be large - only use it to merge small amounts of records (e.g. less then 500 records); if you have large number of records, combine them with selector queries to reduce them first.
• Use merge if you cannot do it with SQL's UNION; this might be the case if the fields of each query are different.
• Use merge if the join structure is different in both queries (so combining them into 1 query would end up in unnecessary complex join and/or will result in LEFT JOIN's which are in general slower than inner joins).
• Use merge to avoid SQL's OR in the where-clause, which is in general inefficient.

Filter queries - a:b:c

Filter queries are meant to be used to split complex, slow queries into simpler, faster queries. Besides the performance issue another reason to split queries like this is that you can reuse queries to avoid duplicate code. For example, the name & email queries used in the previous section look very similar to the query users, which we already created for the nesting. We can rewrite these queries as follows:

		{
			"keys":
			{
				"userID": "id"
			},
			"select":
			[
				"id",
				"name",
				"email"
			],
			"from": "users",
			"orderBy": "name"
		}
		

		{
			"keys":
			{
				"userID": "id"
			},
			"params":
			{
				"search": { "type": "string" }
			},
			"select": "id", // Only select the id, not the other fields
			"from": "users",
			"where": "name LIKE :search"
		}
		

		{
			"keys":
			{
				"userID": "id"
			},
			"params":
			{
				"search": { "type": "string" }
			},
			"select": "id", // Only select the id, not the other fields
			"from": "users",
			"where": "email LIKE :search"
		}
		

		// Get all users which have a name starting with 'a'
		$output = $db->get("users:name", "a%");
		
		// Get all users which have a name or email starting with 'a'
		$output = $db->get("users:(name|email)", "a%");
		

Please take note of the following when applying filter queries:

• Note that the order is important, so a:b is NOT the same as b:a. a is the 'base query' and b is the filter.
• The naming of the queries is important. The second query should have the first query name as prefix: users.name. This naming convention is introduced to create a clear structure within your set of queries.

Any Combination

Using the basic operators you can construct more complex expressions, like for example:

		$output = $db->get("(users(posts)+activity):(name|email)", "a%");
		

This expression means: Get all users which have a name or email starting with 'a', add their activity statistics and select their posts.

Operator precedence

When you create query expressions which use more than one operator you have to take into account the precedence of the operators. For example if you do users+activity:name the parser will split the term into users and activity:name; the result of activity:name is attached to users. Although this is technically a correct way of combining the queries it is not very meaningful. You actually want it the other way around: First attach activity to users, and then filter the result by name. In this case you should add parenthesis: (users+activity):name.

The precedence of the operators is as follows:

1. Merge |
2. Attach +
3. Filter :
4. Nest ( )