CakePHP 3 Query Builder missing contain fields when specifying select fields

Question

I have a fairly simple query that generates a different SQL statement, with regard to the contained fields, depending on whether or not I specify fields to select. I know that is very confusing, so I will try to describe the problem with code.

I have the following associations set up, and they work fine with a simple find(), but not when I try to use find()->select.

CollectionsTable:

    $this->belongsTo('IconMedia', [
        'foreignKey' => 'icon_media_id',
        'className' => 'Media'
    ]);
    $this->belongsTo('HeroMedia', [
        'foreignKey' => 'hero_media_id',
        'className' => 'Media'
    ]);

MediaTable:

    $this->hasMany('CollectionsIconMedia', [
        'foreignKey' => 'icon_media_id',
        'className' => 'Collections'
    ]);
    $this->hasMany('CollectionsHeroMedia', [
        'foreignKey' => 'hero_media_id',
        'className' => 'Collections'
    ]);

The full, simple version of the query, which returns all fields, including the fields for the associated tables HeroMedia and IconMedia.

$this->Collections->find()
        ->contain(['Participations', 'HeroMedia', 'IconMedia']);

generates

   SELECT Collections.id AS `Collections__id`, 
          Collections.name AS `Collections__name`, 
          Collections.slug AS `Collections__slug`, 
          Collections.description AS `Collections__description`, 
          Collections.icon_media_id AS `Collections__icon_media_id`, 
          Collections.hero_media_id AS `Collections__hero_media_id`, 
          Collections.user_id AS `Collections__user_id`, 
          Collections.created AS `Collections__created`, 
          Collections.modified AS `Collections__modified`, 
          HeroMedia.id AS `HeroMedia__id`, 
          HeroMedia.file AS `HeroMedia__file`, 
          HeroMedia.description AS `HeroMedia__description`, 
          HeroMedia.caption AS `HeroMedia__caption`, 
          HeroMedia.source AS `HeroMedia__source`, 
          IconMedia.id AS `IconMedia__id`, 
          IconMedia.file AS `IconMedia__file`, 
          IconMedia.description AS `IconMedia__description`, 
          IconMedia.caption AS `IconMedia__caption`, 
          IconMedia.source AS `IconMedia__source` 
     FROM collections Collections 
LEFT JOIN media HeroMedia ON HeroMedia.id = (Collections.hero_media_id) 
LEFT JOIN media IconMedia ON IconMedia.id = (Collections.icon_media_id)

This is exactly what I expect. However, I would like to limit the selected fields returned from the collection table. I tried the following statement:

$this->Collections->find()
        ->select( [ 'id', 'name', 'slug', 'description', 'icon_media_id', 'hero_media_id' ] )
        ->contain(['Participations', 'HeroMedia', 'IconMedia']);

This unfortunately generates the following SQL. Notice that the JOINs are still present, but the SELECTs for IconMedia and HeroMedia are gone.

   SELECT Collections.id AS `Collections__id`, 
          Collections.name AS `Collections__name`, 
          Collections.slug AS `Collections__slug`, 
          Collections.description AS `Collections__description`, 
          Collections.icon_media_id AS `Collections__icon_media_id`, 
          Collections.hero_media_id AS `Collections__hero_media_id` 
     FROM collections Collections 
LEFT JOIN media HeroMedia ON HeroMedia.id = (Collections.hero_media_id) 
LEFT JOIN media IconMedia ON IconMedia.id = (Collections.icon_media_id)

Maybe there is a problem with the virtual tables HeroMedia and IconMedia (not sure of the proper CakePHP name for them), but they seem to work properly for the simple find() without ->select. I've tried reading through the Query Builder and Associations Docs, but haven't found anything to explain this phenomenon.

Answer 1

You need to use Table::aliasField() on all your fields.

->contain([
    $this->aliasField('name'),
    'OtherTable.field',
    'YetAnotherTable.field'
]);

• Selecting All Fields From a Table
• Passing Conditions to Contain Check the 2nd half of this section
• See enableAutoFields()