Use Count(*) In Yii2

Yii 2.0 General Discussions
1

hi all,

I want to get the count of deleted content per user in my DB, i have two fields to get this data in ‘content’ table

  1. is_deleted

2.deleted_by (user_id),

so what i want is as the following :

user_id | count(*)

1 | 3

2 | 5

I wrote this query in Yii2:




 $count = content::find()->select('content.deleted_by, count(*)')->innerJoin('users', 'users.id=content.user_id')

->where("content.is_deleted='1' and content.deleted_time>='$from' AND content.deleted_time<= '$to 23:59:59'")->groupBy('deleted_by')->all();

but i get this error:

Setting unknown property: backend\models\Content::count

can anyone help me

2

I believe you have to alias your count() to a model property like "select count() as counter" where your model has a public int $counter = 0;

At least that’s the case for Yii 1.1.x

3

Do you really need to use model in this case? It looks pretty much like raw query. Consider using it directly.

4

You can use asArray() to get the result as array instead of trying to populate records. This way it should work.

5

use asArry() works for me to solve a similar count(*) not displaying issue.Thanks

6

This is how i’m doing in my model (hoping it’s the right way)


    public static function ArtCount($ArtDes)

    {

        $connection = Yii::$app->getDb();

        $command = $connection->createCommand("SELECT count(*) as tot FROM art where des =:artdes",[':artdes'=> $ArtDes]);

        $result = $command->queryAll();

        return $result[0]['tot'];

    }
1 Like
7

great reply for custom query.
thank you