cakephp complex queries

Question

How can I transform the SQL statement into the CakePHP Code? I am able to get the group by result. But how can I add the inner Select Count(*) statement?

I've updated the code under "Revised" However, I am getting this error. Parse error: syntax error, unexpected 'INCOMPLETE' (T_STRING), expecting ')'. I am sure I check my open and close brackets.

SQL Statement below

SELECT 
    `id` , 
    `mtpToken` , 
    `mtpCreator_id` , 
    `school_id` , 
    `user_id` , 
    `mtpStatus` , 
    `created` , 
    `modified` , 
    (
        (SELECT COUNT( * ) FROM mtpScreenings 
        WHERE (mtpStatus = 'INCOMPLETE' OR mtpStatus = 'HALFWAY') AND mtpToken = `mtpToken` )
    ) AS `mainStatus` 

    FROM `mtpScreenings` WHERE `mtpScreenings`.`school_id` = 15 GROUP BY `mtpToken`

CakePHP Code below

    $setting = $this->paginate = array(
        conditions' => array('MtpScreening.school_id' => 15),
        'recursive' => -1,
        'fields' => array('MtpScreening.field1'),
        'group' => array('MtpScreening.mtpToken'),
        'limit' => 1000
    );

Revised

    $setting = $this->paginate = array(
        conditions' => array('MtpScreening.school_id' => 15),
        'recursive' => -1,
        'fields' => array('SELECT COUNT(*) FROM mtpScreenings 
        WHERE (mtpStatus = "INCOMPLETE" OR mtpStatus = "HALFWAY") AND mtpToken = `mtpToken`
    ) AS mainStatus'),
        'group' => array('MtpScreening.mtpToken'),
        'limit' => 1000
    );

Is the only difference here that virtual field you're creating in the subselect? If so, you can set it as a virtual field in the model you're querying, or you can define it as a custom field literally by cut and pasting the brackets and the as mainStatus part and pasting it as a field into your fields array — scrowler
@Manwal not necessary, a virtual field is built specifically for this — scrowler

Manwal Manwal · Accepted Answer · 2014-08-19T04:24:25

Virtual Field

Code example of virtual field:

public $virtualFields = array(
  'count' => "SELECT COUNT( * ) FROM MtpScreening 
    WHERE (MtpScreening.mtpStatus = 'INCOMPLETE' OR MtpScreening.mtpStatus = 'HALFWAY') AND MtpScreening.mtpToken = 'mtpToken'"    
   );

This code will produce virtual field:

$this->find('all', array(
               'fields'=>array(
                        'MtpScreening.id',
                        'MtpScreening.mtpToken', 
                        'MtpScreening.mtpCreator_id', 
                        'MtpScreening.school_id',
                        'MtpScreening.count'/*virtual field*/
                       )
                )
           );

Pagination and virtual fields

Since virtual fields behave much like regular fields when doing find’s, Controller::paginate() will be able to sort by virtual fields too.

cakephp complex queries

1 Answers

Virtual Field