xPDO Help Needed

Development
1

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 
resource.id,
resource.pagetitle,
resource.introtext,
resource.description,
docgroups.document,
docgroups.document_group

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);
2

I believe this should work:

<?php
$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;
3

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.