Query collections

A query collection is simply a group of related queries, bundled within a directory of SQL or PHP files.

Most of the time, this means a directory in query/:

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

Calling SQL query files

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 makes it easy to find the SQL later.

These separators are supported:

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

Using / is the clearest option and is the recommended style in these docs.

Nested collections

Collections can be nested when that structure is useful.

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

That would resolve to query/admin/audit/listRecent.sql.

Working with a QueryCollection object

Sometimes it is helpful to pass only part of the database API into another class. This allows the concept of encapsulation to be applied to the database layer.

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

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

This is a tidy way to keep a repository or service focused on one area of the schema.

Note that only the query name is required to be passed as the first paramter to the fetch/fetchAll queries, and that the $userDb reference can now be passed to areas of code related to working with the user records - the code can call all of the queries within the provided query collection, but are unable to call queries outside of the collection. As an example of when this could be used: a ShippingService will need to query within the shipping query collection, but should be prohibited from calling queries within the payment collection.

PHP query collections

PHP query classes are also supported, for when SQL queries add limitations.

namespace App\Query;

use Gt\SqlBuilder\SelectBuilder;

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

Then we can call:

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

Tip

PHP query classes are most helpful when we want to generate SQL programmatically, often with phpgt/sqlbuilder. The main SQL Builder docs live separately at https://www.php.gt/sqlbuilder.

Note

In WebEngine, the same query collection layout is used under the project query/ directory. This means the structure you learn here transfers directly into a WebEngine application.

---

Next, move on to Parameter binding so we can pass values into these queries safely and readably.