📘 Query Builder

query() gives you an Illuminate query builder over the MODX database. It is the same builder Laravel uses, so its whole vocabulary applies — this page only covers what is specific to PageBlocks.

Table names go without the prefix

The connection already knows the prefix, so pass the bare table name:

query('site_content')    // → modx_site_content
query('pb_block_data')   // → modx_pb_block_data

Passing modx_site_content produces modx_modx_site_content and a "table doesn't exist" error.

Called without arguments, query() returns the connection itself — useful for raw statements and transactions:

query()->statement('SET SESSION group_concat_max_len = 100000');
query()->transaction(function () { /* ... */ });

What comes back

get() returns a PbCollection of plain stdClass rows. Fields are read as properties, not through a getter:

{foreach query('site_content')->where('template', 4)->orderBy('menuindex')->get() as $page}
    <a href="{$page->uri}">{$page->pagetitle}</a>
{/foreach}

This is the biggest practical difference from xPDO: there is no $page->get('uri') and no model behaviour on the row. If you need a model — with relations, casts and scopes — use model() instead.

JSON columns

PageBlocks stores a lot in JSON columns (data, properties, sync_fields). The raw builder returns them as strings. withJsonColumns() decodes them for you:

$rows = query('pb_block_data')
    ->withJsonColumns(['data'])
    ->where('model_id', 1)
    ->get();

// $rows->first()->data is an array, not a JSON string

Everyday methods

All of these are the standard builder; the examples below were run against a live site.

Reading

query('site_content')->get();                       // PbCollection of rows
query('site_content')->first();                     // one row or null
query('site_content')->find(1);                     // by primary key
query('site_content')->where('id', 1)->value('pagetitle');   // one field
query('site_content')->pluck('pagetitle');          // flat list of one column
query('site_content')->inRandomOrder()->first();    // random row

Counting and aggregating

query('site_content')->count();
query('site_content')->max('id');
query('site_content')->min('id');
query('site_content')->avg('id');
query('site_content')->sum('id');
query('site_content')->where('id', 1)->exists();

Filtering

query('site_content')
    ->where('template', 4)
    ->whereIn('id', [1, 2, 3])
    ->whereNotNull('publishedon')
    ->orderBy('menuindex')
    ->limit(10)
    ->get();

Joins

query('site_content')
    ->leftJoin('users', 'site_content.createdby', '=', 'users.id')
    ->select('site_content.*', 'users.username')
    ->get();

join, leftJoin, rightJoin and joinSub all work. Note the joined table also goes without the prefix.

Unions

query('site_content')->where('template', 4)
    ->union(query('site_content')->where('template', 5))
    ->get();

Debugging a query

toSql() shows the SQL with placeholders, getBindings() shows the values:

$q = query('site_content')->where('template', 4);
$q->toSql();        // select * from `modx_site_content` where `template` = ?
$q->getBindings();  // [4]