Yii2 : How to write distinct SQL query?

Question

I want to implement following SQL queries in Yii 2 but with no success.

This should give total number of unique company names:

SELECT count(DISTINCT(company_name)) FROM clients 

And this should display company_name with client code and id(PK):

SELECT (DISTINCT(company_name,client_code)) FROM clients 

How to achieve this?

Answer 1

Try this:

$total = YourModel::find()->select('company_name')->distinct()->count(); 

In Search Model:

public function search($params) {     $query = YourModel::find()->select('company_name')->distinct();     // or     $query = YourModel::find()->select(['company_name', 'client_code'])->distinct();      $query->orderBy('id desc');      $dataProvider = new ActiveDataProvider([         'query' => $query,     ]);     // ... } 

Answer 2

Answering my own question I got following working solutions:

Got the count for unique company_name:

$my = (new yii\db\Query())     ->select(['company_name',])     ->from('create_client')     ->distinct()     ->count(); echo $my; 

List of distinct company_name and client_code:

$query = new yii\db\Query(); $data = $query->select(['company_name','client_code'])     ->from('create_client')     ->distinct()     ->all(); if ($data) {     foreach ($data as $row) {         echo 'company_name: ' . $row['company_name'] . ' client_code: ' . $row['client_code'] . '<br>';     } }