How can I expand on a normal Craft query to sort by a column in a custom table?

Question

I have a plugin which contains a Record. My record contains a foreign key of the related element ID. The only other column in my custom table is the value I'd like to sort by.

In Twig syntax, how can I expand on a normal Craft query to sort by a column in my custom table?

Ordinary query:

craft.entries.section('colors').find

Ordinary query which sorts by my custom data:

craft.entries.section('colors').myPlugin('asc').find

---

Disclaimer:

I've done something similar to this once before... But in that case, I used the modifyElementsQuery method in my Field Type. Since this new plugin doesn't require a field type, I'm not exactly sure of the best way to modify a standard query.

Answer 1

You can't do this using the craft.entries tag in twig. You'll need to create a new function in your plugin that returns the entries in your custom order.

Assuming you can create an array of element ids in your custom order then the following code should allow you to then fetch entries in that order:

$criteria = craft()->elements->getCriteria(ElementType::Entry);

// filter entries by element ids
$criteria->setAttribute('id', $elementIds);

// order entries by element ids
$criteria->setAttribute('order', 'FIELD(elements.id, '.join(', ', $elementIds).')');

// get entries
$entries = craft()->elements->findElements($criteria);