Sort Dates in a Paginator by Month (ignoring year)

Don’t you love it when you’re trying to solve a problem, and in the process of writing out your plea for help on StackOverflow, you work it out anyway :slight_smile:

The Problem.

I have a simple module in an app I’ve written that shows members of a kid’s youth group with their birthdays - the idea being users can check all upcoming birthdays for the kids and organise cards, cakes or whatever.

The paginator view was supposed to sort by ‘date_of_birth’ , but being a date and with some kids born in different years it was instead sorting on age. I want 5th January 2006 to appear before 10th January 2005.

After a while of scratching my head on this one I created a virtual field in the construct.

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

Now I just include this field in the Paginator, use it in the sort but display the date_of_birth.

<th><?php echo $this->Paginator->sort('Member.birth_date', 'Date of Birth'); ?></th>

echo $this->Time->format('j M Y', $member['Member']['date_of_birth']);

Now I have my list sorting the way I want but still displaying the date. Quite pleased with how simple if was in the end so I thought I’d share incase anyone else ever needs something similar

Brian

As much I know Currently CakePHP doesn’t support virtual field sorting. You can sort your results directly with your DB fields.