MANY_MANY and COUNT()

Hi,

my model ‘hotel’ has two MANY_MANY Relations: ‘relProperty’ and ‘relCertificate’.

Now I want to find all hotel-records which has properties (relProperty) 2 and 4 (id)

                             AND which has certificates (relCertificate) 15 and 16 and 18 (id).

I do it like this:

$models = hotel::model()->with(

    array(

        'relProperty'=>array(

            'select'=>'COUNT(??.id) as COUNTER',

            //'select'=>'COUNT(??.id) as COUNTER_A', // this does not work either

            'condition'=>'??.id IN (2,4)',

            'group'=>'hotel.id',

            'having' => 'COUNTER = 6', // this is 2*3 // at first I thought it should be 2 --- we have 2 Properties: 2,4

            //'having' => 'COUNTER_A = 2',

            'together'=>true    

        ),

        'relCertificate'=>array(

            'select'=>'COUNT(??.id) as COUNTER',

            //'select'=>'COUNT(??.id) as COUNTER_B', // this does not work either

            'condition'=>'??.id IN (15,16,18)',

            'group'=>'hotel.id',

            'having' => 'COUNTER = 6', // this is 2*3 // at first I thought it should be 3 --- we have 3 Certificates: 15,16,18

            //'having' => 'COUNTER_B = 3',

            'together'=>true

        )

        

    )

)->findAll();

It works but:

I do not understand why I have to check if

COUNTER = 6

and not

COUNTER = 2 (or COUNTER_A = 2) for first relation

and

COUNTER = 3 (or COUNTER_B = 3) for second relation.

I am not sure if my group and having statements are ok… could you please take a look at this? I use Yii 1.0.x

Thanks for help!

I think the reason for this behavior might be that you actually are counting records in the association table. I’m not able to suggest a Yii 1.0 solution.

/Tommy

I did something similar earlier, but I wonder why you want to count the rows? Do you want those records that have only and exactly these conditions, or can the records have some other ids additionally (so you would only look to exclude those that don’t have the ids).