How to create this complex database query in CakePHP 3.0?

Question

This is my database project, or at least the tables related to this issue.

I want to create a query that returns a single page (the search will be based on an attribute path), with a corresponding container with associated child containers in streaming form, and all these containers must individually have their own (preferably in the correct order sorted by the column indexfrom the blocks_pages table).

Can someone let me know how to wrap everything using the query builder? Or, if this is not possible, can this be done using the new map / reduce function since the after-find function has been removed?

, , , Article-box .

Answer 1

$pagesTable
  ->find()
  ->where(['path' => $myPath])
  ->contain([
   'Containers.ChildContainers' => function($q) {
      return $q->formatResults(function($results) {
        return $results->map(function($container) {
            $container->nested = $container->source()
              ->find('children', ['for' => $container->id])
              ->find('threaded')
              ->contain(['Blocks']);
            return $container;
        });
      });
   },
   'Containers.ChildContainers.Blocks'
  ])