cakephp: find ignores DISTINCT

Question

i got the following cakephp find situation:

$data = $this->find('all', array(
        'conditions' => array(
            'Roster.league_id' => $league_id,
            'Roster.season' => $season,
            ),
        'fields' => array(
            'DISTINCT Roster.player_id',
            'Roster.league_id',
            'Roster.fflteam_id',
            'Roster.season',
            'Roster.modified',
            'Fflteam.name',
            'Player.firstName',
            'Player.lastName',
            'Player.position'
        ),
        'order' => array(
            'Roster.player_id',
            'Roster.modified DESC'),
        'contain' => array(
            'Fflteam',
            'Player' => array(
                'Stat' => array(
                    'conditions' => array(
                        'Stat.season' => $season),
                    'Scores'),
                'Teamplayer')
                )
    ));

There are more Roster-records with specific player_ids, thats why i try to use DISTINCT. I only need the most recent. Thats why i order the results by player_id and Roster.modified. But die DISTINCT command gets ignored.

e.g:
records:

id=1 player_id=1 modified=2012  
id=2 player_id=1 modified=2013  
id=3 player_id=1 modified=2014  
id=4 player_id=2 modified=2014  
id=5 player_id=2 modified=2013  

result should be:

id=3 player_id=1 modified=2014  
id=4 player_id=2 modified=2014 

I don't see any syntax errors. Maybe there some commands are not possible together or my way of filtering is wrong. would be great if someone can help me.

Answer 1

As AgRizzo suggested i am using a query now.

Its split into 2 Parts. The first gets all entries with the one2one relations:

$this->query("SELECT *  
     FROM (SELECT * FROM `rosters` AS `Roster1` WHERE " . $conditions . " ORDER BY `Roster1`.`modified` DESC) AS `Roster` 
    LEFT JOIN `fflteams` AS `Fflteam` ON (`Roster`.`fflteam_id` = `Fflteam`.`id`) 
    LEFT JOIN `players` AS `Player` ON (`Roster`.`player_id` = `Player`.`id`) 
    GROUP BY `Player`.`id` ORDER BY `Roster`.`player_id` ASC");

The second part gets all one2many relations with its relation:

$this->Stat->find('all', array(
                'conditions' => $conditions,
                'contain' => array(
                    'Scores')
    ));

at the end i merge those 2 arrays