Get quantity of user by Position (count and group by query)

Yii 2.0 General Discussions
1

Hi
I need help to pass a query from SQL to yii2 ORM.

SELECT b.name, COUNT(a.position_id) as cant_users, b.institution_id as institucion
from tb_user_positions as a
JOIN tb_positions b ON b.id = a.position_id
where b.institution_id = 850
group By b.name;

my goal is get all users that belongs to Position and show in positions index view
Position name and its respective counter of users that have this position (marked column)
here an image

image
image2420Ă—729 99.9 KB

part of my code:

$positionsv = Positions::find()
            ->where(['institution_id' => $institution_id])
            ->notDeleted()->select('name')
            ->indexBy('id')->column();
2

So what is the problem?

3

hi @evstevemd
I’ve changed the query to this

$positionsv = Positions::find()
            ->with(['activeUserPositions'])
            ->where(['institution_id' => $institution_id])
            ->notDeleted()->all();

        dd($positionsv);

Now I can see the relationship with userPositions

But I don’t know how count the “position_id” and pass into query

4

Would you please explain what is the problem you are trying to solve first?
If all you want to do is display some results of the query then I suggest you create method in your AR that does the counting and then call it on view.
Here is an example

class SomeModel extends ActiveRecord
{
    //normal AR stuffs here

    public function getPositionCount()
    {
        $sql = 'SELECT b.name, COUNT(a.position_id) as cant_users, b.institution_id as institucion FROM tb_user_positions as a 
                JOIN tb_positions b ON b.id = a.position_id where b.institution_id = 850 group By b.name;'
        $count = self::getDb()->createCommand($sql)->queryScalar();

        return intval($count);
    }
}

and in the view

<?= GridView::widget([
    //normal Gridview stuffs
    'columns' => [
        //other columns
        [
            'label' => Yii::t('app', 'Position Count'),
            'format' => 'integer',
            'value' => function($model){
                return $model->getPositionCount();
            }
        ]
    ]
]) ?>

Let me know if that is what you want!

1 Like
5

I resolve it with your example.
I did a similar query in the model, to get only active users, and then, I called from the column with $model->activeUserPositions

[
                    'label' => __('GxP', 'commons.users_with_position'),
                    'attribute' => 'activeUserPositions',
                    'headerOptions' => ['class' => 'text-center'], 
                    'contentOptions' => ['class' => 'text-center'], 
                    'value' => function ($model) {
                        return count($model->activeUserPositions);
                    },
                ],

Thanks