joining tables and get count from one table

I am new to yii and I faces a small issue.In one of my project,I have two models employees and departments.In admin side,while listing the departments(grid view),I have to show the number of employees in each department.I have done the page for listing the departments but cannot able to add the number of employees in each department.

my employee table consists of

1-empid

2-deptid,

3-empname

my department table consists of

1-deptid,

2-deptname

In mysql (phpmyadmin interface) I can write a query to combine the two tables and get the correct result

"SELECT D. * , count( E.deptid) AS emp_count

FROM department AS D

LEFT JOIN employee AS E ON D.id = E.deptid

GROUP BY D.id" and the result of this query will be like

id name emp_count

1 Accounts 0

2 Development 2

3 Quality Control 1

4 Operations 0

5 Human Resources 2

6 System 1

how I can implement this in grid view in yii…

Please help me…

Thank you in advance…

You can use the pure sql like your mysql statement above.

Use a CSqlDataProvider .

Yii 2: SqlDataProvider

Hi Jobio,

Thank you for your replay…

But I am too fresh in yii…I cant find a solution with your reply…

Thank you…