Sort query results by nested association in cakephp 3

Question

The Problem:

I have a cakephp 3.x query object with two nested associations, Organizations.Positions.Skills, that is being set to a view variable, $Organizations. I'm trying to sort the query's resulting top level array by a column in the first nested association. That is, I want to sort $Organizations by a column in Positions, specifically Positions.Ended).

public function index()
{
    $this->loadModel('Organizations');
    $this->set('Organizations',
            $this->Organizations->find('all')
            ->contain([ //eager loading
            'Positions.Skills'
             ])
    );
}

Model Info

Organizations has many Positions

Positions has many Skills

Research I've Done

order option

According to the cookbook find() has an order option: find()->order(['field']); or find('all', ['order'=>'field DESC']); However, this only applies to fields in the table find() is being called upon. In this case, Organizations. For example, this is how it's typically used.

//This works.
//Sorts Organizations by the name of the organization in DESC.
$this->loadModel('Organizations');
$this->set('Organizations',
        $this->Organizations->find('all')
        ->contain([ //eager loading
            'Positions.Skills'
         ])
        ->order(['organization DESC'])
);

but, trying to use it for nested associations doesn't work:

$this->set('Organizations',
  this->Organizations->find(...)
    ->contain(...)
    ->order(['Organizations.Positions.ended DESC'])
);

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Organizations.Positions.ended' in 'order clause'

and altering it to refer to the field that'll be nested doesn't work either:

//also doesn't work.
$this->set('Organizations',
  $this->Organizations->find(...)
    ->contain(...)
    ->order([Positions.ended DESC])
);

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Positions.ended' in 'order clause'

In both cases, the sql error is created when cakephp executes the PDO statement generated by the query.

sort option

Similarly, according to the cookbook, eager loading / associations has the 'sort' option: $this->loadModel('Organizations');

$this->set('Organizations',
    $this->Organizations->find('all')
    ->contain([ //eager loading
        'Positions.Skills',
        'Positions' => [
            'sort' => ['Positions.ended'=>'DESC']
        ]
    ])
);

But, this only sorts the nested association.Specifically, it sorts the associations that are nested. It does not sort the entire resulting set by the nested association (ergo, a multidimensional sort).

For example:

The Organization, Organization C (org id 1), has two positions:

1. Position 5. Ended 2012
2. Position 4. Ended 2014

And the Organization, Organization B (org id 2), has two positions:

1. Position 3 Ended 2013
2. Position 2 Ended 2015

The above code and data results in the following array being generated when the query is evaluated:

Organizations => [
  0 => [
    'organization' => 'Organization A',
    positions => [
      0 => [
        'position' => 'Position 1',
        'ended' => '2016'
      ]
    ]
  ],
  1 => [
    'organization' => 'Organization C',
    'positions' => [
      0 => [
        'position' => 'Position 4',
        'ended' => '2014'
      ],
      1 => [
        'position' => 'Position 5',
        'ended' => '2012'
      ]
    ]
  ],
  2 => [
    'organization' => 'Organization B',
    'positions' => [
      0 => [
        'position' => 'Position 2',
        'ended' => '2015'
      ],
      1 => [
        'position' => 'Position 3',
        'ended' => '2013'
      ]
    ]
  ]
]

other research

Likewise the following stackoverflow questions came up in my research:

http://stackoverflow.com/questions/26859700/cakephp-order-not-working

http://stackoverflow.com/questions/17670986/order-by-doesnt-work-with-custom-model-find-while-paginating

http://stackoverflow.com/questions/18958410/cakephp-paginate-and-sort-2nd-level-association

http://stackoverflow.com/questions/34705257/cakephp-paginate-and-sort-hasmany-association 

Furthermore, I do know that PHP has its own sorting functions like sort() and multisort(); but, those can only be called once the query has been evaluated (by foreach). Alternatively, there's calling $organization->toArray() then using multisort; but, this would have to be done in the view, would break the MVC convention of separations of concerns (data and queries are manipulated by the controller and model, not the view!), and would be quite inefficient as it'll be called while the page is loading.

How then, do I sort a cakephp query by its nested associations?

Or, put more simply, how do I order/sort the query to produce the following array upon evaluation:

Organizations => [
  0 => [
    'organization' => 'Organization A',
    'positions' => [
      0 => [
        'position' => 'Position 1',
        'ended' => '2016'
      ]
    ]
  ],
  0 => [
    'organization' => 'Organization B',
    'positions' => [
      0 => [
        'position' => 'Position 2',
        'ended' => '2015'
      ],
      1 => [
        'position' => 'Position 3',
        'ended' => '2013'
      ]
    ]
  ],
  1 => [
    'organization => 'Organization C',
    'positions' => [
      0 => [
        'position' => 'Position 4',
        'ended' => '2014'
       ],
      1 => [
        'position' => 'Position 5',
        'ended' => '2012'
      ]
    ]
  ]
]

Background & Context:

I'm building a [portfolio website][7] for myself with cakephp 3.2 to showcase my web dev skills and assist in my quest for a dev career. For my resume page, I'm organizing the massive amount of data with nested accordions to mimic the resume style recruiters would expect to see on an actual resume. As a result, my view does the following:

1. Looping through the top level view variable (Organizations)
   2. Rendering the organization details
   3. Looping through that organization's positions (still inside 1)
      4. render the position details
      5. loop through the position's relevant skills
         6. render each skill w/ the appropriate link to filter by that skill.

List item

Answer 1

Only hasOne and belongsTo associations are being retrieved via a join on the main query. hasMany associations are being retrieved in a separate queries, hence the errors when you try to refer to a field of Positions.

What you want should be fairly easy to solve, on SQL level, as well as on PHP level.

SQL level

On SQL level you could join in Positions, and order by a computed max(Positions.ended) column, like:

$this->Organizations
    ->find('all')
    ->select(['max_ended' => $this->Organizations->query()->func()->max('Positions.ended')])
    ->select($this->Organizations)
    ->contain([
        'Positions.Skills',
        'Positions' => [
            'sort' => ['Positions.ended' => 'DESC']
        ]
    ])
    ->leftJoinWith('Positions')
    ->order([
        'max_ended' => 'DESC'
    ])
    ->group('Organizations.id');

And that's all, that should give you the results that you want. The query will look something like:

SELECT
    MAX(Positions.ended) AS max_ended,
    ... 
FROM
    organizations Organizations     
LEFT JOIN
    positions Positions 
        ON Organizations.id = (
            Positions.organization_id
        )   
GROUP BY
    Organizations.id   
ORDER BY
    max_ended DESC

PHP level

On PHP level it's also pretty easy to solve to using collections (note that queries are collections - kind of), however it would only make sense if you'd intend to retrieve all rows, or must deal with a set of unordered rows for whatever reason... something like:

$Organizations->map(function ($organization) {
    $organization['positions'] =
        collection($organization['positions'])->sortBy('ended')->toList();
    return $organization;
});
$sorted = $sorted->sortBy(function ($organization) {
    return
        isset($organization['positions'][0]['ended']) ?
            $organization['positions'][0]['ended'] : 0;
});

This could also be implemented in a result formatter, so things happen on controller or model level if you insist.

$query->formatResults(function ($results) {
    /* @var $results \Cake\Datasource\ResultSetInterface|\Cake\Collection\CollectionInterface */
    $results = $results->map(function ($row) {
        $row['positions'] =
            collection($row['positions'])->sortBy('ended')->toList();
        return $row;
    });
    return $results->sortBy(function ($row) {
        return isset($row['positions'][0]['ended']) ?
            $row['positions'][0]['ended'] : 0;
    });
});

See

• Cookbook > Database Access & ORM > Query Builder > Using SQL Functions
• Cookbook > Database Access & ORM > Query Builder > Using leftJoinWith
• Cookbook > Database Access & ORM > Query Builder > Queries Are Collection Objects
• Cookbook > Database Access & ORM > Query Builder > Adding Calculated Fields
• Cookbook > Collections > Sorting

Answer 2

This worked for me:

$this->Organizations->find('all')
->contain(['Positions' => ['sort' => ['Positions.ended' => 'DESC']]])
->contain('Postions.Skills');