Следната SQL заявка в phpMyAdmin връща точно това, което очаквам:
SELECT d.office_id, o.city, sum(d.`nb_followers`) as total_followers FROM `data` as d
LEFT JOIN offices as o on d.office_id = o.id
WHERE d.`year` = 2014 AND d.`month` = 10 AND d.`day` = 01
Group by d.`office_id`
ORDER BY total_followers DESC limit 10
В моя контролер:
$this->Paginator->virtualFields = array(
'followers' => 'SUM(Data.nb_followers)',
'id' => 'Office.office_id'
);
$this->Paginator->settings = array(
'fields' => array('id', 'Office.city', 'Data.followers'),
'joins' => array(
array(
'table' => 'data',
'alias' => 'Data',
'type' => 'inner',
'conditions' => array(
'Office.id = Data.office_id',
'year = ' . date('Y', mktime(0, 0, 0, date('m'), 1, date('Y'))),
'month = ' . date('m', mktime(0, 0, 0, date('m'), 1, date('Y'))),
'day = 01'
)
)
),
'group' => array('Data.id'), //Mal positionné à la base mais vérifier si activé n'impacte pas les résultats
'order' => array('Data.followers' => 'desc')
//'limit' => 1
);
$dataTop = $this->paginate('Office');
Но получавам тази грешка:
Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Data.followers' in 'field list'
SQL заявка: SELECT Office
.id
, Office
.city
, Data
.followers
FROM ebd
.offices
AS Office
inner JOIN ebd
.data
AS Data
ON (Office
.id
= Data
.office_id
И година = 2014 И месец = 10 И ден = 01) WHERE 1 = 1 GROUP BY Data
.id
LIMIT 20
Имам нужда от вашата помощ, благодаря
'followers' => 'SUM(Data.nb_followers)'
в настройките на пагинатора в полетата - person gmponos   schedule 29.10.2014