Using xPDO for Custom Queries in MODX Revolution: A Step‑by‑Step Guide

Why Use xPDO Instead of Raw SQL?

MODX Revolution ships with xPDO, a lightweight ORM that maps database tables to PHP classes. Using xPDO keeps your code portable across database engines, automatically handles escaping, and integrates with MODX’s caching and event system. Raw SQL bypasses these benefits and can lead to duplication, security gaps, and maintenance headaches.

Desired Outcome

Write a snippet that retrieves the five most recent published resources, returns them as hydrated modResource objects, and outputs the titles as a comma‑separated string.

Prerequisites

• MODX Revolution 2.x or 3.x installed and running.
• Access to the Manager to create snippets.
• PHP error reporting enabled during development (e.g., ini_set('display_errors',1); error_reporting(E_ALL);).
• For non‑core tables, an xPDO model generated via php generate_model.php and loaded with $modx->addPackage().
• Sufficient memory to hold the result set; avoid limit() when querying large collections.

Focused Procedure

1. Initialize the Query

   In a snippet, start by creating a new query object for the target class. For core tables, the class name is the same as the table prefix plus the capitalized table name. Example for resources:

   // Inside a snippet
   /* @var $modx modX */
   $query = $modx->newQuery('modResource');
   

2. Add Conditions

   Use the where() method to filter. Operators are expressed as array keys with a colon suffix. For published resources:

   $query->where(array(
       'published' => 1,
       'deleted'   => 0,
       'publishedon:>' => date('Y-m-d H:i:s', strtotime('-1 month'))
   ));
   

3. Order and Limit

   Sort by publishedon descending and limit to five results:

   $query->sortby('publishedon', 'DESC');
   $query->limit(5);
   

4. Execute and Hydrate

   Run the query with getCollection() to receive hydrated objects. If you need raw arrays, chain select() and call toArray() on each object.

   $resources = $modx->getCollection('modResource', $query);
   

5. Output the Result

   Iterate over the collection and build a string of titles:

   $titles = array();
   foreach ($resources as $resource) {
       $titles[] = $resource->get('pagetitle');
   }
   echo implode(", ", $titles);
   

6. Optional: Inspect Generated SQL

   For debugging, dump the SQL that xPDO will run:

   echo $query->toSQL();
   

Expected Checks

• Result Type: $resources should be an array of modResource objects. Verify with is_object($resources[0]) or get_class($resources[0]).
• Hydration: Calling $resource->get('pagetitle') should return a string, not null.
• SQL Accuracy: Compare $query->toSQL() against a manual query in your database client to ensure conditions and ordering match expectations.
• Cache Impact: After first run, the snippet’s result may be cached by MODX. Clear the cache if you modify the snippet logic.

Recovery Options

• Snippet Failures: If the snippet throws an exception or halts page rendering, disable it from the Manager (Resources → Snippets → Delete or Disable). Then clear the MODX cache via Manage → Clear Cache or by deleting core/cache/* manually.
• Database Errors: Check core/cache/logs/error.log for PDO or xPDO errors. The log will contain stack traces pointing to the problematic line.
• Memory Exhaustion: If the snippet fails with “Allowed memory size exhausted”, add a limit() clause or select only necessary fields with select('id','pagetitle').

Custom Table Example

When querying a non‑core table, you must first generate an xPDO model. Assume you have a table my_custom_table with columns id, name, and createdon. Steps:

1. Generate the model:

   php generate_model.php --source=core/components/mycomponent/model/schema/mycomponent.mysql.schema.xml --output=core/components/mycomponent/model/ --namespace=mycomponent
   

2. Load the package in your snippet:

   $modx->addPackage('mycomponent', core_path . 'components/mycomponent/model/');
   

3. Query the table:

   $query = $modx->newQuery('mycomponent\\MyCustomTable');
   $query->where(array('createdon:>' => date('Y-m-d H:i:s', strtotime('-7 days'))));
   $results = $modx->getCollection('mycomponent\\MyCustomTable', $query);
   

Limitations & Version Notes

• MODX Evolution (1.x) uses a different DBAPI; the guide applies only to Revolution.
• In MODX 3, core classes are namespaced (MODX\\Revolution\\modResource). Snippet code may need to reference the full class name or use use MODX\\Revolution\\modResource; for clarity.
• The addPackage() path syntax changed between 2.x and 3.x; always verify against core_path constant.
• Large collections without limit() can exhaust memory. Use pagination or select() to narrow the result set.

Practical Verification Checklist

1. Run the snippet and confirm the output string matches the titles of the newest resources in the Manager.
2. Open the generated SQL ($query->toSQL()) and run it in a database client to ensure it returns the same rows.
3. Check core/cache/logs/error.log after execution for any PHP or xPDO errors.
4. Disable the snippet, clear cache, and re‑enable to ensure no stale data remains.

Conclusion

By leveraging xPDO’s fluent query builder, you can write maintainable, database‑agnostic code that integrates seamlessly with MODX’s caching and event systems. The key is to keep queries concise, test the generated SQL, and handle errors gracefully using the built‑in recovery steps outlined above.