CODEWITHSUNDEEP
cakephp
user3144903
user3144903

Reputation: 1

CAKEPHP - Find count of related model (with conditions)

I have a model called "User" which has a hasMany relationship to "AdvertisementClicks". I want to find all users that have at least 5 clicks (for example) that were made in the past 5 days. I currently have this:

$params = array(
            'conditions' => array(
                'User.referrer_id' => NULL,
                'User.id !=' => $this->Session->read('MySite.User.id')
            ),
            'contain' => array(
                'AdvertisementClick' => array(
                    'conditions' => array(
                        'CAST(AdvertisementClick.created AS DATE) >=' => date('Y-m-d', strtotime("-4 days"))
                    )
                )
            )
        );


        $this->User->Behaviors->attach('Containable');
        $availableReferrals = $this->User->find('all', $params);

But this will find all the users regardless if they made any clicks or not.

And if possible, limit the find to only return 3 users once it found those 3 users have at least 5 clicks.

Thanks!

Upvotes: 0

Views: 337

Answers (2)

Anubhav
Anubhav

Reputation: 1625

Use group by in query, it will give desired result:

$params = array(
                'conditions' => array(
                    'User.referrer_id' => NULL,
                    'User.id !=' => $this->Session->read('MySite.User.id'),
                    'COUNT(AdvertisementClick.id) AS clickcount',
                    'CAST(AdvertisementClick.created AS DATE) >' => date('Y-m-d', strtotime("-4 days"))

                ),
                'contain' => array(
                    'AdvertisementClick' => array(
                        'conditions' => array(
                            'CAST(AdvertisementClick.created AS DATE) >' => date('Y-m-d', strtotime("-4 days"))
                        )
                    )
                )
              'group'=>'AdvertisementClick.id HAVING clickcount >= 5'
            );


            $this->User->Behaviors->attach('Containable');
            $availableReferrals = $this->User->find('all', $params);

Upvotes: 0

arilia
arilia

Reputation: 9398

I assume that AdvertisementClick belongsTo User

$params = array(
    'conditions' => array(
        'User.referrer_id' => NULL,
        'User.id !=' => $this->Session->read('MySite.User.id'),
        'COUNT(AdvertisementClick.id) AS clickcount',
        'CAST(AdvertisementClick.created AS DATE) >=' => date('Y-m-d', strtotime("-4 days"))
    ),
    'group' => 'AdvertisementClick.id HAVING clickcount >= 5'
);



$availableReferrals = $this->User->AdvertisementClick->find(
    'all',
    $params
);

Upvotes: 1

Related Questions