Query collections

A query collection is a group of related queries. Most of the time that means a directory of .sql files, but the same idea also works with PHP classes.

SQL query collections

This is the common layout:

query/
└── user/
    ├── getById.sql
    ├── insert.sql
    └── updateEmail.sql

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

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

The path maps directly to the query name, which keeps the SQL straightforward to find later.

Supported separators

These collection separators are supported:

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

These docs use / because it reads most clearly in both PHP and filesystem paths.

Nested collections

Collections can be nested when that structure is useful:

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

That resolves to query/admin/audit/listRecent.sql.

Working with QueryCollection

Database::queryCollection() gives us a narrower object that only works inside one collection:

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

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

That is often a good fit when we want to hand one part of the application access to one part of the schema.

Calling query names as methods

QueryCollection also supports magic method calls:

$user = $userDb->getById(105);
$rows = $userDb->listAll();

This still resolves through the same query files and returns the same ResultSet and Row objects.

PHP query collection classes

When a query is better expressed in PHP, we can replace the directory with a PHP class:

namespace App\Query;

use Gt\SqlBuilder\SelectBuilder;

class Product {
	public function listByCategory():SelectBuilder {
		return (new SelectBuilder())
			->select("id", "name", "price")
			->from("product")
			->where("category = :category")
			->orderBy("name");
	}
}

Then we can call:

$db->setAppNameSpace("App\\Query");

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

PHP query methods may return:

• a SQL string
• any Stringable object
• a phpgt/sqlbuilder builder

Setting the namespace

The default namespace for PHP query classes is \App\Query.

We can change it globally:

$db->setAppNameSpace("Demo\\Query");

Or just for one collection:

$productQueries = $db->queryCollection("Product");
$productQueries->setAppNamespace("Demo\\Query");

SQL overrides for PHP collections

A PHP query collection can also have a sibling directory with the same name. Queries in that directory override class methods of the same collection.

For example:

query/
├── Report.php
└── Report/
    └── getByCode.sql

That lets us keep most of a collection in PHP while dropping back to SQL for a specific query.

If a SQL override and a public class method use the same query name, the library throws QueryOverrideConflictException rather than choosing one silently.

Tip

PHP query classes are often most useful with phpgt/sqlbuilder, especially when a query has optional clauses or repeated structure. The SQL Builder docs live at https://www.php.gt/sqlbuilder.

Note

WebEngine uses the same collection layout under the project query/ directory, so the same organisation works there too.

---

Next, move on to Parameter binding.