CODEWITHSUNDEEP
mysqlyii2
Santosh Gaikwad
Santosh Gaikwad

Reputation: 153

Yii2 : LeftJoin query with Where clause & orderBy clause

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]);

Upvotes: 1

Views: 965

Answers (1)

robsch
robsch

Reputation: 9728

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.

Upvotes: 1

Related Questions