Translating query involving join table from CakePHP 1.3 to 3

Question

I'm migrating an application from Cake 1.3 to 3.0. My database has teams and people (with teams_people to connect them via belongsToMany relations) and divisions (teams belongTo divisions). The teams_people table has additional information, like whether the person's role on the team (captain, player), jersey number, position, that sort of thing. In the example below, I'm trying to read the list of teams in open divisions that a person is on, including their position, etc.

$teams = $this->Teams->find()
    ->select(['teams_people.*'])
    ->autoFields(true)
    ->contain(['Divisions'])
    ->leftJoin(
        ['teams_people'],
        ['teams_people.team_id = Teams.id']
    )
    ->where([
        'Divisions.is_open' => true,
        'teams_people.person_id' => $id,
    ]);

This is the most direct translation I can find of my Cake 1.3 query, but it won't work because of the "teams_people.*" wildcard that was part of 1.3. (This is discussed elsewhere.) If I put the whole list of teams_people columns in there, it works fine, but that's fragile in the case where I add more columns later, and isn't DRY. (At the moment, to get it to work, I have a helper function that looks at the schema and builds the list of all columns.) I'm still pretty new to the new ORM, and I want to learn the "right" way to do things now so that the many queries I need to convert can be done optimally the first time around instead of having kludges get ingrained.

I don't need the information (name, etc.) about the person in question, as that's already been loaded by the Auth component. That's why I haven't directly involved the people table anywhere in here (eliminating that saves a query), which in turn is why I need to specify the join of the teams_people table. And autoFields alone is not enough to pull in fields from manually-joined tables, hence why I need the select to specify those fields.

I feel like there's going to be some excellent method to do this, maybe by doing the query through the People table object but with the "matching" function or some other method whereby the query on the people table never actually happens? What's the "Cakiest" way to handle situations like this?

Answer 1

Matching seems to be the way go in your case, your tables are properly associated, so there's not really a need to use custom joins and stuff.

Here's a basic example based on your given query:

$teams = $this->Teams
    ->find()
    ->contain(['Divisions'])
    ->matching('People', function(\Cake\ORM\Query $query) use ($id) {
        return $query
            ->where([
                'People.id' => $id
            ]);
    }))
    ->where([
        'Divisions.is_open' => true
    ]);

The ORM will create appropriate inner joins for the people and the teams_people tables and use them to filter the teams where person $id is assigned to. This is all done via inner joins in a single query, and will look something like

SELECT
    Teams.id AS `Teams__id`,
    ...,
    People.id AS `People__id`,
    ...,
    TeamsPeople.id AS `TeamsPeople__id`,
    TeamsPeople.team_id AS `TeamsPeople__team_id`,
    TeamsPeople.person_id AS `TeamsPeople__person_id`,
    ...,
    Divisions.id AS `Divisions__id`,
    ...
FROM
    teams Teams 
INNER JOIN
    people People 
        ON People.id = 1
INNER JOIN
    teams_people TeamsPeople 
        ON (
            Teams.id = (
                TeamsPeople.team_id
            ) 
            AND People.id = (
                TeamsPeople.person_id
            )
        ) 
INNER JOIN
    divisions Divisions 
        ON Divisions.id = (
            Teams.division_id
        ) 
WHERE
    Divisions.is_open = 1

The data from the join table will be available in the special _matchingData property, it will hold an entity in the TeamsPeople key

$teamEntity->_matchingData['TeamsPeople']

See also Cookbook > Database Access & ORM > Filtering by Associated Data