Yii Framework Forum: MANY_MANY and COUNT() - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • This topic is locked

MANY_MANY and COUNT() It works but I am not sure if I do it right Rate Topic: -----

#1 User is offline   windman 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 47
  • Joined: 09-March 09

Posted 19 August 2011 - 07:58 AM

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!
0

#2 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 19 August 2011 - 08:46 AM

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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#3 User is offline   Narretz 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 48
  • Joined: 20-August 11

Posted 24 August 2011 - 09:11 AM

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).
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • This topic is locked

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users