Subqueries and composition

One of the most useful features of SqlBuilder is that query objects can be composed inside other query objects.

Because every query implements Stringable, a query can appear anywhere a string expression would make sense.

Select used as a subquery

SelectQuery accepts a boolean constructor argument. When set to true, the rendered query is wrapped in brackets.

use GT\SqlBuilder\Query\SelectQuery;

class ConsentMinAgeSelect extends SelectQuery {
	public function __construct() {
		parent::__construct(true);
	}

	public function select():array {
		return [
			"minAge",
		];
	}

	public function from():array {
		return [
			"consent",
		];
	}

	public function where():array {
		return [
			"consent.district = student.district",
		];
	}
}

Now it can be embedded in another query:

use GT\SqlBuilder\Query\SelectQuery;

class StudentSelect extends SelectQuery {
	public function select():array {
		return [
			"id",
			"name",
			"dateOfBirth",
		];
	}

	public function from():array {
		return [
			"student",
		];
	}

	public function where():array {
		return [
			"deletedAt is null",
			"age > " . new ConsentMinAgeSelect(),
		];
	}
}

That produces:

age > ( select minAge from consent where consent.district = student.district )

insert ... select ...

InsertQuery supports a select(): ?SelectQuery method for insert ... select ... workflows.

use GT\SqlBuilder\Query\InsertQuery;
use GT\SqlBuilder\Query\SelectQuery;

class ImportStudentRows extends InsertQuery {
	public function into():array {
		return [
			"student",
		];
	}

	public function columns():array {
		return [
			"name",
			"dateOfBirth",
		];
	}

	public function select():?SelectQuery {
		return new class extends SelectQuery {
			public function select():array {
				return [
					"legalName",
					"assignedDOB",
				];
			}

			public function from():array {
				return [
					"temp",
				];
			}

			public function where():array {
				return [
					"example is not null",
				];
			}
		};
	}
}

Queries inside query parts

Several query parts accept SqlQuery objects directly, not just raw strings. That means you can:

• return a SelectQuery from InsertQuery::select()
• return SqlQuery objects in some table-definition arrays
• concatenate or interpolate stringable queries into custom expressions

Composition works best when each query has one job

As with normal PHP classes, composition stays readable when the smaller queries are focused.

Good:

• one query that represents a base student selection
• one query that represents a reporting subquery
• one query that imports rows from a staging table

Less good:

• one giant class that tries to model every possible report shape through conditionals

If the query is starting to branch in several directions, that is usually a sign to split it into a base query and a few specific subclasses.

A note on execution layers

Subqueries are rendered as SQL text only. The surrounding execution layer still handles placeholders and bound values in the normal way.

That is one reason it pairs well with repository or query-collection patterns: the query shape stays in the query object, while the data still arrives from the caller.

Note

In a WebEngine application using phpgt/database, this often means a page or service class chooses the query object, and the database layer deals with connection management and execution automatically.

---

The remaining query family is the table-definition API, covered in Table queries.