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.

Tachymetry

Your question is kinda offtopic as you're not elaborating on your actual problem! People familiar with how CakePHP works might be able to figure what you're confused about, but that's not how StackOverflow works, questions should be clear about the problem so that noone has to guess what you are asking for. That being said, using contain causes the ORM to load the associated TicketTypes, so that's what you have to do, and what you're already doing. If there's a specific problem, then please mention that. – Gnarl

Hi ndm, thank you very much for your comments. Keep in mind that it is my first post. I am going to edit my question to make it more clearly. Regards. – Tachymetry

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

Gnarl

I have debugged the result and the data that I am expecting is not there. 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',` ... – Tachymetry

@DanielFuscoCiccariello Look at what the (help) key says, you are viewing a non-executed query object, you haven't actually retrieved anything at all yet. book.cakephp.org/3.0/en/orm/… – Gnarl

Would you mind a having a look at my original question. I added the output of debug($query->all);. Thanks. – Tachymetry

@Tachymetry You need to include the foreign key (TicketTypes.event_id) in your containments select(), otherwise the results cannot be stitched together, I've overlooked that. Normally that should trigger an error, not sure why it doesn't do that anymore (might be a bug, might have been an intentional change). book.cakephp.org/3.0/en/orm/…. I'll update my answer later on. – Gnarl

Now we are talking! Thank you very much for you help and for being patient. Have a nice day! – Tachymetry

a perfect answer, you rock ndm :) – Sliding