CODEWITHSUNDEEP
phpmysqlyii2
JKLM
JKLM

Reputation: 1520

Yii2 : How to write distinct SQL query?

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?

Upvotes: 31

Views: 73688

Answers (5)

Maxim Paladi
Maxim Paladi

Reputation: 121

For those who need select distinct on and other columns with aliases, the following solution will work:

$modelClass::find()
    ->select(new Expression(
        "distinct on ('col1') col1, col2, test as col3, 'hardCodeValue' as col4"
    ))

Note: for PGSQL it is important what quotes you put in the query.

Upvotes: 2

JKLM
JKLM

Reputation: 1520

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>';
    }
}

Upvotes: 19

Muhammad Shahzad
Muhammad Shahzad

Reputation: 9652

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

Upvotes: 34

ScaisEdge
ScaisEdge

Reputation: 133410

I hope this sample is useful for you 

 $names = Yii::$app->db->createCommand('SELECT  count(DISTINCT(company_name)) as name FROM clients')
    ->queryAll();

for access the the data 

foreach ($names as $name){
    echo $name['name'];
}

Upvotes: 2

user2382679
user2382679

Reputation: 179

All worked fine

return Clients::find()->count('DISTINCT(company_name)');

Upvotes: 2

Related Questions