CODEWITHSUNDEEP
phpyii2yii2-active-records
rufusy
rufusy

Reputation: 99

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

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?

Upvotes: 0

Views: 1247

Answers (2)

rufaidulk
rufaidulk

Reputation: 374

  $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

Upvotes: 0

Dinson David Kurian
Dinson David Kurian

Reputation: 1

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

You have to pass in actual table name when using '.' operator in selecting a column in query.

Upvotes: -1

Related Questions