Multi-source report engine on an EAV/association CRM: aggregate each measure per distinct source record per group

A custom report that joins a primary object to secondary objects through an association table (deals -> contacts -> companies) multiplies rows: a deal linked to two contacts appears twice, so SUM(amount) or AVG over the joined rows double counts inside a single group, and totals drift from what users expect from CRM-style report builders.

Build the joined row set with LEFT JOINs from the primary source (so primary records without associations still produce a '(No value)' row), then compute each measure in two steps: first group by (all group keys, the measure's source record id) keeping one value per record, then aggregate that deduplicated set per group. A record still counts once in every group it legitimately belongs to (e.g. a deal shows under each of its contacts' owners), but never twice in the same group. Measures from different sources are computed in separate subqueries and joined back on the group keys. Filters on any source are compiled to 'source.id IN (subquery)' so an existing single-object filter compiler can be reused; renumber the subquery bind parameters ($n -> $n+offset) when concatenating several compiled filters into one statement.

Row multiplication from one-to-many joins is invisible in the SQL but changes SUM/AVG/COUNT; deduplicating by (group keys, record id) restores per-record semantics without losing legitimate multi-group membership.