How to get DISTINCT rows on a relation in Yii2 Active record

Question

I have a AieDetail model as below:

  class AieDetail extends \yii\db\ActiveRecord
  {
      public function getDepts()
      {
          return $this->hasOne(Department::className(), ['DEPT_CODE' => 'DEPT_CODE']);
      }
  }

I have this query that I want to use to select distinct COL_ABBREV column on Department table

  $aie_detail = AieDetail::find()->alias('AD')
                        ->select(['DEPT.COL_ABBREV'])
                        ->joinWith(['depts DEPT'])
                        ->where(['not',['DEPT.COL_ABBREV' => ['CA']]])
                        ->distinct()
                        ->all();
    return $aie_detail;

The value of $aie_detail is a query instead of an array of data. What is the correct approach to get the rows?

Answer 1

  $aie_detail = AieDetail::find()
                        ->select([Department::tableName() . '.COL_ABBREV'])
                        ->joinWith('depts')
                        ->where([
                          '!=',
                          Department::tableName() . '.COL_ABBREV',
                         'CA'
                        ])
                        ->distinct()
                        ->asArray()
                        ->all();

if you got any undefined index error include foreign keys used in the relation to the select statement or use leftJoin() method instead of joinWith().

if you want to select more data and distinct based on single column, then add groupBy() with arguments having distinct columns