Retrieve related data (hasMany) in CakePHP 3

Question

Events hasMany TicketTypes
TicketTypes belogsTo Events

I am trying to retrieve all events with associated ticket types:

$query= $this->Events
    ->find()
    ->select(['id', 'name'])
    ->autoFields(false)
    ->contain(['TicketTypes' => function($q) { 
        return $q->select(['TicketTypes.id', 'TicketTypes.name']); }])
;

SQL query generated:

SELECT Events.id AS `Events__id`, Events.name AS `Events__name` FROM events Events

But what I expected is:

SELECT Events.id AS `Events__id`, Events.name AS `Events__name`, TicketTypes.id AS `TicketTypes__id`, TicketTypes.name AS `TicketTypes__name` FROM events Events LEFT JOIN ticket_types TicketTypes ON Events.id = (TicketTypes.event_id)

This is how my models are configured:

class EventsTable extends Table
{
    public function initialize(array $config)
    {
        $this->displayField('name');

        $this->addAssociations([
            'hasMany'=> ['TicketTypes']
        ]);
    }
}

class TicketTypesTable extends Table
{
    public function initialize(array $config)
    {
        $this->displayField('name');

        $this->addAssociations([
            'belongsTo' => ['Events']
        ]); 
    }
}

Here is the result of debugging my find query:

object(Cake\ORM\Query) {

    '(help)' => 'This is a Query object, to get the results execute or iterate it.',
    'sql' => 'SELECT Events.id AS `Events__id`, Events.name AS `Events__name` FROM events Events',
    'params' => [],
    'defaultTypes' => [
        'Events.id' => 'integer',
        'id' => 'integer',
        'Events.name' => 'string',
        'name' => 'string',
        'Events.datetime_start' => 'datetime',
        'datetime_start' => 'datetime',
        'Events.datetime_end' => 'datetime',
        'datetime_end' => 'datetime',
        'Events.created' => 'datetime',
        'created' => 'datetime',
        'Events.modified' => 'datetime',
        'modified' => 'datetime',
        'Events.slug' => 'string',
        'slug' => 'string',
        'TicketTypes.id' => 'integer',
        'TicketTypes.event_id' => 'integer',
        'event_id' => 'integer',
        'TicketTypes.name' => 'string',
        'TicketTypes.description' => 'text'
    ],
    'decorators' => (int) 0,
    'executed' => false,
    'hydrate' => true,
    'buffered' => true,
    'formatters' => (int) 0,
    'mapReducers' => (int) 0,
    'contain' => [
        'TicketTypes' => [
            'queryBuilder' => object(Closure) {

            }
        ]
    ],
    'matching' => [],
    'extraOptions' => [],
    'repository' => object(App\Model\Table\EventsTable) {

        'registryAlias' => 'Events',
        'table' => 'events',
        'alias' => 'Events',
        'entityClass' => 'App\Model\Entity\Event',
        'associations' => [
            (int) 0 => 'tickettypes'
        ],
        'behaviors' => [],
        'defaultConnection' => 'default',
        'connectionName' => 'default'

    }

}

And here is the result of debugging $query->all():

object(Cake\ORM\ResultSet) {

    'items' => [
        (int) 0 => object(App\Model\Entity\Event) {

            'id' => (int) 101,
            'name' => 'qwertyuiop',
            'ticket_types' => [],
            '[new]' => false,
            '[accessible]' => [
                '*' => true
            ],
            '[dirty]' => [],
            '[original]' => [],
            '[virtual]' => [],
            '[errors]' => [],
            '[repository]' => 'Events'

        },
    ...

As you can see in this line 'ticket_types' => [] ticket types are not being returned by the query.

What can I do to retrieve TicketTypes data?

Thanks.

Answer 1

hasMany associations are being retrieved in a separate query

Your assumption about how the CakePHP ORM retrieves associated data is incorrect.

Unlike hasOne and belongsTo assocaitions which are using joins in the main query, hasMany and belongsToMany asociated data is being retrieved in a separate query, which is being filtered using foreign key values collected from the main query, which in your case would be the Events.id column values.

Look at the rest of the SQL log, you shound find a query similar to

SELECT
    TicketTypes.id AS `TicketTypes__id`, ...
FROM
    ticket_types TicketTypes
WHERE
    TicketTypes.event_id IN (1,2,3, ...)

The results of that query are being stitched together with the main results, and returned in a single result set.

Foreign keys need to be selected

A second problem is that your containments select() call is missing the foreign key column (TicketTypes.event_id), which is required, as without it, the ORM cannot stitch the results together, and thus the ticket types will not be present in the results.

Quote from the docs:

When you limit the fields that are fetched from an association, you must ensure that the foreign key columns are selected. Failing to select foreign key fields will cause associated data to not be present in the final result.

See also

• Cookbook > Database Access & ORM > Retrieving Associated Data
• Cookbook > Database Access & ORM > Query Builder > Passing Conditions to Contain