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

Yii 2.0 General Discussions
1

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,
        ]);
    }

Thanks.
Luka

2

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

School::find()
    ->joinWith("clicks")
    ->groupBy("id") // School primary key
    ->having("count(click.id) = :count", ["count" => 0]); // Click primary key
1 Like
3

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

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

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

4

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(click.id) = :count", ["count" => 0])->andHaving('count(code.first_login) IS NULL');
5

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

1 Like
6

Success. Thanks again :angel: