Yii2 : LeftJoin query with Where clause & orderBy clause

Question

I have write query to get distinct stateID whose status is active, from tbl_summer table which is primary key of table tbl_states.

I want the listing of distinct state names in alphabetical order. Actually i got this from following query but alphabetical order is not getting...

So what is the solution...?

Here is my query :

$query = Tbl_summer::find()
    ->select('tbl_summer.StateID, tbl_states.state_name')
    ->distinct('tbl_summer.StateID')
    ->from('tbl_summer')
    ->leftJoin('tbl_states', ['tbl_states.ID' => 'tbl_summer.StateID'])
    ->where(['tbl_summer.IsActive' => '1'])
    ->orderBy(['tbl_states.state_name' => SORT_ASC]);

You have to use group by instead of distinct. See this answer here http://stackoverflow.com/a/10905511/57091 — robsch, Apr 10 '15 at 06:25

score 1 · Answer 1 · answered Apr 10 '15 at 08:48

Does this work?

$query = Tbl_summer::find()
    ->select('tbl_summer.StateID, tbl_states.state_name')
    ->from('tbl_summer')
    ->leftJoin('tbl_states', ['tbl_states.ID' => 'tbl_summer.StateID'])
    ->where(['tbl_summer.IsActive' => '1'])
    ->groupBy('tbl_summer.StateID, tbl_states.state_name')
    ->orderBy(['tbl_states.state_name' => SORT_ASC]);

I think the second field in groupBy is not needed if there is only one name for one id.

@SantoshGaikwad What does that mean? Which problem do you get? — robsch, Apr 17 '15 at 06:46

Yii2 : LeftJoin query with Where clause & orderBy clause

1 Answers1