Примерно так джойнить:
$modx->query("SELECT distinct t1.*, v2.value, v3.value FROM ".$tpref."site_content t1 INNER JOIN ".$tpref."site_tmplvar_contentvalues v2 ON
t1.id = v2.contentid INNER JOIN ".$tpref."site_tmplvar_contentvalues v3 ON
t1.id = v3.contentid WHERE t1.parent = 123 LIMIT 10")->fetchAll(PDO::FETCH_ASSOC);