CakePHP 3: Changing structure of result set

Question

I'm new to CakePHP and have the following problem:

I have the tables "Images", "Keywords" and "KeywordCategories". Every image can have many keywords (many to many) and every keyword has a category (many to one). Retrieving a list of images with

$images = $this->Images->find()->contain(['Keywords',  'Keywords.KeywordCategories']);

returns a result structure like this:

[
   {
      "id":1,
      "keywords":[
         {
            "keyword":"Dog",
            "keyword_category":{
               "title":"Animal"
            }
         },
         {
            "keyword":"Cat",
            "keyword_category":{
               "title":"Animal"
            }
         },
         {
            "keyword":"Black",
            "keyword_category":{
               "title":"Color"
            }
         }
      ]
   }
]

That's fine but I want the keywords to be grouped by its keyword-category in a structure like this:

[
   {
      "id":1,
      "keyword_categories":[
         {
            "title":"Animal",
            "keywords":[
               {
                  "keyword":"Dog"
               },
               {
                  "keyword":"Cat"
               }
            ]
         },
         {
            "title":"Color",
            "keywords":[
               {
                  "keyword":"Black"
               }
            ]
         }
      ]
   }
]

Any idea how I can achieve that with a CakePHP query?

Answer 1

That format is pretty much a reversed contain, that's not possible with using only the ORMs association auto-magic. You would have to fetch the associated data separately, filter it, and inject it into the image results... you could even create a custom association class that contains, fetches and stitches the results together, but that's kind of an overkill if you ask me.

Since you'd have to do some additional formatting anyways (and being it just for stitching the results together), I would simply use some collection formatting foo instead, something along the lines of

// ...
->find()
->contain(['Keywords', 'Keywords.KeywordCategories'])
->formatResults(function ($results) {
    /* @var $results \Cake\Datasource\ResultSetInterface|\Cake\Collection\CollectionInterface */
    return $results->map(
        function ($image) {
            $image['keyword_categories'] =
                collection($image['keywords'])
                    ->groupBy('keyword_category.title')
                    ->map(function ($keywords, $category) {
                        foreach ($keywords as &$keyword) {
                            unset($keyword['keyword_category']);
                        }
                        return [
                            'title' => $category,
                            'keywords' => $keywords
                        ];
                    })
                    ->toList();
            unset($image['keywords']);
            return $image;
        }
    );
});

^{* untested example code for illustration purposes}

ie create a calculated field named keyword_categories on each image result, consisting of the contained keywords, grouped by their associated categories title, with the keywords nested in an array with title and keywords fields where the category is being removed from the keywords, and finally re-index the whole thing as a basic numerically indexed array.

See also

• Cookbook > Database Access & ORM > Query Builder > Adding Calculated Fields
• Cookbook > Collections > Grouping and Counting > Collection::groupBy()
• Cookbook > Collections > Iterating > Collection::map()
• API > \Cake\Collection\CollectionInterface::toArray()
• API > \Cake\Collection\CollectionInterface::toList()