Saya memiliki asosiasi berikut
pengguna memiliki banyak domain
domain memiliki banyak pengunjung
domain memiliki banyak tampilan halaman
Dan berikut ini pertanyaannya
SELECT
pv.name,pv.domain_id,pv.counter as page_visits, v.ip, v.counter as visits
FROM (
SELECT `domains`.`name` , `pageviews`.`domain_id` , COUNT( `pageviews`.`domain_id` ) as counter
FROM `pageviews`
LEFT JOIN `domains` ON ( `domains`.`id` = `pageviews`.`domain_id`
AND `domains`.`user_id` = '129' )
WHERE `pageviews`.`user_id` = '129'
GROUP BY `pageviews`.`domain_id`
ORDER BY COUNT( `pageviews`.`domain_id` ) DESC
) as pv
LEFT JOIN (
SELECT `domains`.`name` , `visitors`.`ip` , `visitors`.`domain_id` , COUNT( `visitors`.`domain_id` ) as counter
FROM `visitors`
LEFT JOIN `domains` ON ( `domains`.`id` = `visitors`.`domain_id`
AND `domains`.`user_id` = '129' )
WHERE `visitors`.`user_id` = '129'
GROUP BY `visitors`.`domain_id`
ORDER BY COUNT( `visitors`.`domain_id` ) DESC
) as v
ON pv.domain_id = v.domain_id
Saya ingin mengonversi menjadi kueri pencarian cakephp untuk paginasi karena cakephp tidak mengizinkan $this->paginate("mysql query here");