SELECT Queries

SELECT Queries

Everything between SELECT and WHERE — projections, table picking, grouping, ordering, pagination. WHERE / ON clauses live in WHERE Clauses; JOINs in JOIN Queries.

Picking a table

$qb->from('users');
// FROM `users`

$qb->from('users', 'u');
// FROM `users` AS `u`

Two or more tables (comma-separated FROM):

$qb->from('users', 'u')
   ->addFrom('orders', 'o');
// FROM `users` AS `u`, `orders` AS `o`

table() is an alias of from() without the alias parameter:

$qb->table('users');
// FROM `users`

When no projection is set, the compiler emits SELECT *:

$qb->from('users');
echo $qb->generateSelectQuery();
// SELECT * FROM `users` WHERE 1

The trailing WHERE 1 is intentional — it gives callers a stable shape to append further conditions to.

select() — projections

$qb->select('id', 'name', $qb->raw('NOW() AS now'))
   ->from('users');
// SELECT `id`, `name`, NOW() AS now FROM `users` WHERE 1

Strings go through the active driver's identifier escaping; RawQuery values are inlined verbatim.

Calling select() again appends — it does not replace:

$qb->select('id')->select('name');
// SELECT `id`, `name`

Wipe the projection list with clearSelect():

$qb->select('id')->clearSelect()->select('name');
// SELECT `name`

Aliases — selectAs()

$qb->selectAs('full_name', 'name')->from('users');
// SELECT `full_name` AS `name` FROM `users` WHERE 1

Aggregate functions

Each helper accepts an optional alias.

Helper	Emits
selectCount('id')	COUNT(id)
selectCount('id', 'total')	COUNT(id) AS total
selectCountDistinct('email')	COUNT(DISTINCT email)
selectMax('age')	MAX(age)
selectMin('age')	MIN(age)
selectAvg('age')	AVG(age)
selectSum('amount')	SUM(amount)

$qb->selectCount('id', 'total')
   ->selectAvg('age', 'avg_age')
   ->from('users');
// SELECT COUNT(`id`) AS `total`, AVG(`age`) AS `avg_age` FROM `users` WHERE 1

String functions

Helper	Emits
selectUpper('name')	UPPER(name)
selectLower('name')	LOWER(name)
selectLength('bio')	LENGTH(bio)
selectMid('name', 1, 5)	MID(name, 1, 5)
selectLeft('name', 3)	LEFT(name, 3)
selectRight('name', 3)	RIGHT(name, 3)
selectConcat(['fn', 'ln'])	CONCAT(fn, ln)
selectDistinct('name')	DISTINCT(name)

⚠️ MID() and LEFT() / RIGHT() are MySQL-flavored. On PostgreSQL or SQLite use Raw Queries for SUBSTRING(... FROM ... FOR ...).

COALESCE

$qb->select('post.title')
   ->selectCoalesce('stat.views', 0, 'views')
   ->from('post')
   ->leftJoin('stat', 'stat.id = post.id');
// SELECT `post`.`title`, COALESCE(`stat`.`views`, 0) AS `views`
//   FROM `post`
//   LEFT JOIN `stat` ON `stat`.`id` = `post`.`id`
//  WHERE 1

The default value can be:

• a numeric literal — inlined as-is;
• a non-numeric string — treated as another identifier and escaped;
• a RawQuery — inlined verbatim.

Fallback to another column:

$qb->selectCoalesce('stat.views', 'post.legacy_views', 'views');
// COALESCE(`stat`.`views`, `post`.`legacy_views`) AS `views`

CONCAT

$qb->selectConcat([
    'first_name',
    $qb->raw("' '"),
    'last_name',
], 'full_name')->from('users');
// SELECT CONCAT(`first_name`, ' ', `last_name`) AS `full_name` FROM `users` WHERE 1

GROUP BY

groupBy() is variadic and recursively flattens arrays:

$qb->select('author_id')
   ->selectCount('id', 'post_count')
   ->from('post')
   ->groupBy('author_id');
// SELECT `author_id`, COUNT(`id`) AS `post_count`
//   FROM `post` WHERE 1
//  GROUP BY `author_id`

$qb->groupBy(['a', 'b'], 'c');
// GROUP BY `a`, `b`, `c`

Duplicate columns are deduplicated automatically.

For HAVING, see WHERE Clauses.

ORDER BY

$qb->orderBy('id', 'desc')->orderBy('name', 'ASC');
// ORDER BY `id` DESC, `name` ASC

The direction must be 'ASC' (default) or 'DESC', case-insensitive. Anything else throws QueryBuilderInvalidArgumentException. Duplicate (column, direction) pairs are deduplicated.

⚠️ When the sort column itself comes from user input, whitelist it in your application code — the builder escapes the identifier but does not constrain it. See Security §V5.

LIMIT / OFFSET

$qb->limit(10);                  // LIMIT 10
$qb->offset(20)->limit(10);      // LIMIT 20, 10
$qb->offset(20);                 // OFFSET 20   (without LIMIT)
$qb->limit(-5);                  // LIMIT 5     (sign-flipped)

Negative arguments are reflected to their absolute value.

The compile-time shortcut

generateSelectQuery() accepts two optional arguments — a selector array and a conditions array. Handy for one-liners:

$qb->from('post');
echo $qb->generateSelectQuery(
    ['id', 'title'],
    ['status' => 1],
);
// SELECT `id`, `title` FROM `post` WHERE `status` = 1

Conditions with string keys become where(key, value); entries with integer keys are passed as a single argument (typically a RawQuery).

End-to-end SELECT

$qb->select('p.id', 'p.title')
   ->selectCount('c.id', 'comments')
   ->from('post', 'p')
   ->leftJoin('comment AS c', 'c.post_id = p.id')
   ->where('p.published', 1)
   ->groupBy('p.id')
   ->orderBy('p.created_at', 'DESC')
   ->limit(20);

echo $qb->generateSelectQuery();

SELECT `p`.`id`, `p`.`title`, COUNT(`c`.`id`) AS `comments`
  FROM `post` AS `p`
  LEFT JOIN `comment` AS `c` ON `c`.`post_id` = `p`.`id`
 WHERE `p`.`published` = 1
 GROUP BY `p`.`id`
 ORDER BY `p`.`created_at` DESC
 LIMIT 20

---

Next: WHERE Clauses