Fetch items in a custom order

If you know the IDs of some items, you can fetch and template these items in a specific order using MySQL’s FIELD() function.

Where does this work?

Note this does not work with all types of content. It only works if the function queries a database table that has the field you are filtering by as a column (e.g. perch_categories()). So this does not work with content regions (perch_content_custom()) or collections (perch_collection()) as they deal with a more flexible tables structures to allow for filtering by custom fields.

Besides categories, I find this is useful when fetching items from custom Perch apps that I built.

How do I implement this?

You need to know the IDs (or the value of another unique field). You may get the IDs when fetching another set of items, or from a third-party source. For demonstration, let’s just have an array of category IDs:

$categoryIDs = [12, 6, 8, 22, 9];

To output these categories specifying the order, we can filter on the catID column:

perch_categories([
    'filter'    => 'catID',
    'match'     => 'in',
    'value'     => implode(',', $categoryIDs),
]);

The PHP function implode() glues the array elements together with a given string. In this case we are joining the elements with a comma , so we get the following string:

12,6,8,22,9

So if we are to hardcode the filter, it will be:

perch_categories([
    'filter'    => 'catID',
    'match'     => 'in',
    'value'     => '12,6,8,22,9',
]);

Now we can use the FIELD() function in the sort option:

$gluedCategoryIDs = implode(',', $categoryIDs);

perch_categories([
    'filter'    => 'catID',
    'match'     => 'in',
    'value'     => $gluedCategoryIDs,
    'sort'      => "FIELD(catID,$gluedCategoryIDs)", // use double quotes to allow variable interpolation
]);

Hardcoded:

perch_categories([
    'filter'    => 'catID',
    'match'     => 'in',
    'value'     => '12,6,8,22,9',
    'sort'      => "FIELD(catID,12,6,8,22,9)",
]);
link