Help with XPDO getIterator complex sorting

[robcarey]

Hi All!

I am trying to do a somewhat complex sort using xpdo / getIterator.

I need to be able to sort by the field ‘dam_id’ as compared to a comma delimited list of the ids.

Essentially it is this method/example:

SELECT id, name
FROM mytable 
WHERE id IN (77, 3, 123, 1) 
ORDER BY FIELD(id, 77, 3, 123, 1)

as explained here: php - MySQL sort by some list - Stack Overflow

my code so far (no sorting at all currently)

$galleryCollection = $modx->getIterator(classDAMResources, ['filename:!=' => '', 'dam_id:IN' => $fullIDList ]);
foreach ($galleryCollection as $galleryItem) {
   $galleryItems = [
       'filename'       => $galleryItem->get('filename'),
       'dam_id'         => $galleryItem->get('dam_id'),
       'caption'         => $galleryItem->get('caption')
   ];
   
   $output .= $modx->getChunk('chunk.inspirationgallery.item.tpl', $galleryItems);
}

I am assuming I need some line similar to the below before my foreach:

$galleryCollection->sortby(???);

any guidance is appreciated!

[opengeek]

Something like this is what you want…

@robcarey

$galleryCollectionQuery = $modx->newQuery(classDAMResources, ['filename:!=' => '', 'dam_id:IN' => $fullIDList ]);
$galleryCollectionQuery->sortby('FIELD(dam_id, ' . implode(',', $fullIDList) . ')');
$galleryCollection = $modx->getIterator(classDAMResources, $galleryCollectionQuery);
foreach ($galleryCollection as $galleryItem) {
   $galleryItems = [
       'filename'       => $galleryItem->get('filename'),
       'dam_id'         => $galleryItem->get('dam_id'),
       'caption'         => $galleryItem->get('caption')
   ];
   
   $output .= $modx->getChunk('chunk.inspirationgallery.item.tpl', $galleryItems);
}

[robcarey]

That is EXACTLY like what I want.

Thanks @opengeek !