通过嵌套关联在cakephp 3中排序查询结果 [英] Sort query results by nested association in cakephp 3
问题描述
问题：
我有一个带有两个嵌套关联的cakephp 3.x查询对象， Organizations.Positions.Skills ，即设置为视图变量 $ Organizations 。我试图通过第一个嵌套关联中的列排序查询的结果顶级数组。也就是说，我想通过 Positions 中的列排序 $ Organizations ，特别是 Positions。已结束）。
public function index（）
{
$ this-> loadModel（'Organizations'）;
$ this-> set（'Organisations'，
$ this-> Organizations-> find（'all'）
- > contain（[// eager loading
'Positions.Skills'
]）
）;
}
模型信息
机构有许多职位
职位有许多技能
已完成的研究
订单选项
根据 cookbook find（）有一个订单选项： find（） - > ; order（['field']）; 或 find（'all'，['order'=>'field DESC']）;
然而，这只适用于表find（）被调用的字段。在这种情况下，组织。例如，这是它通常使用的方式。
//这个工程。
//按DESC中组织的名称排序组织。
$ this-> loadModel（'Organizations'）;
$ this-> set（'Organisations'，
$ this-> Organizations-> find（'all'）
- > contain（[// eager loading
'Positions.Skills'
]）
- > order（['organization DESC']）
）;
但是，尝试将其用于嵌套关联不起作用：
$ this- > set（'Organizations'，
this-> Organizations-> find（...）
- > contains（...）
- > order DESC']）
）;
错误：SQLSTATE [42S22]：未找到列：1054未知列.Positions.ended'in'order clause'
并修改它以引用嵌套也不起作用：
//也不起作用。
$ this-> set（'Organizations'，
$ this-> Organizations-> find（...）
- > contains（...）
- > order（[Positions.ended DESC]）
）;
错误：SQLSTATE [42S22]：未找到列：1054'order clause'中的未知列'Positions.ended'
在这两种情况下，当cakephp执行查询生成的PDO语句时，将创建sql错误。
排序选项
同样，根据 cookbook ，eager加载/关联具有'sort'选项：
$ this-> loadModel（'Organizations'）;
$ this-> set（'Organizations'，
$ this-> Organizations-> find（'all'）
- > contains（[// eager loading
'Positions.Skills'，
'Positions'=& [
'sort'=> ['Positions.ended'=>'DESC']
]
]）
）;
但是，这只会对嵌套的关联进行排序。具体来说，它会对嵌套的关联进行排序。它不会通过嵌套关联（ergo，多维排序）对整个结果集进行排序。
例如：
佛罗里达科技组织（org id 1）有两个职位：
- 实验室技术员。 2012年12月
- 研究助理。 2014年年底
并且今天的机构（org id 2）有两个职位：
- 2013年结束的市场数据分析师
- 2015年完结的完整网络开发者
上面的代码和数据导致在查询被计算时生成以下数组：
组织=> [
0 => [
'organization => 'plus Marketing Group'，
positions => [
0 => [
'position'=> '能源销售'，
'ending => '2016'
]
]
]，
1 => [
'organization => 'Florida Tech'，
positions => [
0 => [
'position'=> '研究助理'，
'ending => '2014'
]，
1 => [
'position'=> 'Lab Technician'，
'ended => '2012'
]
]
]，
2 => [
'organization'=> 'Fit In Today'，
positions => [
0 => [
'position'=> 'Full Stack Web Developer'，
'ended => '2015'
]，
1 => [
'position'=> 'market Data Analyst'，
'ended => '2013'
]
]
]
]
< h2>其他研究
同样，我的研究中出现了以下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查询进行排序？
或者，更简单地说，如何对查询进行排序/排序，以便在评估时生成以下数组：
组织= [
0 =>。 [
'organization => 'Plus Marketing Group'，
positions => [
0 => [
'position'=> '能源销售'，
'ending => '2016'
]
]
]，
0 => [
'organization'=> 'Fit In Today'，
'positions'=> [
0 => [
'position'=> 'Full Stack Web Developer'，
'ended => '2015'
]，
1 => [
'position'=> 'market Data Analyst'，
'ended => '2013'
]
]
]，
1 => [
'organization => 'Florida Tech'，
'positions'=> [
0 => [
'position'=> '研究助理'，
'ending => '2014'
]，
1 => [
'position'=> 'Lab Technician'，
'ended => '2012'
]
]
]
]
< h1> Background&上下文：
我正在为cakephp 3.2创建一个[投资组合网站] [7]，展示我的web开发技能，并协助我开发一个开发职业。对于我的简历页面，我使用嵌套手风琴组织大量数据，以模仿招聘人员在实际简历上看到的简历样式。因此，我的观点如下：
- 循环浏览顶层视图变量（组织）
- 呈现组织详细信息
- 循环访问该组织的职位（仍在1内）
- 渲染位置细节
- 循环浏览职位的相关技能
div class =h2_lin>解决方案
正在检索 hasOne 和 belongsTo 通过主查询上的联接。 hasMany 关联正在单独的查询中检索，因此当您尝试引用 Positions 的字段时出现错误。
您想要的应该是相当容易解决，在SQL级别以及PHP级别。
SQL级别
在SQL级别上，您可以加入 Positions ，并按计算 max（Positions.ended）列，例如
$ this-> b - > find（'all'）
- > select（['max_ended'=> $ this-> Organizations-> query（） - > func Positions.ended'）]）
- > select（$ this->组织）
- > contains（[
'Positions.Skills'，
' => [
'sort'=> ['Positions.ended'=>'DESC']
]
]）
- > leftJoinWith ）
- > order（[
'max_ended'=>'DESC'
]）
- > group（'Organizations.id'）;
这就是应该给你想要的结果。然而在巨大的桌子上它可能相当慢。查询将类似于
SELECT
MAX（Positions.ended）AS max_ended ，
...
FROM
组织机构
LEFT JOIN
位置职位
ON Organizations.id =（
Positions.organization_id
）
GROUP BY
Organizations.id
ORDER BY
max_ended DESC
PHP级别
在PHP级别上，使用集合也很容易解决（注意，查询是集合 - / p>
$ 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;
}）;
}）;
请参阅
- Cookbook>数据库访问& ORM>查询生成器>使用SQL函数
- Cookbook>数据库访问& ORM>查询构建器>使用leftJoinWith
- Cookbook>数据库访问& ORM>查询生成器>查询是集合对象
- Cookbook>数据库访问& ORM>查询生成器>添加计算字段
- 食谱>收藏>排序
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, Florida Tech (org id 1), has two positions:
- Lab Technician. Ended 2012
- Research Assistant. Ended 2014
And the Organization, Fit In Today (org id 2), has two positions:
- Market Data Analyst Ended 2013
- Full Stack Web Developer Ended 2015
The above code and data results in the following array being generated when the query is evaluated:
Organizations => [
0 => [
'organization => 'Plus Marketing Group',
positions => [
0 => [
'position' => 'Energy Sales',
'ended => '2016'
]
]
],
1 => [
'organization => 'Florida Tech',
positions => [
0 => [
'position' => 'Research Assistant',
'ended => '2014'
],
1 => [
'position' => 'Lab Technician',
'ended => '2012'
]
]
],
2 => [
'organization' => 'Fit In Today',
positions => [
0 => [
'position' => 'Full Stack Web Developer',
'ended => '2015'
],
1 => [
'position' => 'market Data Analyst',
'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 => 'Plus Marketing Group',
positions => [
0 => [
'position' => 'Energy Sales',
'ended => '2016'
]
]
],
0 => [
'organization' => 'Fit In Today',
'positions' => [
0 => [
'position' => 'Full Stack Web Developer',
'ended => '2015'
],
1 => [
'position' => 'market Data Analyst',
'ended => '2013'
]
]
],
1 => [
'organization => 'Florida Tech',
'positions' => [
0 => [
'position' => 'Research Assistant',
'ended => '2014'
],
1 => [
'position' => 'Lab Technician',
'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:
- Looping through the top level view variable (Organizations)
- Rendering the organization details
- Looping through that organization's positions (still inside 1)
- render the position details
- loop through the position's relevant skills
- render each skill w/ the appropriate link to filter by that skill.
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. It may however be rather slow on huge tables. 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), 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
这篇关于通过嵌套关联在cakephp 3中排序查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
