[FIXED] how to converting sql to orm cakephp2

Issue

So I have this query that I am trying to convert to cake ORM and I do not know how to go about it

SELECT users.id, users.email, users.phone, SUM(user_orders.quantity ) , SUM(user_orders.total_price )
FROM `users`
JOIN `user_orders` ON users.id = user_orders.user_id
WHERE users.role_id =2
AND user_orders.status =1
GROUP BY users.id
ORDER BY SUM( user_orders.quantity ) DESC
LIMIT 200

Solution

Inside your Users model:

$this->virtualFields = array(
    'sum_user_orders_quantity' => 'SUM(UserOrders.quantity)',
    'sum_user_orders_total_price' => 'SUM(UserOrders.total_price)',
);
$options = array(
    'fields' => array(
        'Users.id',
        'Users.email',
        'Users.phone',
        'UserOrders.sum_user_orders_quantity',
        'UserOrders.sum_user_orders_total_price',
    ),
    'joins' => array(
        array(
            'conditions' => array(
                'Users.id = UserOrders.user_id',
            ),
            'table' => 'user_orders',
            'alias' => 'UserOrders',
            'type' => 'join',
        ),
    ),
    'conditions' => array(
        'Users.role_id' => '2',
        'UserOrders.status' => '1',
    ),
    'group' => array(
        'Users.id',
    ),
    'order' => array(
        'SUM(UserOrders.quantity)' => 'desc',
    ),
    'limit' => '200',
    'contain' => array(
        'UserOrders',
    ),
);
$data = $this->find('all', $options);

If you're new to using CakePHP 2.x, I recommend taking a look at Dogmatic to give you some ideas on converting SQL to CakePHP query syntax.

Answered By - Warren Sergent

Answer Checked By - Clifford M. (PHPFixing Volunteer)