GROUP和COUNT()在CakePHP中老化 [英] GROUP and COUNT() ages in CakePHP
问题描述
我尝试按出生日期分组,并根据结果计数,使用CakePHP。这是我的查询。
$ data = $ this-> User-> find('all',array $ b'fields'=> array(
DATE_FORMAT(NOW(),'%Y') - DATE_FORMAT(User.dob,'%Y') - (DATE_FORMAT m%d') 'COUNT(id)'
),
' group'=>'age'
));
到目前为止,还不错。字段 User.dob
是出生日期,它是一个 DATETIME
字段。
事实上,它返回如下:
数组
(
[ 0] => Array
(
[0] => Array
(
[age] => 9
[COUNT(id)] => ; 1
)
)
[1] => Array
(
[0] =& b(
[age] => 10
[COUNT(id)] => 1
)
)
[ 2] => Array
(
[0] => Array
(
[age] => 11
[COUNT(id)] => ; 1
)
)
[3] => Array
(
[0] =& b(
[age] => 12
[COUNT(id)] => 8
)
)
[4] => Array
(
[0] => Array
(
[age] => 13
[COUNT(id)] => 1
)
)
当然必须有更好的方法。 p>
我甚至不能过滤它。此代码引发错误。未知栏'age'
$ data = $ this-> User-> find('all',array b $ b'conditions'=> array('age>'=> 20),
'fields'=> array(
DATE_FORMAT(NOW(),'%Y') - DATE_FORMAT(User.dob,'%Y') - (DATE_FORMAT(NOW(),'00 - %m-%d')< DATE_FORMAT(User.dob,'00 - %m-%d'))AS age,
'COUNT(id)'
),
'group'=>'age'
)
顺便说一下,这些是查询。
SELECT DATE_FORMAT(NOW(),'%Y') - DATE_FORMAT(User.dob,'%Y') - (DATE_FORMAT(NOW),'00 - %m- %d')
(年龄计算程序在 matt的博客。)
不认为你需要'COUNT(id)'
。你可以轻松地在PHP中计数:
foreach($ data as $ group){echo count($ group);}
要过滤,请检查原始字段,而不是派生字符:
'conditions'=> array('User.dob>'=> date('Ym-d',strtotime )))
无论如何,总是 Model-> query / code>如果需要。
I'm trying to group by date of birth and count based on the results, using CakePHP. Here's my query.
$data = $this->User->find('all', array(
'fields' => array(
"DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(User.dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(User.dob, '00-%m-%d')) AS age",
'COUNT(id)'
),
'group' => 'age'
));
So far, so good. The field User.dob
is date of birth, it's a DATETIME
field.
Thing is, it returns something like this:
Array
(
[0] => Array
(
[0] => Array
(
[age] => 9
[COUNT(id)] => 1
)
)
[1] => Array
(
[0] => Array
(
[age] => 10
[COUNT(id)] => 1
)
)
[2] => Array
(
[0] => Array
(
[age] => 11
[COUNT(id)] => 1
)
)
[3] => Array
(
[0] => Array
(
[age] => 12
[COUNT(id)] => 8
)
)
[4] => Array
(
[0] => Array
(
[age] => 13
[COUNT(id)] => 1
)
)
Surely there must be a better way.
And I can't even filter it. This code throws error. Unknown column 'age'
$data = $this->User->find('all', array(
'conditions' => array('age >' => 20),
'fields' => array(
"DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(User.dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(User.dob, '00-%m-%d')) AS age",
'COUNT(id)'
),
'group' => 'age'
));
By the way, these are the queries.
SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(User.dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(User.dob, '00-%m-%d')) AS age, COUNT(id) FROM `users` AS `User` WHERE 1 = 1 GROUP BY age
(The age calculation routine was found in matt's blog.)
First, I don't think you need 'COUNT(id)'
. You can always count that in PHP easily:
foreach($data as $group){echo count($group);}
To filter, check on the original field, not the derived one:
'conditions'=>array('User.dob >' => date('Y-m-d', strtotime('-20 years')))
Anyhow, there's always Model->query()
if you need.
这篇关于GROUP和COUNT()在CakePHP中老化的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!