Building Custom Tables and Paginating Data with MODX Revolution's xPDO

Problem: Needing a Custom Data Store Inside MODX

You want to store data that does not fit into MODX’s default resources, such as event registrations, product inventory, or API logs. Creating a separate table manually and writing raw SQL works, but you lose the convenience of MODX’s ORM, caching, and package management. The goal is to define a custom table once, let MODX generate the matching xPDO class, and then query and paginate results safely.

Thesis

By adding a simple schema XML file to a MODX package and using xPDOQuery’s limit() and offset() methods, you can create a typed, paginated data layer that integrates with MODX’s installer, logging, and caching without hand‑crafted SQL.

Section 1: Understanding the xPDO Schema Workflow

MODX Revolution reads a model/schema.xml file during package installation. From this XML it generates PHP classes that extend xPDOObject and creates the corresponding MySQL tables. The generated class provides getters/setters, collection methods, and integrates with the xPDO cache.

Key points:

• The XML namespace must match the package’s xpdo section in the package manifest.
• Column types follow PDO constants (e.g., xpdotype.integer, xpdotype.varchar).
• Indexes and foreign keys can be declared, but complex database‑specific features may still need raw SQL.

Section 2: Creating a Custom Table – Worked Example

Suppose we need a table for tracking newsletter subscriptions.

1. Create the package structure (e.g., core/packages/newsletter/).
2. Add the manifest newsletter.transport.php that points to the schema.
3. Add the schema file model/schema/newsletter.mysql.schema.xml:

<?xml version="1.0" encoding="UTF-8"?>
<model package="newsletter" baseClass="xPDOObject" platform="mysql" version="1.1">
    <object class="newsletter.Subscription" table="subscriber_subscriptions" extends="xPDOSimpleObject">
        <field key="email" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
        <field key="subscribed_on" dbtype="datetime" phptype="datetime" null="false" default="0000-00-00 00:00:00" />
        <index alias="email" name="email" type="unique">
            <column key="email" length="" />
        </index>
    </object>
</model>

Place the file, zip the package, and install via MODX Manager → Packages → Install. During installation MODX will:

• Create the table subscriber_subscriptions with columns email and subscribed_on.
• Generate the class file core/packages/newsletter/model/newsletter/subscription.class.php.

Verify by checking the database schema and the generated class file; no manual SQL is required.

Section 3: Querying and Paginating with xPDOQuery

Now we can fetch subscriptions in a snippet.

<?php
/** @var modX $modx */
$xpdo = $modx->getXPDO();
$limit = !empty($_GET['limit']) ? (int)$_GET['limit'] : 10;
$page  = !empty($_GET['page'])  ? (int)$_GET['page']  : 1;
$offset = ($page - 1) * $limit;

$criteria = $xpdo->newQuery('newsletter.Subscription');
$criteria->select($xpdo->getSelectColumns('newsletter.Subscription', 'subscription'));
$criteria->sortby('subscribed_on', 'DESC');
$criteria->limit($limit);
$criteria->offset($offset);

$collection = $xpdo->getCollection('newsletter.Subscription', $criteria);

// Output a simple list
foreach ($collection as $sub) {
    echo '
' . htmlspecialchars($sub->get('email'), ENT_QUOTES) . ' – ' . $sub->get('subscribed_on') . '
';
}

// Total count for pagination controls
$countCriteria = $xpdo->newQuery('newsletter.Subscription');
$total = $xpdo->getCount('newsletter.Subscription', $countCriteria);
$totalPages = (int)ceil($total / $limit);

echo 'Page ' . $page . ' of ' . $totalPages . '';
?>

Explanation:

• newQuery() creates an xPDOQuery object.
• Methods limit() and offset() add the appropriate SQL clauses; MODX logs the final parameterized query when the system log level is set to Debug.
• A separate count query (getCount()) provides the total rows needed to calculate page numbers.
• All user‑input values are cast to integers, preventing injection.

Trade‑offs and Limitations

While xPDO simplifies CRUD and pagination, consider these points:

• Schema changes after installation require migration scripts; altering columns directly can break the generated class map.
• Complex joins, sub‑queries, or database‑specific hints (e.g., MySQL USE INDEX) may still need raw SQL or a custom extension.
• The generated class uses xPDOSimpleObject by default, which does not include validation or behaviors; you must add them manually if needed.

Actionable Closing

To try this yourself:

1. Spin up a fresh MODX Revolution 2.x or 3.x instance (ensure the database user has CREATE/ALTER privileges).
2. Create a minimal package with the schema XML shown above.
3. Install the package via Manager → Packages.
4. Add the snippet to a resource, set the log level to Debug, and visit the page with ?page=1&limit=5 to see paginated output.
5. Check the error.log for the generated SELECT statement to confirm it uses placeholders.

If you need to modify the table later, write an upgrade script that uses $modx->runProcessor('schema/update') or manually alters the table and regenerates the class files, then clear the cache.

By leveraging xPDO’s schema‑driven approach you keep your custom data tightly integrated with MODX’s lifecycle while still retaining the ability to drop down to raw SQL when performance demands it.