Multiple where ( like ) AR queries

Yii 2.0 General Discussions
21

You are right. Select must be an array




$data = Club::find()

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

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

->all();
22

How would you display result of this in php/html ( without any widget/extension ) ?

23

With a <table> ?

24

But solution is working?

25

I don’t know… I don’t know how too see output.

26 



$data = Club::find()

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

->select( ['group_id', 'LEFT(group_id, 2)' , 'COUNT(*) AS id'] )

->all();


// I'm using id column to display count(*)

foreach($data as $d)

{

    echo print_r($d->group_id, $p->id);

}
27

We are very close to what I need;

With this code:




$data = Club::find()->groupBy( ['group_id', 'LEFT(group_id, 2)'] )

                      ->select( ['group_id', 'LEFT(group_id, 2)' , 'COUNT(*) AS id'] )

                      ->all();


$counter = 1;


foreach($data as $d)

{

    echo $d->cpv_id . " " . $d->id;


    $counter += $d->id;


    echo "<br>";

}


echo $counter;

We are counting number of member of each group.

For example I will get this output:

011: 5

012: 3

…

021: 10

022: 6

023: 5

…

But I also need to count total number of members in these subgroups ( 011, 012…) for each group (01) (02).

So my final result should be:

01: 8

…

02: 21

Any ideas ?

28

Ops, with copy/paste i’ve lost WITH ROLLUP options in second field group, that complete your job!




$data = Club::find()->groupBy( ['group_id', 'LEFT(group_id, 2) WITH ROLLUP'] )

                      ->select( ['group_id', 'LEFT(group_id, 2)' , 'COUNT(*) AS id'] )

                      ->all();


$counter = 1;


foreach($data as $d)

{

    echo $d->cpv_id . " " . $d->id;


    $counter += $d->id;


    echo "<br>";

}


echo $counter;
29

I am not at my PC now, but I removed WITH ROLLUP on purpose, since I was getting some strange results, I didn’t knew what to do with. Or maybe I didn’t displayed them properly… Where/How would you display this total count

01: 8

…

02: 21

I mean, when you apply WITH ROLLUP, where that value will be stored for each group ?

30

This is not returning what I need. I just get output like this:

0110 2

0110 2

0120 3

0120 3

0130 1

0130 1

It just repeat the same line again ( count in subgroup ), instead of counting total number of members in a group like this:

01 : 6

01 is parent, 011, 012, 013… are children. With this query we got number of members in each subgroup ( 011, 012, 013 ), but not total count for whole group ( 01 ).

Any ideas ?

31

Maybe WITH ROLLUP is dropped from Yii, because WITH ROLLUP make total amount for field that you use to group.

Check log to view complete sql that is executed.

32

This is my realworld problem and code that is partially doing what I need:

I have 3 tables:

1 - cpv

2 - club_offer

3 - club_offer_cpv

in club_offer_cpv I have fields: club_offer_id and cpv_id. So this table is related with other two by one to many relationship.

In field cpv_id is stored what I called previously group_id, because cpv_id represent the group that club_offer is belonging to.

For example in club_offer_cpv table: offers and groups that they belong to are stored like this:

club_offer_id | cpv_id

1 | 011000

2 | 012000

3 | 021000

4 | 024000

Now, what I wanted is to count club_offers for every group. But when I say every group I mean to display number of offers for every parent group like this:

010000 - 76 offers

020000 - 55 offers

010000 is parent and it is stored in cpv table with its name like this:

id | name

010000 | First Group

020000 | Second Group

030000 | Third Group

My end disired result should be like this :

010000 - First Group - 54 offers

020000 - Second Group - 63 offers

With this code:




$total = ClubOfferCpv::find()->groupBy( ['LEFT(cpv_id, 2)'] )

                             ->select( ['LEFT(cpv_id, 2) AS cpv_id', 'club_offer_id', 'COUNT( DISTINCT cpv_id) AS count' ])

                             ->all();


foreach ($total as $data) 

{

    echo $data->cpv_id."0000" . " " . $data->count;


    echo "<br>";

}

I am getting this result:

010000 - 76 distinct groups are used ( I am not getting offers, instead I am getting cpv_id )

020000 - 55 distinct groups are used

…

But I also need to pull cpv names from cpv table to get it like this:

010000 - First Group - 76 distinct groups are used

020000 - Second Group - 55 distinct groups are used

So from my original idea to count number of offers for each group ( parent 010000 ) I ended up counting number of subgroups used ( cpv_id ). Its is not bad, it can go like this.

But still, I need to get this list with name of parent group and number next to it ( indicating distinct number of all subgroups or number of offers for all groups )

Any ideas ? I hope that you understand all of this, its complex.

Thanks