xPDO Help Needed

I’ve been trying for several days to duplicate this PDO query in xPDO for an article I’m writing. The point is to get the all the resources in a Resource Group.

I’d show you what I’ve tried, but it would be 40+ versions.

This query works fine, but it seems like there ought to be an xPDO version. I already have several xPDO versions that work, but I’d like to include an innerJoin version. Any help appreciated.

function usexPDO($modx, $groupId) {
    $stmt = $modx->query("SELECT 

FROM modx_site_content AS resource
INNER JOIN modx_document_groups AS docgroups
ON resource.id=docgroups.document AND docgroups.document_group= {$groupId}");
    if ($stmt) {
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($results as $doc => $fields) {
           // echo "\n" . $fields['pagetitle'];
           // $resource = $modx->getObject('modResource', $fields['id']);
    } else {
        throw new Exception("SQL Failed");

usexPDO($modx, 2);

I believe this should work:

$groupId = 2;

$q = $modx->newQuery('modResource');
$q->innerJoin('modResourceGroupResource', 'ResourceGroupResources');
$q->select($modx->getSelectColumns('modResource', 'modResource', '', ['id', 'pagetitle', 'introtext', 'description']));
$q->select($modx->getSelectColumns('modResourceGroupResource', 'ResourceGroupResources', '', ['document', 'document_group']));
$q->where(['ResourceGroupResources.document_group' => $groupId]);
// $q->prepare();
// return $q->toSQL();

$result = $modx->getCollection('modResource', $q);
$output = '';
foreach($result as $res) {
    $output .= '<pre>' . print_r($res->toArray('', false, true), 1) . '</pre>';
return $output;

It works! Thanks!!! :+1:

I see that my error was caused by completely forgetting that ResourceGroupResources is an alias for modResource, even though I use that alias in another part of the article (sound of palm hitting face). :face_with_open_eyes_and_hand_over_mouth:

Ironically, that alias is listed on my own website here.

I still don’t completely understand how xPDO gets from that join statement to:

 `ON `modResource`.`id` =  `ResourceGroupResources`.`document`

and why all my attempts to specify that join condition explicitly in the third argument to innerJoin() failed.