CODEWITHSUNDEEP
cakephp-4.x
markcoatsworth
markcoatsworth

Reputation: 45

Select distinct years from a database table in CakePHP 4

I'm trying to retrieve a list of distinct years from a database table in CakePHP 4, using a simple SQL DISTINCT query.

In CakePHP 2 this was pretty straightforward. I used the following line in my controller function:

$this->set('years', $this->Event->findAll(null, array('DISTINCT YEAR(Event.date) as year'), 'date DESC'));

In CakePHP 4, the query builder is different and I've been struggling to come up with something similar. I feel like the closest comparable is:

$years = $this->Events->find('all', array('fields' => array('DISTINCT YEAR(date) as year'), 'order' => array('year DESC')));
$this->set(compact('years'));

However this produces a completely malformed SQL query that is nowhere close to being valid. I've tried following their instructions and everything I've come up with fails terribly.

Does anybody have a simple example of a CakePHP 4 query to grab the unique years from a database table?

Upvotes: 0

Views: 783

Answers (1)

Greg Schmidt
Greg Schmidt

Reputation: 5098

I think this may work for you:

$years = $this->Events->find() // "all" is the default, no need to specify it
    ->select(['year' => 'DISTINCT YEAR(Events.date)'])
    ->order(['year' => 'DESC']);

Upvotes: 2

Related Questions