converting simple query into cakephp find query for paginate in controller

0
votes

I have following associations

  1. users hasMany domains

  2. domains hasMany visiters

  3. domains hasMany pageviews

And following is the query 

    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

I want to convert into cakephp find query for paginate as cakephp does not allow $this->paginate("mysql query here");

2
cakephpcakephp-1.3
what do your complex queries look like :)? Have you tried anything? – AD7six
I can join (domains with visitors) and (domains with pageviews) using cakephp associations but how to join these two I dont have any idea about that – Garry

2 Answers

1
votes

I would recommend giving this tool a go.

http://dogmatic69.com/sql-to-cakephp-find-converter

This will create you a custom find method for this query, allowing you to implement it in your model.

0
votes

You can simply execute your query as

$this->ModelName->query('your_sql_query');

This will do your work for complex queries like above