# Sorting and Virtual Fields

**URL:** <https://discourse.cakephp.org/t/sorting-and-virtual-fields/10115>\
**Category:** Need Help\
**Created:** [January 26, 2022, 7:02am UTC](https://discourse.cakephp.org/t/sorting-and-virtual-fields/10115 "2022-01-26T07:02:52Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![bgrinter](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/bgrinter/32/1381_2.png) [@bgrinter](https://discourse.cakephp.org/u/bgrinter)\
**Post date:** [January 26, 2022, 7:02am UTC](https://discourse.cakephp.org/t/sorting-and-virtual-fields/10115/1 "2022-01-26T07:02:52Z")

</div>

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

```auto
$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

---

<div class="post-metadata">

**Author:** ![Jawfin](https://avatars.discourse-cdn.com/v4/letter/j/dbc845/32.png) [@Jawfin](https://discourse.cakephp.org/u/Jawfin)\
**Post date:** [January 26, 2022, 1:56pm UTC](https://discourse.cakephp.org/t/sorting-and-virtual-fields/10115/2 "2022-01-26T13:56:07Z")

</div>

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

> [@Searching on a Virtual Field](https://discourse.cakephp.org/t/searching-on-a-virtual-field/10058):
>
> Hi all I have a members table that includes a member’s date of birth, and from this I create a virtual field that is the member’s current age. Reading the [cookbook](https://book.cakephp.org/3/en/orm/entities.html#creating-virtual-fields) I see that it states "Do bear in mind that virtual fields cannot be used in finds’ I have a report that I need to create for all members over a certain age however this doesn’t work $query = $membersTable-\>find('search', ['search' =\> $this-\>request-\>getQuery()]) -\>contain(['CurrentMemberUnits.Units']) …

?

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.

---

<div class="post-metadata">

**Author:** ![bgrinter](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/bgrinter/32/1381_2.png) [@bgrinter](https://discourse.cakephp.org/u/bgrinter)\
**Post date:** [January 29, 2022, 12:15pm UTC](https://discourse.cakephp.org/t/sorting-and-virtual-fields/10115/3 "2022-01-29T12:15:46Z")

</div>

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…