Query collections

A query collection is the unit we use to organise related queries.

In practice, it is either:

• a directory containing .sql and .php query files, or
• a single PHP class file with methods that return SQL.

SQL directory collections

If we create query/user/getById.sql, we can execute it like this:

$row = $db->fetch("user/getById", 105);

It's recommended to use the / character to separate directories, but the following separators are supported:

• user/getById
• user.getById
• user\\getById

Nested collections work in the same way:

$db->fetch("admin/audit/listRecent");
$db->fetch("admin.audit.listRecent");

Working with a QueryCollection object

$userQueries = $db->queryCollection("user");

$row = $userQueries->fetch("getById", 105);
$all = $userQueries->fetchAll("listAll");
$newId = $userQueries->insert("insert", ["email" => "dev@example.com"]);

Now we can pass $userQueries into a repository class and keep access scoped to one part of the database, enforcing encapsulation to the database.

PHP query collections

Warning

It is bad practice to embed SQL within strings, and the below is only made possible so that Query classes can utilise the features of PHP.GT/SqlBuilder - the functionality is maintained separately to keep the Database layer clean and minimal.

Instead of storing SQL files in a named directory, we can use a PHP class. For example, query/Product.php:

namespace App\Query;

class Product {
	public function listByCategory():string {
		return "select id, name from product where category = :category";
	}
}

Usage:

$db->fetchAll("Product/listByCategory", ["category" => "books"]);

If your namespace is not \App\Query, set it with setAppNameSpace() or setAppNamespace() on the collection.

---

In the next section we will jump in to building queries with Parameter binding.