Use ActiveDataProvider to filter by an user custom defined 'hasMany' property

I have defined a function getClicksCount() in my School model and a function actionActive() in my SchoolController and I want to use a ActiveDataProvider to show the results in a grid having the property clicksCount equal zero. Is that even possible?

public function getClicksCount()
        return $this->hasMany(Click::class, ['school_id' => 'id'])->count();

How to change the actionActive() to fit my requirements?

public function actionActive()
        $searchModel = new SchoolSearch();
        $dataProvider = $searchModel->search($this->request->queryParams);
        $dataProvider->sort->defaultOrder = ['modified' => SORT_DESC];
        $dataProvider->pagination = ['pageSize' => 200];
        $dataProvider->query->where('last_active_time >= NOW() - INTERVAL 30 SECOND');
        // ???

        return $this->render('index', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,


You can do it more or less with:

// in School class:
// let it be regular relation
public function getClicks()
        return $this->hasMany(Click::class, ['school_id' => 'id']);

// In SchoolSearch or wherever you need

    ->groupBy("id") // School primary key
    ->having("count( = :count", ["count" => 0]); // Click primary key
1 Like

Thanks a lot Bart I put it directly in my // ??? “section” like this:

$dataProvider->query->joinWith('clicks')->groupBy('id')->having("count( = :count", ["count" => 0]);

Works like a charm :wink:
ps: A new thing learned! :heart_decoration:

Bart I have another question; how to upgrade (add another having condition) to show all those schools who have all attributes ‘first_login’ (in clicks table) set to NULL ?

I tried hit no success. :cry:

$dataProvider->query->joinWith('clicks')->groupBy('id')->having("count( = :count", ["count" => 0])->andHaving('count(code.first_login) IS NULL');

Try to add (not tested):
->andHaving('COUNT(IF(click.first_login IS NOT NULL, 1, NULL)) = 0')

1 Like

Success. Thanks again :angel: