CakePHP 3 - Select rows of containing table by date (year)

Question

I've a table like below and now I wan't to get the rows by year. But when I try this I will get the following error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Matches.date' in 'where clause'

My "Matches" table

id | goalsfor   | goalsagainst  | date
1  | 5          | 4             | 2017-07-05 15:57:36
2  | 9          | 5             | 2017-07-05 17:06:31
3  | 7          | 8             | 2015-07-05 15:57:36
4  | 0          | 2             | 2014-07-05 15:57:36
6  | 5          | 4             | 2014-07-05 15:57:36
7  | 7          | 9             | 2014-07-05 15:57:36

My query:

 $playedMatches = $this->Players
    ->find()
    ->contain([
       'MatchePlayers' => [
          'Matches'
       ]
    ]);

 $playedMatches->where(['YEAR(Matches.date) =' => "2017"]);

My associations:

class PlayersTable extends Table {

    public function initialize(array $config) {
        parent::initialize($config);

        $this->setTable('players');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');

        $this->hasMany('MatchePlayers', [
            'className' => 'MatchPlayers',
            'foreignKey' => 'player_id',
            'propertyName' => 'matches',
            'dependent' => true
        ]);
    }
}

class MatchPlayersTable extends Table {

    public function initialize(array $config) {
        parent::initialize($config);

        $this->setTable('matches_players');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');

        $this->belongsTo('Players', [
            'className' => 'Players',
            'foreignKey' => 'player_id',
            'propertyName' => 'player'
        ]);

        $this->belongsTo('Matches', [
            'className' => 'Matches',
            'foreignKey' => 'match_id',
            'propertyName' => 'match'
        ]);
    }
}

arilia arilia · Accepted Answer · 2017-07-06T11:18:24

I find your choose of name confusing: MatchPlayers, MatchePlayers...

anyway: cake does not perform a single query when you have a belongsToMany relationship (and a Player belongsToMany matches trough matches_players)

so you have to specify your conditons in the contain clause

$playedMatches = $this->Players
->find()
->contain([
    'MatchePlayers.Matches' => function ($q)
    {
        return $q->where(['YEAR(Matches.date) =' => "2017"])
    }
]);

CakePHP 3 - Select rows of containing table by date (year)

2 Answers