📘 pbQuery

pbQuery is a powerful and concise wrapper over xPDOQuery, inspired by the Laravel Query Builder style. It allows you to write readable, flexible, and secure database queries in MODX.

You can work with pbQuery in two ways:

• Via the facade:

  pbQuery::table(modResource::class)

• Or via the global helper (preferred method):

  query(modResource::class)

In all the following examples, we'll use the query() helper.

🏷 Changing the Table Alias

By default, pbQuery uses the class name (e.g., modResource) as the table alias in all SQL queries:

query(modResource::class)

🔎 SQL:

SELECT `modResource`.`id`, ... FROM `modx_site_content` AS `modResource`

If you need to set a custom alias, use the alias() method:

query(modResource::class)
    ->alias('r')
    ->select(['id', 'pagetitle'])
    ->fetchAll();

🔎 SQL:

SELECT `r`.`id`, `r`.`pagetitle` FROM `modx_site_content` AS `r`

The alias is used in all parts of the query: SELECT, WHERE, JOIN, ORDER BY, etc.

🔹 Retrieving All Records

To get all resources from the modResource table:

$resources = query(modResource::class)->get();

The get() method returns an iterator (Traversable)—you can safely loop through the results in foreach, even if there are a large number of records:

foreach ($resources as $resource) {
    echo $resource->pagetitle;
}

This approach is equivalent to native MODX:

$resources = $modx->getIterator(modResource::class);

📌 get() vs all()

pbQuery provides two methods for retrieving multiple records: get() and all(). Both return a list of objects but differ in how they do it.

Method	Returns	When to Use
get()	Traversable (lazy iterator)	For large datasets, to save memory
all()	array (array of objects)	When you need to work with all objects quickly

Example with get() (iterator):

foreach (query(modResource::class)->get() as $resource) {
    echo $resource->id;
}

Example with all() (array):

$resources = query(modResource::class)->all();
echo count($resources); // Get the count

The difference is important: get() loads data on the fly (memory-efficient), while all() fetches all data into memory at once. If you need sorting, counting, or array manipulations, use all().

🔸 Retrieving a Single Record

First Result

To get the first found resource:

$resource = query(modResource::class)->first();

This method returns null if no record is found. It is equivalent to $modx->getObject(...).

Last Result

The last() method automatically sorts by descending ID and returns the last object:

$resource = query(modResource::class)->last();

Random Result

To get a random object:

$resource = query(modResource::class)->rand();

Useful for random banners, testimonials, or products.

🔍 Finding a Specific Record

By ID (default)

$resource = query(modResource::class)->find(3);

This returns the resource object with id = 3.

By Another Field

You can specify another field:

$resource = query(modResource::class)->find('about', 'alias');

Here, it will find the resource where alias = 'about'.

📋 Fetching Data as an Array

If you don't need xPDOObject instances, you can request data as plain arrays.

All Records:

$resources = query(modResource::class)->fetchAll();

The result is an array where each record is an associative array (similar to PDO::FETCH_ASSOC).

Single Record:

$resource = query(modResource::class)->fetch();

Returns the first matching row as an array.

🧷 Extracting Specific Values

value() Method

To get a single value from a specific field:

$alias = query(modResource::class)
    ->where(['id' => 6])
    ->value('alias');

Returns, for example: 'about'.

pluck() Method

The pluck() method retrieves an array of values from a single column:

$aliases = query(modResource::class)
    ->where(['published' => 1])
    ->pluck('alias');

Result:

['home', 'about', 'contacts', ...]

You can also specify which field to use as the array key:

$aliases = query(modResource::class)
    ->pluck('alias', 'id');

Result:

[
    1 => 'home',
    2 => 'about',
    3 => 'contacts',
]

📊 Aggregations

pbQuery provides convenient methods for aggregate database queries. These methods work similarly to SQL functions like COUNT, MIN, MAX, AVG, SUM, EXISTS, simplifying their usage.

🔢 count()

Returns the number of records matching the query conditions.

$count = query(modResource::class)
    ->where(['published' => 1])
    ->count();

👉 Returns, for example: 42.

📉 min()

Get the minimum value of a field:

$minId = query(modResource::class)->min('id');

👉 Returns the smallest value of the id field.

📈 max()

Get the maximum value of a field:

$maxId = query(modResource::class)->max('id');

👉 Can be used to find the last ID in the table.

➗ avg()

Calculate the average value of a field:

$avgRank = query(modUser::class)->avg('rank');

👉 Returns the average value of rank (e.g., 3.14).

➕ sum()

Get the sum of values for a specific field:

$totalViews = query(modResource::class)
    ->where(['published' => 1])
    ->sum('views');

👉 Returns, for example: 15927.

📊 range()

Returns an array with the minimum and maximum values of a field:

$range = query(modUser::class)->range('rank');

👉 Result:

[
    'min' => 1,
    'max' => 5,
]

Useful for building sliders with min/max values.

❓ exists()

Checks if at least one record matches the conditions:

$hasUnpublished = query(modResource::class)
    ->where(['published' => 0])
    ->exists();

👉 Returns true or false.

🔗 Table Joins

Join methods allow combining the current table with other tables or subqueries based on specified conditions.

📌 Methods

Method	Description
join($class, $alias, $on)	Performs an INNER JOIN with the ON condition.
leftJoin($class, $alias, $on)	Performs a LEFT JOIN.
rightJoin($class, $alias, $on)	Performs a RIGHT JOIN.
joinSub($callback, $alias, $on)	Performs a JOIN with a subquery.
leftJoinSub($callback, $alias, $on)	Performs a LEFT JOIN with a subquery.
rightJoinSub($callback, $alias, $on)	Performs a RIGHT JOIN with a subquery.

📎 Example 1: Simple LEFT JOIN with a User

query(modResource::class)
    ->alias('r')
    ->leftJoin(modUser::class, 'u', 'r.createdby = u.id')
    ->select(['r.id', 'r.pagetitle', 'u.username'])
    ->fetchAll();

🟢 SQL:

SELECT `r`.`id`, `r`.`pagetitle`, `u`.`username`
FROM `modx_site_content` AS `r`
LEFT JOIN `modx_users` AS `u` ON r.createdby = u.id

📎 Example 2: Multiple JOINs

query(modResource::class)
    ->alias('r')
    ->select(['r.id', 'creator.username as created_by', 'editor.username as edited_by'])
    ->join(modUser::class, 'creator', 'r.createdby = creator.id')
    ->leftJoin(modUser::class, 'editor', 'r.editedby = editor.id')
    ->fetchAll();

🟢 SQL:

SELECT `r`.`id`, `creator`.`username` AS `created_by`, `editor`.`username` AS `edited_by`
FROM `modx_site_content` AS `r`
JOIN `modx_users` AS `creator` ON r.createdby = creator.id
LEFT JOIN `modx_users` AS `editor` ON r.editedby = editor.id

📎 Example 3: LEFT JOIN with a Subquery (joinSub)

query(modUser::class)
    ->alias('u')
    ->select(['u.username', 'p.fullname'])
    ->leftJoinSub(function ($query) {
        $query->table(modUserProfile::class)
            ->select(['internalKey', 'fullname']);
    }, 'p', 'u.id = p.internalKey')
    ->fetchAll();

🟢 SQL:

SELECT `u`.`username`, `p`.`fullname`
FROM `modx_users` AS `u`
LEFT JOIN (
    SELECT `modUserProfile`.`internalKey`, `modUserProfile`.`fullname`
    FROM `modx_user_attributes` AS `modUserProfile`
) AS p ON u.id = p.internalKey

🔀 Query Unions

The union and unionAll methods allow combining the results of multiple queries with the same column structure into a single dataset.

📌 Methods

Method	Description
union($callback)	Combines with another query using UNION (unique rows).
unionAll($callback)	Combines with another query using UNION ALL (all rows).

📎 Example 1: Combining Resources with Different Templates

query(modResource::class)
    ->select(['id', 'pagetitle'])
    ->where(['template' => 1])
    ->union(function ($query) {
        $query->table(modResource::class)
            ->select(['id', 'pagetitle'])
            ->where(['template' => 2]);
    })
    ->fetchAll();

🟢 SQL:

(SELECT `modResource`.`id`, `modResource`.`pagetitle`
 FROM `modx_site_content` AS `modResource`
 WHERE `modResource`.`template` = 1)
UNION
(SELECT `modResource`.`id`, `modResource`.`pagetitle`
 FROM `modx_site_content` AS `modResource`
 WHERE `modResource`.`template` = 2)

📎 Example 2: Combining Users and Admins (unionAll)

query(modUser::class)
    ->select(['id', 'username'])
    ->where(['sudo' => 0]) // Regular users
    ->unionAll(function ($query) {
        $query->table(modUser::class)
            ->select(['id', 'username'])
            ->where(['sudo' => 1]); // Admins
    })
    ->fetchAll();

🟢 SQL:

(SELECT `modUser`.`id`, `modUser`.`username`
 FROM `modx_users` AS `modUser`
 WHERE `modUser`.`sudo` = 0)
UNION ALL
(SELECT `modUser`.`id`, `modUser`.`username`
 FROM `modx_users` AS `modUser`
 WHERE `modUser`.`sudo` = 1)

📝 Notes on union

• union and unionAll accept a Closure where an instance of $query is passed for direct manipulation.
• The number and order of columns must match in each query.
• All selection methods are supported: fetch(), fetchAll(), get(), count(), toSql(), etc.

🧩 Where: Filtering Records

The where() method allows applying conditions to the query.

It supports:

• Simple conditions with an array,
• Nested conditions via Closure,
• Logical connectors AND / OR (second parameter),
• Extended operators for regular and JSON fields.

Methods:

where(array|Closure $conditions, string $conjunction = 'AND'): self
orWhere(array|Closure $conditions): self
whereRaw(string $expression): self
orWhereRaw(string $expression): self
whereColumn(array $conditions, string $conjunction = 'AND'): self
orWhereColumn(array $conditions): self
whereExists(\Closure $callback): self
whereNotExists(\Closure $callback): self

✅ Example

query(modResource::class)->where([
    'template' => 1,
]);

WHERE `template` = 1

📘 Supported Operators

Operator	PHP Example	SQL Result Example
= (default)	'template' => 1	template = 1
!=	'template:!=' => 1	template != 1
>	'id:>' => 10	id > 10
<	'id:<' => 10	id < 10
>=	'id:>=' => 5	id >= 5
<=	'id:<=' => 5	id <= 5
LIKE	'pagetitle:LIKE' => 'demo'	pagetitle LIKE '%demo%'
LIKE + array	'pagetitle:LIKE' => ['one', 'two']	(pagetitle LIKE '%one%' OR pagetitle LIKE '%two%')
NOT LIKE	'title:NOT LIKE' => 'test'	title NOT LIKE '%test%'
NOT LIKE + array	'title:NOT LIKE' => ['a', 'b']	(title NOT LIKE '%a%' AND title NOT LIKE '%b%')
IN	'id:IN' => [1, 2, 3]	id IN (1, 2, 3)
NOT IN	'id:NOT IN' => [1, 2, 3]	id NOT IN (1, 2, 3)
IS	'deleted:IS' => null	deleted IS NULL
IS NOT	'deleted:IS NOT' => null	deleted IS NOT NULL
BETWEEN	'id:BETWEEN' => [10, 20]	id >= 10 AND id <= 20
FIND_IN_SET	'groups:FIND_IN_SET' => 3	FIND_IN_SET(3, groups)
FIND_NOT_IN_SET	'groups:FIND_NOT_IN_SET' => 3	NOT FIND_IN_SET(3, groups)

✅ Simple Conditions

query(modResource::class)->where([
    'published' => 1,
    'template' => 2,
])->get();

SQL:

SELECT * FROM modx_site_content WHERE published = 1 AND template = 2

🔁 OR Instead of AND (via Second Argument)

query(modResource::class)->where([
    'template' => 1,
    'parent' => 0,
], 'OR')->get();

SQL:

SELECT * FROM modx_site_content WHERE template = 1 OR parent = 0

🧠 Mixed Keys with OR: (Within One Array)

query(modResource::class)
    ->select(['id', 'pagetitle'])
    ->where([
        'published' => 1,
        'OR:pagetitle:LIKE' => '%demo%',
        'OR:alias:LIKE' => '%demo%',
    ])->get();

SQL:

SELECT `modResource`.`id`, `modResource`.`pagetitle` 
FROM `modx_site_content` AS `modResource` 
WHERE (
    `modResource`.`published` = 1 
    OR `modResource`.`pagetitle` LIKE '%demo%' 
    OR `modResource`.`alias` LIKE '%demo%')

🧩 orWhere() as an Alternative

query(modResource::class)
    ->where(['published' => 1])
    ->orWhere([
        'template' => 1,
        'parent' => 0,
    ])
    ->get();

Same as:

->where([
    'template' => 1,
    'parent' => 0,
], 'OR')

🔄 Compound Array Conditions

query(modResource::class)->orWhere([
    ['parent' => 12, 'published' => 1],
    ['template' => 7, 'published' => 1],
])->get();

SQL:

SELECT `modResource`.`id`, `modResource`.`pagetitle` 
FROM `modx_site_content` AS `modResource` 
WHERE  ( 
    (`modResource`.`parent` = 12 AND `modResource`.`published` = 1) OR 
    (`modResource`.`template` = 7 AND `modResource`.`published` = 1) 
)

🔗 Subqueries in where

The where() method supports subqueries via closures (anonymous functions). This is useful for conditions with IN, NOT IN, EXISTS, and NOT EXISTS.

📌 Supported Operators

Condition	Example Key in where	SQL Result
IN	'id:IN' => fn ($q) => ...	id IN (SELECT ...)
NOT IN	'id:NOT IN' => fn ($q) => ...	id NOT IN (SELECT ...)
EXISTS	':EXISTS' => fn ($q) => ...	EXISTS (SELECT ...)
NOT EXISTS	':NOT EXISTS' => fn ($q) => ...	NOT EXISTS (SELECT ...)

✅ Example: Find Resources Where id Is in Another Selection

query(modResource::class
    ->select(['id', 'pagetitle'])
    ->where([
        'id:IN' => function ($query) {
            $query
                ->select('id')
                ->where(['template' => 7]);
        }
    ])
    ->fetchAll();

SQL:

SELECT `modResource`.`id`, `modResource`.`pagetitle` 
FROM `modx_site_content` AS `modResource` 
WHERE (`modResource`.`id` IN (
    SELECT `modResource`.`id` 
    FROM `modx_site_content` AS `modResource` 
    WHERE (`modResource`.`template` = 7) 
))

✅ Example: Resources Without Children

query(modResource::class)
    ->select(['id', 'pagetitle'])
    ->where([
        'id:NOT IN' => function ($query) {
            $query
                ->select('parent')
                ->where(['parent:!=' => 0]);
        }
    ])
    ->fetchAll();

SQL:

SELECT `modResource`.`id`, `modResource`.`pagetitle` 
FROM `modx_site_content` AS `modResource` 
WHERE (`modResource`.`id` NOT IN (
    SELECT `modResource`.`parent` 
    FROM `modx_site_content` AS `modResource` 
    WHERE (`modResource`.`parent` != 0)
))

✅ Example: With EXISTS

query(\modResource::class)
    ->select('id,pagetitle')
    ->where([':EXISTS' => function ($query) {
        $query->table(\modTemplate::class)
            ->select('id')
            ->where(['category' => 53]);
    }
])->get();

SQL:

SELECT `modResource`.`id`, `modResource`.`pagetitle` 
FROM `modx_site_content` AS `modResource` 
WHERE EXISTS (
    SELECT `modTemplate`.`id` 
    FROM `modx_site_templates` AS `modTemplate` 
    WHERE (`modTemplate`.`category` = 53) )

🧨 Raw SQL Conditions

query(modResource::class)
    ->select('id,pagetitle')
    ->whereRaw('pagetitle LIKE "%demo%"')
    ->orWhereRaw('alias LIKE "%demo%"')
    ->get();

SQL:

SELECT `modResource`.`id`, `modResource`.`pagetitle` 
FROM `modx_site_content` AS `modResource` 
WHERE ( pagetitle LIKE "%demo%" OR alias LIKE "%demo%" )

🔍 Searching JSON Fields

pbQuery supports convenient work with data stored in JSON format—for example, in the values field.

📦 Automatic Mode

If you specify a where condition and the field doesn't exist in the table, pbQuery automatically looks for it in the JSON field (default: values).

query(modResource::class)
    ->where(['priority:>' => 5]) // searches in values->priority
    ->get();

• No need to specify values->priority—pbQuery handles it automatically.
• All standard operators work: =, LIKE, IN, >, <, !=, etc.

📌 Explicit JSON Field Specification

If you want to specify the exact path to a JSON key, use the -> syntax. This is useful when working with multiple JSON fields or specifying depth.

query(modResource::class)
    ->where([
        'values->brand' => 'adidas',
        'values->price:>=' => 100,
        'data->meta->color:LIKE' => '%red%',
    ])

In this case, pbQuery understands that this is JSON access and automatically adds JSON_EXTRACT and JSON_UNQUOTE.

🔢 Smart Type Casting

If the value is a number (int or float), the comparison is performed as numeric (CAST(...) AS DECIMAL); otherwise, it's treated as a string.

->where(['values->discount:>' => 0]) // numeric comparison
->where(['values->discount' => '0']) // string comparison

🔎 Special Operators

Some operators require special handling and offer more flexibility:

✅ SEARCH

Searches for a substring anywhere in the JSON, regardless of nesting. Used via the main JSON path (values->...) but works "deep."

->where(['meta:SEARCH' => 'search']) // inside values->meta

• Case-insensitive.
• Can accept an array of values:

->where(['meta:SEARCH' => ['one', 'two']])

✅ JSON_CONTAINS

Checks if a JSON array contains the specified value (or all values from an array).

->where(['tags:JSON_CONTAINS' => 'news'])
->where(['tags:JSON_CONTAINS' => ['news', 'featured']])

✅ NOT JSON_CONTAINS

Negation of JSON_CONTAINS—the value must not be found in the array.

->where(['tags:NOT JSON_CONTAINS' => 'old'])

💡 Examples

query(modResource::class)
    // Automatic JSON (values->priority)
    ->where(['priority:>' => 5])

    // Explicit JSON path
    ->where(['values->brand' => 'nike'])

    // Numeric comparison
    ->where(['values->discount:>' => 0])

    // Nested key search
    ->where(['data->meta->price:<=' => 1000])

    // LIKE
    ->where(['title:LIKE' => '%test%'])

    // IN
    ->where(['status:IN' => ['draft', 'published']])

    // JSON_CONTAINS
    ->where(['tags:JSON_CONTAINS' => 'special'])

    // SEARCH
    ->where(['meta:SEARCH' => 'text'])

    // Combined search
    ->where([
        ['meta:SEARCH' => 'one'],
        ['type:IN' => ['a', 'b']],
    ])

🔗 whereColumn() and orWhereColumn()

The whereColumn() and orWhereColumn() methods allow comparing one field to another field, rather than to a value. This is useful when you need to compare two columns, such as createdby = editedby or publishedon > editedon.

->whereColumn(array $conditions, string $conjunction = 'AND'): self
->orWhereColumn(array $conditions): self

📌 Syntax

query(modResource::class)
    ->whereColumn([
        'createdby' => 'editedby',
        'publishedon:>' => 'editedon',
    ])
    ->fetchAll();

• The key is the field name (optionally with an operator, e.g., publishedon:>).
• The value is the field to compare against.
• All SQL operators are supported: =, !=, <, >, <=, >=, LIKE, NOT LIKE, etc.

📌 orWhereColumn()

Same as above but uses OR instead of AND.

query(modResource::class)
    ->orWhereColumn([
        'createdby' => 'editedby',
        'deletedon:<=' => 'publishedon'
    ])
    ->fetchAll();

🧠 Example: Comparing with Another Table

If you use join(), you can compare fields from different tables:

query(modResource::class)
    ->select('id,pagetitle,createdby')
    ->join(modUser::class, 'User', 'User.id = modResource.createdby')
    ->whereColumn([
        'modResource.createdby' => 'User.id'
    ])
    ->fetchAll();

🔎 SQL:

SELECT `modResource`.`id`, `modResource`.`pagetitle`, `modResource`.`createdby` 
FROM `modx_site_content` AS `modResource` 
JOIN `modx_users` AS `User` 
    ON User.id = modResource.createdby 
    WHERE `modResource`.`createdby` = `User`.`id`

🔍 whereExists()

The whereExists() method adds an EXISTS (...) condition to the SQL query. It's used for nested queries that check for the existence of records matching certain conditions.

query(modResource::class)
    ->select('id,pagetitle')
    ->whereExists(function ($q) {
        $q->table(\modTemplate::class)
            ->select('id')
            ->where(['category' => 53]);
    });
    ->fetchAll();

SQL:

SELECT `modResource`.`id`, `modResource`.`pagetitle` 
FROM `modx_site_content` AS `modResource` 
WHERE EXISTS (
    SELECT `modTemplate`.`id` 
    FROM `modx_site_templates` AS `modTemplate` 
    WHERE (`modTemplate`.`category` = 53) )

📌 whereNotExists()

The whereNotExists() method adds a NOT EXISTS (...) condition to the query. It's the opposite of whereExists() and is used to exclude records for which no related data exists.

Example:

query(modResource::class)
    ->select('id,pagetitle')
    ->whereNotExists(function ($q) {
        $q->table(modTemplate::class)
          ->select('id')
          ->where('category', 53);
    })
    ->fetchAll();

SQL:

SELECT `modResource`.`id`, `modResource`.`pagetitle`
FROM `modx_site_content` AS `modResource`
WHERE NOT EXISTS (
    SELECT `modTemplate`.`id`
    FROM `modx_site_templates` AS `modTemplate`
    WHERE (`modTemplate`.`category` = 53) )

🎯 Selecting Fields

The select(), selectRaw(), and distinct() methods allow flexible control over field selection in pbQuery.

📌 select()

The select() method defines the list of fields to retrieve from the database. It supports strings, arrays, aliases, JSON fields, subqueries (Closure), and multiple calls.

▪️ String

query(modResource::class)
    ->select('id, pagetitle')
    ->fetchAll();

▪️ Array

query(modResource::class)
    ->select(['id', 'pagetitle'])
    ->fetchAll();

▪️ Aliases (AS)

query(modResource::class)
    ->select(['pagetitle as title']) // or ['pagetitle' => 'title']
    ->fetchAll();

🧩 JSON Field Support

If a specified field doesn’t exist in the table, it will be automatically extracted from the JSON column values:

query(modResource::class)
    ->select(['id', 'price']) // => values->price
    ->fetchAll();

You can explicitly specify a JSON path using ->:

query(modResource::class)
    ->select('properties->title as prop_title')
    ->select('values->description')
    ->get();

query(modResource::class)
    ->select('id, pagetitle, properties->seosuite->uri as seouri')
    ->fetchAll();

🗂 JSON keys are supported with aliases (AS) or without.

🔁 Multiple Calls

The select() method can be called multiple times—fields are merged:

query(modResource::class)
    ->select('id')
    ->select(['pagetitle', 'price'])
    ->get();

🔀 Subqueries in select() via Closure

The select() method supports Closure, allowing subqueries inside SELECT:

query(modResource::class)
    ->alias('resource')
    ->select('id, pagetitle')
    ->select(['children_count' => function ($q) {
        $q->selectRaw('COUNT(*)')
          ->whereColumn(['parent' => 'resource.id']);
    }])
    ->fetchAll();

SQL:

SELECT `resource`.`id`, `resource`.`pagetitle`, (
    SELECT COUNT(*) 
    FROM `modx_site_content` AS `modResource` 
    WHERE `modResource`.`parent` = `resource`.`id` ) as `children_count` 
FROM `modx_site_content` AS `resource`

🔢 Counting with selectCount()

A simplified version of the COUNT(*) subquery with conditions:

query(modResource::class)
    ->alias('resource')
    ->select('id, pagetitle')
    ->selectCount('children_count', [
        'parent' => 'resource.id'
    ])
    ->fetchAll();

🧪 Custom SQL: selectRaw()

Adds any expression or subquery manually:

query(modResource::class)
    ->alias('resource')
    ->select('id, pagetitle')
    ->selectRaw('(
        SELECT COUNT(*)
        FROM modx_site_content
        WHERE parent = resource.id
    ) as children_count')
    ->fetchAll();

🔁 distinct()

The distinct() method removes duplicate rows:

query(modResource::class)
    ->select('createdby')
    ->distinct()
    ->fetchAll();

SQL:

SELECT DISTINCT `modResource`.`createdby` 
FROM `modx_site_content` AS `modResource`

👉 Retrieves a list of all unique authors.

---

🔃 Sorting

orderBy(field, direction = 'ASC')

Sorts by the specified field. If the field doesn’t exist in the table, it assumes it’s in the JSON field values.

query(modResource::class)
    ->orderBy('menuindex') // ASC by default
    ->get();

query(modResource::class)
    ->orderBy('price', 'DESC') // JSON field values->price
    ->get();

📌 Sorting by id_order

If id:IN is specified in where(), the method:

query(modResource::class)
    ->where(['id:IN' => '3,7,1'])
    ->orderBy('id_order')
    ->get();

returns records in the specified order: [3, 7, 1].

💡 Passing Closure to orderBy

You can define complex sorting logic via a closure:

query(modResource::class)
    ->select('id,pagetitle')
    ->orderBy(function ($q) {
        $q->table(\modUser::class)
            ->select('createdon')
            ->whereColumn([
                'modUser.id' => 'modResource.createdby'
            ]);
    }, 'DESC')
    ->fetchAll();

SQL:

SELECT `modResource`.`id`, `modResource`.`pagetitle`
FROM `modx_site_content` AS `modResource` 
ORDER BY (
    SELECT `modUser`.`createdon` 
    FROM `modx_users` AS `modUser` 
    WHERE `modUser`.`id` = `modResource`.`createdby` 
) DESC

🔁 Sorting by the number of child elements:

query(\modResource::class)
    ->alias('resource')
    ->select('id,pagetitle')
    ->selectCount('children_count', [
        'parent' => 'resource.id'
    ])
    ->orderBy(function ($q) {
        $q->table(\modResource::class)
            ->selectRaw('COUNT(*)')
            ->whereColumn([
                'parent' => 'resource.id'
        ]);
    }, 'DESC')
    ->fetchAll();

SQL:

SELECT `resource`.`id`, `resource`.`pagetitle`, (SELECT COUNT(*) 
    FROM `modx_site_content` AS `modResource` 
    WHERE `modResource`.`parent` = `resource`.`id` 
) as `children_count`
FROM `modx_site_content` AS `resource` 
ORDER BY (
    SELECT COUNT(*) 
    FROM `modx_site_content` AS `modResource` 
    WHERE `modResource`.`parent` = `resource`.`id` 
) DESC

Result:

Array
(
    [0] => Array
        (
            [id] => 47
            [pagetitle] => Very Large
            [children_count] => 34
        )

    [1] => Array
        (
            [id] => 49
            [pagetitle] => Non-Integer Prices
            [children_count] => 24
        )

    [2] => Array
        (
            [id] => 12
            [pagetitle] => Women's Clothing
            [children_count] => 23
        )

    [3] => Array
        (
            [id] => 1
            [pagetitle] => Children's Products
            [children_count] => 17
        )
)

🧪 orderByRaw(expression)

Allows passing arbitrary SQL expressions to ORDER BY:

query(modResource::class)
    ->orderByRaw("FIELD(id, 7, 3, 1)")
    ->get();

query(modResource::class)
    ->orderByRaw("CASE WHEN template = 1 THEN 0 ELSE 1 END")
    ->get();

🧮 Grouping

groupBy(field)

Groups results by a field:

query(modResource::class)
    ->select(['id','template'])
    ->groupBy('template')
    ->fetchAll();

🎯 Filtering Groups

having(string $expression)

query(modResource::class)
    ->alias('resource')
    ->select('template')
    ->selectRaw('COUNT(id) as total')
    ->groupBy('template')
    ->having('total > 5')
    ->fetchAll();

SQL:

SELECT `resource`.`template`, COUNT(id) as total 
FROM `modx_site_content` AS `resource` 
GROUP BY template 
HAVING total > 5

Result:

Array
(
    [0] => Array
        (
            [template] => 0
            [total] => 172
        )

    [1] => Array
        (
            [template] => 1
            [total] => 62
        )

    [2] => Array
        (
            [template] => 7
            [total] => 17
        )
)

Retrieving templates with resource counts between 5 and 10 (inclusive):

query(modResource::class)
    ->groupBy('template')
    ->having('COUNT(id) BETWEEN 5 AND 10')
    ->fetchAll();

📌 Limits

• limit(n) — limits the number of results
• offset(n) — skips the first n records

query(modResource::class)
    ->limit(10)
    ->offset(20)
    ->get();

👉 Retrieves 10 records, starting from the 21st.

✨ Pagination

paginate(int $perPage = 10, string $pageName = 'page'): Paginator

The method returns a Paginator object for convenient paginated data output.

Parameters

• $perPage (int, default 10)
  Number of records per page.

• $pageName (string, default 'page')
  The name of the query string parameter that indicates the current page.
  For example, if $pageName = 'p', the URL will look like:

  /products?p=2

Usage Example in Template

{set $users = query('modUser')
    ->where(['active' => 1])
    ->paginate(15)}

<ul>
    {foreach $users->items() as $user}
        <li>{$user.username}</li>
    {/foreach}
</ul>

<div class="pb-pagination" pb-pagination>
    {$users->links()}
</div>

Additional Information

For more details on available methods and capabilities, refer to the Paginator class documentation.

✏️ Modifying Data

Data modification methods in pbQuery:

Method	Uses	Description
create	xPDO	Creates one or more objects via newObject()->save()
update	xPDO	Updates fields of found objects
remove	xPDO	Deletes found objects
increment	xPDO	Increments a numeric field
decrement	xPDO	Decrements a numeric field
firstOrCreate	xPDO	If record exists - returns it, otherwise creates new one
updateOrCreate	xPDO	If record exists - updates it, otherwise creates new one
insert	SQL	Inserts data directly into table without using models
command('UPDATE')	SQL	Performs mass update via raw SQL UPDATE
command('DELETE')	SQL	Performs mass deletion via raw SQL DELETE

✅ create(array $data)

Creates one or more records via xPDO. Returns:

• xPDOObject on successful creation of single record,
• null if failed to create,
• 7 - count of created records for batch creation.

// Creating single record
query(modResource::class)->create([
    'pagetitle' => 'New Resource',
    'context_key' => 'web'
]);

// Creating multiple records
query(modResource::class)->create([
    ['pagetitle' => 'New Resource', 'context_key' => 'web'],
    ['pagetitle' => 'New Resource 2', 'context_key' => 'web'],
]);

💡 Uses $modx->newObject() and $object->save() for each record.

🧩 insert(array $data): int

Direct data insertion into table via SQL (without xPDO). Faster than create but doesn't trigger save() events.

query(modResource::class)->insert([
    'pagetitle' => 'New Resource',
    'context_key' => 'web'
]);

query(modResource::class)->insert([
    ['pagetitle' => 'New Resource', 'context_key' => 'web'],
    ['pagetitle' => 'New Resource 2', 'context_key' => 'web'],
]);

Returns lastInsertId() for single record or rowCount() for batch insert.

🔄 update(array $data): int

Updates fields of found objects.

query(modResource::class)
    ->where(['parent' => 2])
    ->update(['deleted' => 1]);

Returns count of updated records.

🗑 remove(): int

Deletes all found objects.

query(modResource::class)
    ->where(['parent' => 2])
    ->remove();

Returns count of deleted objects.

📈 increment(string $column, int $count = 1): bool

Increments value of numeric field.

pbQuery::table(modResource::class)
    ->where(['parent' => 2])
    ->increment('menuindex');

📉 decrement(string $column, int $count = 1): bool

Decrements field value. Same as increment(..., -$count).

pbQuery::table(modUserProfile::class)
    ->where(['parent' => 2])
    ->decrement('menuindex');

🧩 firstOrCreate(array $values): ?\xPDOObject

If object matching current query exists - returns it. If not - creates new object with specified values.

$user = query(modUser::class)
    ->where(['username' => 'admin'])
    ->firstOrCreate([
        'username' => 'admin',
        'password' => md5('123456'),
    ]);

If user admin exists - returns it. If not - creates with given password.

🔁 updateOrCreate(array $data): int

If record matching current query exists - updates it, otherwise creates new one.

pbQuery::table(modSystemSetting::class)
    ->where(['key' => 'site_name'])
    ->updateOrCreate(['value' => 'New Site Title']);

Returns count of updated or created records.

🛠 command(string $command = 'SELECT'): self

Allows executing low-level SQL commands UPDATE and DELETE directly. Used with set() (for UPDATE) and where().

🔧 Unlike update() and remove(), works directly via SQL bypassing xPDO. Suitable for mass changes without triggering save()/remove() events.

✅ Example: updating records via UPDATE

$result = query(modResource::class)
    ->command('UPDATE')
    ->set([
        'deleted' => 0,
        'published' => 1,
    ])
    ->where([
        'template' => 5,
        'parent' => 2,
    ])
    ->execute();

🔸 SQL:

UPDATE `modx_site_content`
SET `deleted` = 0, `published` = 1
WHERE `template` = 5 AND `parent` = 2

🗑 Example: deleting records via DELETE

$result = query(modResource::class)
    ->command('DELETE')
    ->where([
        'published' => 0,
        'parent' => 10,
    ])
    ->execute();

🔸 SQL:

DELETE FROM `modx_site_content`
WHERE `published` = 0 AND `parent` = 10

📌 Difference between update()/remove() and command()

Method	Base	Events	Batch	Performance
update()	xPDO	✅ save()	✅	🐌 Slow
command('UPDATE')	SQL	❌	✅	⚡ Fast
remove()	xPDO	✅ remove()	✅	🐌 Slow
command('DELETE')	SQL	❌	✅	⚡ Fast

🔁 Iterating and Processing Records

The map, each, and chunk methods provide convenient ways to process record sets - either entirely or in batches. This is particularly useful when you need to modify, iterate through, or extract data from multiple records.

🔹 map(callable $callback): array

The map method iterates through objects in the selection and returns an array of results obtained after processing each element.

💡 When to use: When you need to transform a collection of objects into a simple array, for example for APIs, lists of IDs, names, etc.

// Get array of IDs for all published resources
$ids = query(modResource::class)
    ->where('published', 1)
    ->map(fn($resource) => $resource->get('id'));

// Get array of resource URLs
$urls = query(modResource::class)
    ->where('published', 1)
    ->map(function ($resource) {
        return $resource->get('uri');
    });

🔹 each(callable $callback): self

The each method iterates through objects one by one and passes them to a callback. Can be used for data modification, logging, sending emails, etc.

💡 When to use: When you need to perform an action with each object without creating a result array.

// Set template for all child pages
query(modResource::class)
    ->where(['parent' => 2])
    ->each(function ($resource) {
        $resource->set('template', 5);
        $resource->save();
    })
    ->get();

// Log each resource
query(modResource::class)
    ->where('published', 1)
    ->each(function ($resource) {
        logger('Processing resource: ' . $resource->get('pagetitle'));
    })
    ->get();

🔹 chunk(int $size, callable $callback): self

The chunk method loads objects in batches and passes them to a callback. This allows processing large selections without memory overflow.

💡 When to use: When processing thousands of records where memory efficiency and stability are important.

// Process resources in batches of 100
query(modResource::class)
    ->where('template', 4)
    ->chunk(100, function ($resources) {
        foreach ($resources as $resource) {
            $resource->set('published', 0);
            $resource->save();
        }
    });

// Send emails to all users in batches of 50
query(modUser::class)
    ->chunk(50, function ($users) {
        foreach ($users as $user) {
            $user->sendEmail("Hello, {$user->username}", [
                'subject' => 'Anyone alive?'
            ]);
        }
    });

Comparison

Method	What it does	Returns	When to use
map	Iterates objects and returns result	Array	Data transformation
each	Iterates objects and performs actions	pbQuery	Modification, logging, side effects
chunk	Splits selection into batches	pbQuery	Mass processing without memory overload

📦 Template Rendering

The tpl(), tplWrapper(), tplParent, outputSeparator() and render() methods allow not only executing queries but also immediately rendering the obtained data through templates. This is especially convenient if you want to generate HTML output directly from PHP without manual template insertion in Fenom.

🔧 tpl(string $tpl): self

Sets the main template that will be applied to each element in the selection.

$output = query(modResource::class)
    ->where(['published' => 1])
    ->tpl('tpl.resource.row')
    ->render();

Example template tpl.resource.row.tpl:

<div class="item">
    <h3>{$pagetitle}</h3>
    <p>{$introtext}</p>
</div>

🧩 tplWrapper(string $tpl, string $var = 'output'): self

Sets a wrapper template that applies to the entire list as a whole. The wrapper receives a variable (default output) containing the entire rendered list.

$output = pbQuery::table(modResource::class)
    ->tpl('tpl.resource.row')
    ->tplWrapper('tpl.resource.wrapper', 'list')
    ->render();

Example template tpl.resource.wrapper.tpl:

<section class="resources">
    {$list}
</section>

🔹 tplParent(string $name): self

Template used only for elements that have children. This allows setting a different template for parent nodes in the tree.

->tplParent('parent-item')

If tplParent is not set, then tplWrapper is used (if set) or the main tpl.

🔀 outputSeparator(string $separator): self

Allows setting a separator between list items. Default is \n.

$output = pbQuery::table(modUser::class)
    ->tpl('tpl.user.row')
    ->outputSeparator("\n")
    ->render();

🧪 render(): string

Executes the query, applies the template to each element, adds system variables and returns the final HTML string.

Each element receives additional variables:

Variable	Value
_iteration	Sequential number starting from 1
_idx	Index starting from 0
_total	Total number of elements
_first	true if first element
_last	true if last element
_even	true if even index (0, 2, 4...)
_odd	true if odd index (1, 3, 5...)

Example template tpl.user.row.tpl using these variables:

<div class="user {if $_even}even{else}odd{/if}">
    <span class="number">#{$_iteration}</span>
    <strong>{$username}</strong>
</div>

💡 Complete Usage Example

echo query(modResource::class)
    ->where(['template' => 3, 'published' => 1])
    ->orderBy('menuindex')
    ->tpl('tpl.resource.row')
    ->tplWrapper('tpl.resource.wrapper', 'content')
    ->outputSeparator(PHP_EOL)
    ->render();

📚 The menu Method

The menu() method in pbQuery allows convenient building of tree structures for displaying menus, categories and other hierarchical data.

Usage Example

$menu = query(modResource::class)
    ->where(['published' => 1, 'hidemenu' => 0])
    ->tpl('tpl.menu.item')
    ->tplParent('tpl.menu.parent')
    ->tplWrapper('tpl.menu.wrapper')
    ->outputSeparator("\n")
    ->menu();

$menu = query(modResource::class)
    ->alias('resource')
    ->select('id,pagetitle,alias,parent,menuindex')
    ->select(['count' => function ($q) {
        $q->selectRaw('COUNT(*)')
            ->whereColumn(['parent' => 'resource.id']);
    }])
    ->tplWrapper('@INLINE <ul>{$output}</ul>')
    ->tplParent('@INLINE <li>{$pagetitle} ({$count})</li><ul>{$output}</ul>')
    ->tpl('@INLINE <li>{$pagetitle}</li>')
    ->menu(0,2);

Arguments

menu(
    int $rootId = 0,
    int $maxLevel = 3,
    string $parentField = 'parent',
    string $primaryKey = 'id'
)

• $rootId - ID of root element
• $maxLevel - maximum nesting depth
• $parentField - name of parent reference field
• $primaryKey - name of primary key field

What It Does

1. Automatically sets selection fields if not already defined
2. Gets all elements using fetchAll()
3. Groups them by parent field (parentField)
4. Builds hierarchy using buildTree()
5. Calls render() for output via Fenom templates
6. If no template is set - returns plain tree array

Template Configuration

Template methods:

• tpl($tpl) - regular item template
• tplParent($tpl, $var = 'output') - parent item template, $var is variable name with nested output
• tplWrapper($tpl, $var = 'output') - outer wrapper template
• outputSeparator($sep) - separator between items

Example Without Templates

If no templates are set - the menu() method returns a tree array:

$tree = query(modResource::class)
    ->where(['published' => 1])
    ->menu();

return $tree;

Output Array Structure

[
    [
        'id' => 1,
        'pagetitle' => 'Home',
        // ...
        'level' => 1
        'children' => [
            [
                'id' => 2,
                'pagetitle' => 'About',
                // ...
                'level' => 2
                'children' => []
            ]
        ]
    ]
]

🌐 The languages Method

The languages() method in pbQuery is used to display available site languages and generate corresponding URLs for each language.

Usage Example

query(modResource::class)
    ->where(['id' => $modx->resource->id])
    ->tplWrapper('@INLINE <nav>{$output}</nav>')
    ->tpl('@INLINE <a href="{$url}" class="{if $active}active{/if}">{$language}</a>')
    ->languages();

If templates aren't specified - returns an array with languages and links.

What It Does

• Gets current resource ($this->first())
• Loads context list from pageblocks_contexts option (in JSON)
• With active option pageblocks_context_fallback != 'default' excludes languages without resource translation
• Forms languages array:
  • key - context key (web, uk, en etc.)
  • value - language name
  • active - whether current language is active
  • language - synonym for value
  • url - URL for switching language

Example Output Without Templates

$languages = pbQuery::table(modResource::class)
    ->where(['id' => $modx->resource->id])
    ->languages();

Example Result:

[
    [
        'key' => 'web',
        'value' => 'English',
        'active' => true,
        'language' => 'English',
        'url' => '/',
    ],
    [
        'key' => 'uk',
        'value' => 'Ukraine',
        'active' => false,
        'language' => 'Ukraine',
        'url' => '/uk/',
    ],
    [
        'key' => 'ru',
        'value' => 'Russian',
        'active' => false,
        'language' => 'Russian',
        'url' => '/ru/',
    ]
]

Template Configuration

The languages() method uses the same template methods as menu():

• tpl($tpl) - single item template
• tplWrapper($tpl, $var = 'output') - outer wrapper
• outputSeparator($sep) - language separator

Required MODX Settings

System settings must include:

• pageblocks_contexts - context list in JSON:

[
    {"key": "web", "value": "English"},
    {"key": "uk", "value": "Ukraine"},
    {"key": "ru", "value": "Russian"}
]

🔍 Result Filtering (filter)

The filter(callable $callback) method allows filtering already obtained data after database query but before template rendering.

public function filter(callable $callback): self

📌 Where Used

The filter() method can be used with any methods returning arrays: ->menu(), ->languages(), ->all(), ->fetchAll().

📥 Parameters

• $callback - callback function receiving each array element. If returns true, element stays; if false - gets removed.

✅ Example: Language Filtering

Display language list excluding current active:

$languages = query('modResource')
    ->where(['id' => $modx->resource->id])
    ->tplWrapper('@INLINE <nav class="nav">{$output}</nav>')
    ->tpl('@INLINE <a class="nav-link" href="{$url}">{$language}</a>')
    ->filter(fn($item) => !$item['active'])
    ->languages();

✅ Example: Tree Filtering by template

Display resource tree excluding those with template = 3:

$tree = query('modResource')
    ->filter(fn($item) => $item['template'] != 3)
    ->menu();

⚙️ Accessing xPDOQuery Object

Sometimes you may need direct access to the internal xPDOQuery object that pbQuery works with. The getQuery() and setQuery() methods are provided for this.

🔹 getQuery(): xPDOQuery

Returns current xPDOQuery instance used internally. Useful when you need to perform low-level operations not covered by pbQuery methods.

💡 When to use:

• Adding complex conditions or expressions
• Integration with external components requiring xPDOQuery

$query = query(modResource::class)
    ->where(['parent' => 10])
    ->getQuery();

$query->where([
    'pagetitle:LIKE' => '%news%',
]);

// Can manually get results
$results = $query->prepare() && $query->stmt->execute()
    ? $query->stmt->fetchAll(PDO::FETCH_ASSOC)
    : [];

🔹 setQuery(xPDOQuery $query): self

Allows manually setting xPDOQuery object, replacing current one. Provides full control over query logic - can build it outside pbQuery then use class conveniences (like get(), first(), map() etc).

💡 When to use:

• When needing to pre-build xPDOQuery manually or elsewhere
• When convenient to construct base query and pass to pbQuery

$xpdoQuery = $modx->newQuery(modUser::class);
$xpdoQuery->where(['active' => 1]);

$users = query(modUser::class)
    ->setQuery($xpdoQuery)
    ->map(fn($user) => $user->get('username'));

These methods allow combining xPDO flexibility with pbQuery convenience. Best used in advanced cases when standard methods are insufficient.

🧠 Query Caching

The cache() method allows caching query results and reusing them on subsequent calls, avoiding repeated SQL execution. Particularly useful for heavy or frequently repeated operations.

🔹 cache(int $time = 3600, string $key = ''): self

• $time - cache lifetime in seconds. Default 1 hour (3600 seconds).
• $key - (optional) cache key. If empty, auto-generated from SQL.

Internally uses Cache::remember() which saves execution result if not cached yet, and returns cached version on next request.

$resources = query(modResource::class)
    ->where(['template' => 5])
    ->cache('', 600) // Cache for 10 minutes
    ->fetchAll();

🧪 Example: Caching with map()

$titles = query(modResource::class)
    ->where(['published' => 1])
    ->cache('', 300) // 5 minutes
    ->map(fn($res) => $res->get('pagetitle'));

💡 Why Use Caching

• Reduce database load
• Improve response time
• Convenience without manual cache management

🐞 Query Debugging

To see executed SQL query, execution time, memory usage and caching status - use the debug() method.

🔹 debug(): self

Enables debug output after query execution.

$resources = query(modResource::class)
    ->where(['published' => 1])
    ->debug()
    ->fetchAll();

After fetchAll() completes, detailed info will be displayed:

=== pbQuery Debug ===
CACHE: 0
SQL: SELECT ... FROM ...
Queries: 1
Query Time: 0.003210 s
PHP Memory: 4.21 MB
Peak Memory: 4.67 MB
PHP Time: 0.015826 s
======================

🔹 What debug Shows

• CACHE - result from cache (1 - yes, 0 - no)
• SQL - generated SQL query
• Queries - number of SQL queries executed
• Query Time - total SQL execution time
• PHP Memory - current memory usage
• Peak Memory - peak memory usage during execution
• PHP Time - total script execution time

The debug() method also uses getSQL() to display final SQL query. You can get it separately via getSQL() - see Getting SQL Query section.

💥 Quick Output and Stop - dump()

For quick debug output and immediate script termination - use dump():

query(modResource::class)
    ->where(['template' => 2])
    ->dump();

Displays same debug info as debug() but stops script execution immediately.

🧠 Useful For:

• Query optimization
• Cache diagnostics
• Resource usage analysis
• Identifying bottlenecks with large datasets

📄 Getting SQL Query

Sometimes it's useful to get generated SQL query as string - for logging, manual checking or debugging. Use getSQL() method for this.

🔹 getSQL(): string

Returns SQL string generated based on current query state.

$sql = query(modResource::class)
    ->select('id,pagetitle')
    ->where(['published' => 1, 'parent' => 0])
    ->getSQL();

return $sql;

SQL:

SELECT `modResource`.`id`, `modResource`.`pagetitle`
FROM `modx_site_content` AS `modResource` 
WHERE `modResource`.`published` = 1 AND `modResource`.`parent` = 0