问题:

我有一个蛋糕。x查询对象具有两个嵌套的关联Organizations.Positions.Skills，该关联被设置为视图变量$Organizations。我试图通过第一个嵌套关联中的列对查询的结果顶级数组进行排序。也就是说，我想按Positions中的一列(特别是Positions.Ended)对$Organizations进行排序。

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

模型信息

组织有很多职位

position 有很多 Skills

我做过的研究

<

命令选项/h2>

根据食谱find()有一个顺序选项:find()->order(['field']);或find('all', ['order'=>'field DESC']);但是，这只适用于正在调用find()的表中的字段。在本例中，组织。例如，它通常是这样使用的。

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

但是，试图将它用于嵌套关联不工作:

$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'

并将其更改为指向将要嵌套的字段也不起作用:

//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'

在这两种情况下，sql错误都是在cakephp执行查询生成的PDO语句时创建的。

排序选项

同样，根据cookbook，急切加载/关联有'sort'选项:$ this -> loadModel("组织");

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

但是，这只是对嵌套的关联进行排序。具体来说，它对嵌套的关联进行排序。它不按嵌套关联对整个结果集进行排序(因此，是一个多维排序)。

例如:

组织C (org id 1)有两个职位:

1. 5的位置。结束2012年
2. 位置4。结束2014年

组织B (org id 2)有两个职位:

1. Position 3截止2013年
2. Position 2截止2015年

上面的代码和数据导致在计算查询时生成以下数组:

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'
      ]
    ]
  ]
]

其他研究

同样，在我的研究中出现了以下stackoverflow问题:

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 

此外，我知道PHP有自己的排序函数，如sort()和multisort();但是，这些只能在查询求值后调用(通过foreach)。或者，调用$organization->toArray()，然后使用multisort;但是，这必须在视图中完成，这会打破MVC的关注点分离约定(数据和查询由控制器和模型操作，而不是视图!)，并且会非常低效，因为它将在页面加载时被调用。

那么，我如何排序cakephp查询其嵌套的关联?

或者，更简单地说，我如何对查询进行排序，以便在求值时生成以下数组:

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'
      ]
    ]
  ]
]

<标题>的背景,背景:

我正在用cakephp 3.2为自己建立一个[投资组合网站][7]来展示我的web开发技能，并帮助我寻求开发职业。对于我的简历页面，我正在用嵌套的手风琴来组织大量的数据，以模仿招聘人员希望在实际简历中看到的简历风格。因此，我的视图做了以下操作:

1. 遍历顶级视图变量(组织)
2. 显示组织详细信息
3. 遍历该组织的位置(仍在1内)
4. 渲染位置细节
5. 循环该职位的相关技能
6. 渲染每个技能，并提供相应的链接来过滤该技能。

列表项

$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');

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

$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;
});

$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;
    });
});

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