Complex query with COUNT

0DownUp

Hi.

There are 2 tables linked like Brand-hasMany-Device. Devices have an attribute 'sold' (0 - was sold / 1 - wasn't). So, I want to retrieve information about the concrete brand + count of abaliable devices and count of sold devices. It's possible to write an SQL-query like


SELECT
    Brand.id,
    Brand.name,
    Brand.description,
    // ...other fields...
    (SELECT COUNT (Device.id) FROM Device JOIN Brand ON Device.id=Brand.id) AS total,
    (SELECT COUNT (Device.id) FROM Device JOIN Brand ON Device.id=Brand.id AND Device.sold=0) AS avaliable,
    (SELECT COUNT (Device.id) FROM Device JOIN Brand ON Device.id=Brand.id AND Device.sold=1) AS sold
FROM Brand
WHERE Brand.id = $id

and use it in $this->query($query), but it's extreme measures, I think.

Give me a tip how to perform such a query using Cake's facilities, please.

P.S. It seems it's possible to perform using counterScope, but I don't want to create additional fields in tables. Are there other alternatives?

Asked by ImmaculatePine, on 26/11/11

1 Answer

2DownUp

I think you should add a field count more than run a query count.

But if you don't want you can do like below:


$data = $this->Brand->find('first', array(
    'conditions' => array('Brand.id' => $id),
    'contain' => false
));

if( isset( $data['Brand'] ) )
{
     $count1 = $this->Brand->Device->find('count', array(
        'conditions' => array( 'Device.brand_id' => $data['Brand']['id'] )
     ));
     $count2 = $this->Brand->Device->find('count', array(
        'conditions' => array( 'Device.brand_id' => $data['Brand']['id'], 'Device.sold' => 0 )
     ));
     $count3 = $this->Brand->Device->find('count', array(
        'conditions' => array( 'Device.brand_id' => $data['Brand']['id'] 'Device.sold' => 1 )
     ));
}

Answered by beBibietyeuon 27/11/11

<< previous next >>

Rating

0

Viewed

587 times

Last Activity

on 27/11/11

Related Questions