xPDO query bounding and getResources snippet pagination interoperability

Answer first

With ModX Revolution and the standard getResources snippet, page/limit/offset are translated to a single xPDO query bound as:

• offset = (page - 1) * limit
• xPDOQuery::setLimit(limit)
• xPDOQuery::setOffset(offset)

The limit and offset are applied to the final xPDO query after filters, joins and ordering are built. Snippet caching can make page counts and page contents appear stale when the resource set changes between requests. ModX does not ship a built-in keyset/cursor pagination helper; stable boundaries without growing offsets must be implemented manually.

Confirmed facts vs likely explanation

Confirmed behavior

Based on the getResources implementation pattern, the snippet calculates offset from page and limit and then calls setLimit and setOffset on the internal xPDOQuery object. When filters and joins are present, limit/offset apply to the result set produced by those joins, not to a base table alone. Deterministic ordering via ORDERING and ORDERDIRECTION is required for repeatable pages.

Likely explanation

Snippet caching is per snippet call and cache key. The total count used for pagination controls is often obtained via a separate count query. If the result set is cached but the count is not cached with the same key, or cache lifetime exceeds data change frequency, the displayed number of pages and the rows on a page can diverge. Offset-based paging also degrades with large offsets because MySQL must scan and skip rows.

Practical steps for this case

1. Verify mapping. Set page=1 limit=20, confirm offset=0 and 20 rows returned. Set page=5 limit=20, confirm offset=80.
2. Make ordering deterministic. Use a unique column, e.g., id, in ORDERING so ties do not cause duplicates or gaps.
3. Control caching. For paginated output either disable snippet caching for the page, or ensure the same cache key and TTL covers both the result set and the total count. Test by adding a resource and reloading a cached page.
4. Bound offsets. Limit maximum page number or limit size to keep offset growth manageable for large joins.

Stable boundaries without increasing offsets

No documented built-in keyset pagination exists in core getResources. A manual approach is to order by a unique column and filter with a cursor, e.g., WHERE id > lastSeenId, then setLimit. The last seen key must be stored in the UI and passed back for the next page. This keeps boundaries stable when rows are inserted or deleted, at the cost of losing random page access.

Assumption: ModX Revolution with xPDO ORM and MySQL, standard getResources. Version-specific parameter handling can vary. Confirm your getResources version and ModX version to ensure parameter names and caching defaults match this description.