Yii2 : How to write distinct SQL query?

php mysql yii2
JKLM picture JKLM · Jul 11, 2015 · Viewed 53.3k times · Source

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

Muhammad Shahzad picture Muhammad Shahzad · Dec 4, 2015

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,
    ]);
    // ...
}

Related questions

Yii2 How to perform where AND or OR condition grouping?

I am new to Yii-2 framework. How can i achieve following query in Yii-2 framework using activeQuery and models. SELECT * FROM users AS u WHERE u.user_id IN(1,5,8) AND (u.status = 1 OR u.verified = 1) OR (u.social_account = 1 AND …

Read More
How to count and group by in yii2

I would like to generate following query using yii2: SELECT COUNT(*) AS cnt FROM lead WHERE approved = 1 GROUP BY promoter_location_id, lead_type_id I have tried: $leadsCount = Lead::find() ->where('approved = 1') ->groupBy(['promoter_…

Read More
Performing raw SQL queries in Yii2?

I have written the below queries as I migrate my PHP website to the Yii2 framework. I want to add them to my controller so as to display the top 10 bets won. I have tried going through many Yii2 database …

Read More