pdoResources pagination and record count inconsistency with complex filters

Direct Answer

The total count reported by pdoResources should reflect only the filtered subset of resources, not the entire category tree. pdoPage calculates offsets from that count, so limit bounds the visible rows while pagination stays consistent only when the count query and data query apply identical filters and return the same number of distinct resources.

Confirmed Behavior and Assumptions

• pdoResources issues a data query with LIMIT/OFFSET and a separate COUNT(*) query without them.
• The count query reuses the same &where, &parents, &depth, &class, and related parameters as the data query.
• pdoPage reads the totalVar placeholder, defaulting to total, to build page links.
• Count behavior is version-sensitive; older pdoTools releases handle joined COUNT queries differently than newer releases.

Assumption: you are running MODX Revolution with pdoTools, and the mismatch appears when &includeTVs, custom joins, or deep &parents/&depth filters are active.

Likely Explanation for the Inconsistency

• Row multiplication from joins. Joining TV, category, or custom tables can yield multiple rows per resource. COUNT(*) counts every joined row, inflating the total beyond the number of distinct resources.
• Missing DISTINCT on the resource id. Without DISTINCT modResource.id in the count query, multiplied rows overstate the total. Newer pdoTools versions attempt this automatically, but complex &where JSON or TV filters can bypass it.
• Different subquery handling for nested categories. Deep &parents with &depth may generate derived tables; if the count query builds those conditions differently than the data query, totals drift.
• Stale cache. A long &cacheTime can serve an old total after resources are added or removed.

Steps for This Case

1. Add &distinct=1 to the pdoResources call so DISTINCT modResource.id applies to both queries.
2. Keep every filter parameter identical between requests; do not modify &where, &tvFilters, or joins at runtime between the count and data phases.
3. If filtering by TV values, use &tvFilters or an explicit &leftJoin with a &where clause on the TV table instead of relying on &includeTVs=1 alone.
4. Temporarily remove &limit and compare the visible distinct resources against [[+total]], or enable &countOutput=1 and output [[+total]] on the first page.

Verification

Enable SQL logging for one request and compare the generated SELECT COUNT(*) statement against the data query. Confirm both use the same WHERE clauses and that the count query includes DISTINCT on the resource primary key. Run this in a non-production or low-traffic context to limit logging overhead.

One Missing Diagnostic Detail

Does the generated count query contain DISTINCT modResource.id, and what are its exact JOIN clauses? That detail determines whether &distinct=1 alone resolves the mismatch or whether a version upgrade or manual count snippet is required.