cakephp - 在 CakePHP 3 中检索相关数据 (hasMany)

Question

Events hasMany TicketTypes
TicketTypes belogsTo Events

我正在尝试检索具有关联票证类型的所有事件:

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

生成的 SQL 查询:

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

但我期望的是:

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)

这就是我的模型的配置方式:

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']
        ]); 
    }
}

这是调试我的查找查询的结果:

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'

    }

}

这是调试$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'

        },
    ...

正如您在这一行中看到的那样，查询不会返回'ticket_types' => [] 票证类型。

如何检索 TicketTypes 数据？

谢谢。

Answer 1

hasMany 关联正在单独的查询中检索

您对 CakePHP ORM 如何检索关联数据的假设是不正确的。

与在主查询中使用联接的 hasOne 和 belongsTo 关联不同，hasMany 和 belongsToMany 关联数据正在单独的查询中检索，该查询正在使用从主查询收集的外键值进行过滤，在您的情况下，该外键值将是 Events.id 列值。

查看 SQL 日志的其余部分，您应该会发现类似于以下内容的查询

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

该查询的结果将与主要结果拼接在一起，并在单个结果集中返回。

需要选择外键

第二个问题是您的包含select()调用缺少外键列(TicketTypes.event_id)，这是必需的，如果没有它，ORM无法将结果拼接在一起，因此票证类型不会出现在结果中。

引用文档:

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.

另请参阅

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