在cakePHP 1.3中对hasMany关系的复杂查找查询 [英] Complex find query for hasMany relationship in cakePHP 1.3
问题描述
几个月来我一直在忙于CakePHP框架，我真的很喜欢它.目前，我正在开发一个非常新的项目，它的工作可以正常进行(我认为...)，但是我对编写的某些代码感到不舒服.实际上，我应该优化分页条件查询，以便立即获得正确的结果(现在，我通过一堆Set :: extract方法调用来操纵结果集.
I have been busy with the cakePHP framework for a couple of months now and I really love it. At the moment I'm working on a very new project and it does the job like it should (I think ...) but I feel uncomfortable with some code I wrote. In fact I should optimize my paginate conditions query so I get immediately the right results (right now I manipulate the result set by a bunch of Set::extract method calls.
我将概述应用程序的相关方面.我有一个模型"Site"，它与模型"SiteMeta"具有hasMany关系.最后一个表格如下:id，site_id，键，值，已创建.
I'll sketch the relevant aspects of the application. I have a model 'Site' who has a hasMany relationship with the model 'SiteMeta'. This last table looks as follow: id, site_id, key, value, created.
在最后一个模型中，我记录了各个时期站点的多个值.我要存储的密钥的名称(例如alexarank，google pagerank等)，当然也有值.在给定的间隔内，我让我的应用程序更新此数据库，以便可以跟踪此值的演变.
In this last model I record several values of the site at various periods. The name of the key I want to store (e.g. alexarank, google pagerank, ...), and off course also the value. At a given interval I let my app update this database so I can track evolution of this values.
现在我的问题是这个.
在各个网站的概述页面上(控制器=>网站，操作=>索引)，我想显示该网站的当前页面排名.因此，我需要一个确切的SiteMeta记录，其中创建的"字段是最高的，并且键"中的值应与单词"pagerank"匹配.我已经尝试了一些在网上阅读的内容，但是没有一个起作用(可容纳的，bindmodel等).可能是我做错了.
On the overview page of the various websites (controller => Sites, action => index) I'd like to show the CURRENT pagerank of the website. Thus I need one exact SiteMeta record where the 'created' field is the highest and the value in 'key' should be matching the word 'pagerank'. I've tried several things I read on the net but got none of them working (containable, bindmodel, etc.). Probably I'm doing something wrong.
现在，当我执行$ this-> paginate
Right now I get results like this when I do a $this->paginate
Array
(
[0] => Array
(
[Site] => Array
(
[id] => 1
[parent_id] => 0
[title] => test
[url] => http://www.test.com
[slug] => www_test_com
[keywords] => cpc,seo
[language_id] => 1
)
[SiteMeta] => Array
(
[0] => Array
(
[id] => 1
[site_id] => 1
[key] => pagerank
[value] => 5
[created] => 2010-08-03 00:00:00
)
[1] => Array
(
[id] => 2
[site_id] => 1
[key] => pagerank
[value] => 2
[created] => 2010-08-17 00:00:00
)
[2] => Array
(
[id] => 5
[site_id] => 1
[key] => alexa
[value] => 1900000
[created] => 2010-08-10 17:39:06
)
)
)
要获得pagerank，我只需遍历所有站点并操纵此数组即可.接下来，我用Set :: extract过滤结果.但这感觉不太对劲:)
To get the pagerank I just loop through all the sites and manipulate this array I get. Next I filter the results with Set::extract. But this doens't feel quite right :)
$sitesToCheck = $this->paginate($this->_searchConditions($this->params));
foreach($sitesToCheck as $site) {
$pagerank = $this->_getPageRank($site['Site']);
$alexa = $this->_getAlexa($site['Site']);
$site['Site']['pagerank'] = $pagerank;
$sites[] = $site;
}
if (isset($this->params['named']['gpr']) && $this->params['named']['gpr']) {
$rank = explode('-', $this->params['named']['gpr']);
$min = $rank[0];$max = $rank[1];
$sites = Set::extract('/Site[pagerank<=' . $max . '][pagerank>=' . $min .']', $sites);
}
$this->set(compact('sites', 'direction'));
你们能帮我考虑一下解决方案吗?预先感谢.
Could you guys please help me to think about a solution for this? Thanks in advance.
感谢您的贡献.我尝试了这些选项(也可以使用bindmodel进行某些操作，但也无法正常工作)，但仍然无法像预期的那样工作.如果我定义这个
Thanks for the contributions. I tried these options (also something with bindmodel but not working also) but still can't get this to work like it should be. If I define this
$this->paginate = array(
'joins'=> array(
array(
'table'=>'site_metas',
'alias'=>'SiteMeta',
'type' =>'inner',
'conditions' =>array('Site.id = SiteMeta.site_id')
)
),
);
我得到重复的结果 我有一个具有3个不同SiteMeta记录的站点和一个具有2个不同记录的站点. 分页方法总共返回了5条记录.可能有一个简单的解决方案，但我无法弄清楚:)
I get duplicate results I have a site with 3 different SiteMeta records and a site with 2 different record. The paginate method returns me 5 records in total. There's probably an easy solution for this, but I can't figure it out :)
我也尝试自己编写一个sql查询，但在这种情况下似乎无法使用分页魔术.以下是我想模仿的分页选项和条件的查询.查询返回的完全是我想要的.
Also I tried to write a sql query myself, but seems I can't use the pagination magic in that case. Query I'd like to imitate with pagination options and conditions is the following. The query returns exactly as I would like to get.
$sites = $this->Site->query('SELECT * FROM sites Site, site_metas SiteMeta WHERE SiteMeta.id = (select SiteMeta.id from site_metas SiteMeta WHERE Site.id = SiteMeta.site_id AND SiteMeta.key = \'pagerank\' order by created desc limit 0,1 )');
推荐答案
在尝试以hasMany关系检索数据时，cakephp默认情况下不会加入表.如果您选择joins，则可以执行以下操作:
As you are trying to retrieve data in a hasMany relationship, cakephp doesn't join the tables by default. If you go for joins you can do something like:
$this->paginate = array(
'joins'=>array(
array(
'table'=>'accounts',
'alias'=>'Account',
'type' =>'inner',
'conditions' =>array('User.id = Account.user_id')
)
),
'conditions'=> array('OR' =>
array(
'Account.name'=>$this->params['named']['nickname'],
'User.id' => 5)
)
);
$users = $this->paginate();
$this->set('users',$users);
debug($users);
$this->render('/users/index');
您当然必须根据自己的需要进行调整.关于连接的更多，就像已经在另一个答案中提到的那样.
You have to fit this according to your needs of course. More on joins, like already mentioned in another answer.
这是因为您缺少第二个条件".请参阅我的代码段.第一个条件"仅说明联接发生的位置，而第二个条件"进行实际选择.
Edit 1: This is because you are missing the second 'conditions'. See my code snippet. The first 'conditions' just states where the join happens, whereas the second 'conditions' makes the actual selection.
Edit 2: Here some info on how to write conditions in order to select needed data. You may want to use the max function of your rdbms on column created in your refined condition.
不可将可容纳对象和联接一起使用.摘自手册:使用具有可容纳行为的联接可能会导致某些SQL错误(重复的表)，因此，如果您的主要目标是基于相关数据执行搜索，则需要使用joins方法作为可容纳的替代方法. Containable最适合限制find语句带来的相关数据量.我想您还没有尝试过我的编辑2.
Edit 3: Containable and joins should not be used together. Quoted from the manual: Using joins with Containable behavior could lead to some SQL errors (duplicate tables), so you need to use the joins method as an alternative for Containable if your main goal is to perform searches based on related data. Containable is best suited to restricting the amount of related data brought by a find statement. You have not tried my edit 2 yet, I think.
一种可能的解决方案是在表Sites中添加字段last_updated.然后，可以在第二个条件语句中使用此字段与SiteMeta.created值进行比较.
Edit 4: One possible solution could be to add a field last_updated to the table Sites. This field can then be used in the second conditions statement to compare with the SiteMeta.created value.
这篇关于在cakePHP 1.3中对hasMany关系的复杂查找查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
