CODEWITHSUNDEEP
cakephpcakephp-1.3
Garry
Garry

Reputation: 595

converting simple query into cakephp find query for paginate in controller

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");

Upvotes: 0

Views: 348

Answers (2)

Anand G
Anand G

Reputation: 3200

You can simply execute your query as

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

This will do your work for complex queries like above

Upvotes: 0

David Yell
David Yell

Reputation: 11855

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.

Upvotes: 1

Related Questions