[5.x] How to order an Entry query by a field within a Matrix field embedded on the entry

[rob-c-baker]

So this, so far, is how I'm going about this. Technically it works and the important bits happen in the database, which is what I was after due to scalability / pagination requirements. If anyone can see any better ways of doing this, please jump in!

The matrix field on the entry contains several things but we are focussing on the dateFrom field within.

1. Add a new date field on the entry that will be used for ordering the Entry query, we added a condition so it is only visible to admins which in effect should make it invisible to other content editors. The new field has the handle mostRecentDateFrom.

2. Add an event to a custom module that populates that entry before save.

The field is a date field with the handle dateFrom on a Matrix field with the handle dates on the Entry:

Event::on(
	Entry::class,
	Element::EVENT_BEFORE_SAVE,
	function(ModelEvent $event) {

		/** @var Entry $entry */
		$entry = $event->sender;

		if (/* is not the correct entry type or section for the entry */) {
			return;
		}

		// Dates ordered by dateFrom, most recent first
		$dates = array_column(
			$entry->dates->collect()
				->sortByDesc(fn (Entry $date) => $date->dateFrom->getTimestamp())
				->toArray(),
			'dateFrom'
		);

		// Populate & save `mostRecentDateFrom` field
		if (isset($dates[0]))  {
			$entry->setFieldValue('mostRecentDateFrom', $dates[0]);
		}
	}
);

3. Resave the section to populate the new field:

php ./craft resave/entries --section=[sectionHandle]

4. Find the SQL CAST() clause needed for a virtual column by doing an entry query using the new field:

$query = Entry::find()->section('rallies')->orderBy('mostRecentDateFrom DESC');
dump($query->createCommand()->rawSql);

Pick out the ORDER BY clause, in our case it was:

CAST((`elements_sites`.`content`->>'$.\"eb4099d9-5d19-457f-8770-5767791fa9a6\".\"date\"') AS DATETIME)

5. Add a virtual column to the elements_sites DB table that pulls the mostRecentDateFrom field out of the entry's JSON blob into a column in our case called rallyStartDate. Use the clause picked out above.

ALTER TABLE `elements_sites` ADD COLUMN `rallyStartDate` `rallyStartDate` DATETIME AS (CAST((`elements_sites`.`content`->>'$.\"eb4099d9-5d19-457f-8770-5767791fa9a6\".\"date\"') AS DATETIME)) VIRTUAL AFTER `uid`;

6. Add an index to this new virtual column:

ALTER TABLE `elements_sites` ADD INDEX `rallyStartDate` (`rallyStartDate`);

7. Adjust the original Entry query to orderBy() this new virtual field, hopefully / eventually using the index just made:

$query = Entry::find()->section('rallies')->orderBy('elements_sites.rallyStartDate DESC')->limit(10);
dump($query->all());

Note: As yet I have not convinced the MySQL engine to use this new index using this query - not certain how much it will help at this point, working on that.

Any comments / suggestions / improvements are welcome, or indeed a way to do this that is a bit more "built in" rather than the "round the houses" approach I have got to here.