Sorting and Virtual Fields

Need Help
1

In CakePHP2 I was using a virtual field for members “birth_date” which is the DayOfYear of a member’s Date of Birth - 5th August is ‘216’, irrespective of year.

It allows me to create a report sorted by the birthday but ignoring year. 26 Jan 2001 appears before 27th Jan 2000 etc

$this->virtualFields['birth_date'] = sprintf(
	'DAYOFYEAR(%s.date_of_birth)',
	$this->alias
);

I’ve created a virtual field in CakePHP4 but I can’t sort on it.

I’m guessing I could create a custom finder? But could I sort ASC and DESC?

What other options do I have? There is a MySQL function DayOfYear but the ORM doesn’t support it

2

Not trying to be smart, but how is this conceptually different to the question you asked not long ago here: -

?

In that all the problems you’d encounter with searching applies to sorting? There may be a way, I just figured conceptually it’ll be the same, so same issues.

3

Sorry for not replying straight away - day job has kept me busy.

Yeah struggling a bit with stuff that was a ‘given’ in CakePHP2, but the ORM means rethinking some things like this

And here I was thinking the move from CakePHP2 → 4 was going to be easy…