Multiple where ( like ) AR queries

Yii 2.0 General Discussions
1

In my search model I am running code like this:




$query = Club::find()->joinWith('members')->where(['like', 'group_id', '09%', false]);

I need to get count of all club members belonging to certain group specified by group id. For example one group id may be 091 and other 092. So I need to count all members belonging to group with id 09x. But also there are several groups, with ids starting with, for example, 01x, 02x…

So I need to count all members belonging to certain group, and display that number in separate link like this:

group 01 ( 111 members )

group 09 ( 34 members )

…

What is the most optimal way of doing this ?

2

Group by members.group_id and select group_id and count(*)




$query = Club::find()->joinWith('members')

->where(['like', 'group_id', '09%', false])

->groupBy('members.group_id')

->select('group_id, COUNT(*)')->findAll();
3

But do I have to run this query for every group that I have?




->where(['like', 'group_id', '09%', false])

This will find people from group id 09xxxx, but I need also people from 01xxx, 02xxx…

Do I need to run same query for every group ?

4

Remove where, so you’ll every group.

5

But I need to display results like this:

group 01 ( 111 members )

group 09 ( 34 members )

If I remove where, how I am going to know this information: how many members each group has ?

6

If there is a login, for example first 2 digit of group_id, you could set a select to get all group_id with a single statement, as:




$query = Club::find()->joinWith('members')

->groupBy('LEFT(members.group_id, 2)')

->select('LEFT(members.group_id, 2), COUNT(*)')

->findAll();

Otherwise you had to use a loop to set correct group id rule to search.

7

Thank you for your help, but this doesn’t help me.

Let me explain what I need with more simplified example:

I want to display list of groups with number of members in each group like this:

group 01 ( 111 members )

group 02 ( 75 members )

group 09 ( 34 members )

Every group have its subgroup like this:

group 01 have a subgroup of 011, 012, 013…

And I am counting members belonging to each subgroup and I want to display it next to main group name like described above.

I have this query :




Club::find()->where(['like', 'group_id', '01%', false])->count('group_id')

This will display me :

group 01 ( 111 members )

My question is, what is the optimal way of getting member count for all groups ? Do I have to run same query for each group, or there is some better way ?

8

This code group first by group_id to take all single group_id and than taking only first 2 digit of group_id.

Making group with rollup give you partial count.




$query = Club::find()->joinWith('members')

->groupBy('members.group_id, LEFT(members.group_id, 2) WITH ROLLUP')

->select('members.group_id, LEFT(members.group_id, 2), COUNT(*)')

->findAll();
9

Please base your code on:




Club::find()->where(['like', 'group_id', '01%', false])->count('group_id')

Don’t join it with members

10 



$data = Club::find()

->groupBy('group_id, LEFT(group_id, 2) WITH ROLLUP')

->select('group_id, LEFT(group_id, 2), COUNT(*)')

->findAll();
11

Error:

Calling unknown method: yii\db\ActiveQuery::findAll()

12

If I change it to:




Club::find()->groupBy('group_id, LEFT(group_id, 2) WITH ROLLUP')->select('group_id, LEFT(group_id, 2)')->count()

I get mysql error near:

near 'FROM club GROUP BY group_id, LEFT(group_id, 2) WITH ROLLUP)

I think this is the problem:


LEFT(group_id, `2)
13

Sorry, i’ve mixed Yii1 and 2.




$data = Club::find()

->groupBy('group_id, LEFT(group_id, 2) WITH ROLLUP')

->select('group_id, LEFT(group_id, 2), COUNT(*)')

->all();
14

mysql error again. Your code produce this, and it is not valid:


LEFT(group_id, `2)
15

Also it doesn’t like COUNT(*) inside select()… It seems that you are not using query methods properly

16

There is a problem how Yii treat column from string.

i’ve a doubt with ROLLUP in second element of group array, but try this:




$data = Club::find()

->groupBy( ['group_id', 'LEFT(group_id, 2) WITH ROLLUP'] )

->select('group_id, LEFT(group_id, 2), COUNT(*)')

->all();
17

You needn’t a single row count, but you need count of single group_id element (so many rows).

This is why I select COUNT(*).

18

Error again.

Thanks for your time, forget about this…

19

What is error now?

20

near ’ COUNT(*) FROM club GROUP BY group_id, LEFT(group_id, 2) WITH ROLLUP’