Yii2 : How to write distinct SQL query?

Yii2 : How to write distinct SQL query?

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Working with Distinct Queries in Yii2

When migrating from raw SQL or a different ORM framework to Yii2, one of the first hurdles you might encounter is handling aggregation and distinct logic. Specifically, translating a SELECT COUNT(DISTINCT column) or a SELECT DISTINCT column1, column2 into the ActiveRecord pattern requires understanding how Yii constructs queries behind the scenes.

Counting Unique Values

Your first requirement is to get the total number of unique company names from the clients table. In standard SQL, this is straightforward: SELECT count(DISTINCT(company_name)) FROM clients. However, in Yii2, you should rarely use $query->count() if your goal is specifically a distinct count. The built-in count() method returns the total number of rows matching the conditions, ignoring the DISTINCT modifier unless explicitly configured.

To replicate SELECT COUNT(DISTINCT(company_name)), you need to construct a raw SQL query or use Yii's query builder with specific aggregation settings. Using the Query Builder, you can achieve this by creating a sub-query that selects the distinct values first, then counting them. Alternatively, you can rely on the database engine to handle the distinct operation within an aggregate function if your version of MySQL, PostgreSQL, or SQLite supports it directly in the query builder (though this varies).

A robust approach using Yii2's Query Builder is:

use yii\db\Query;

$subQuery = (new Query())
    ->select(['DISTINCT company_name'])
    ->from('clients');

$countQuery = (new Query())
    ->select(['COUNT(*) as count'])
    ->from(['sub' => $subQuery])
    ->scalar(); // Returns a single value, or you can use->one()

// Or using raw SQL for exact syntax match if needed:
$countResult = Yii::$app->db->createCommand(
    'SELECT COUNT(DISTINCT company_name) FROM clients'
)->queryScalar();

The raw SQL approach via createCommand is often the cleanest when you need strict adherence to a specific SQL dialect's aggregation syntax, especially if your database engine handles COUNT(DISTINCT ...) differently than the ORM's default behavior.

Listing Distinct Columns

Your second requirement involves displaying company_name, client_code, and id while ensuring uniqueness across these columns. Your source material shows an attempt to use (DISTINCT(company_name, client_code)). This syntax is actually invalid in standard SQL. The DISTINCT keyword applies to the entire result set row, not individual columns within parentheses like that. The correct syntax is SELECT DISTINCT company_name, client_code, id FROM clients.

In Yii2, you can implement this cleanly using the distinct() method on the Query object. This tells the ActiveRecord layer to append DISTINCT to the SELECT clause automatically.

use yii\db\Query;

$query = (new Query())
    ->select(['id', 'company_name', 'client_code'])
    ->from('clients')
    ->distinct(); // Adds DISTINCT to the SELECT clause

$rows = $query->all();

By calling distinct(), Yii generates a query equivalent to SELECT DISTINCT id, company_name, client_code FROM clients. This ensures that if two rows have identical values for all three selected columns, only one appears in the result set. If you only wanted unique combinations of company_name and client_code but still needed the id, this method works perfectly.

Performance Considerations

It is important to understand the performance implications of using DISTINCT. When you enable distinct logic, the database engine must sort or hash the intermediate result set to remove duplicates before returning data. For large tables, this can be significantly more expensive than a standard SELECT or even a simple GROUP BY.

If your application needs to count unique items frequently but you don't need the list itself, consider caching the result in Redis or Memcached. If you are listing distinct records and the dataset is massive, ensure that there are appropriate indexes on the columns involved in the distinct selection. Without an index, the database may have to perform a full table scan and sort operation, which can degrade response times noticeably as data grows.

When to Use Raw SQL

While Yii2's Query Builder handles most scenarios elegantly, there are edge cases where raw SQL via createCommand is preferable. If you need to pass complex aggregation functions or specific database extensions that the Query Builder doesn't abstract well, falling back to a direct SQL string is acceptable. However, always remember to use parameter binding (via params) to prevent SQL injection, even when writing custom distinct queries.

For example:

$count = Yii::$app->db->createCommand(
    'SELECT COUNT(DISTINCT company_name) FROM clients WHERE id = :id',
    [':id' => $clientId]
)->queryScalar();

This pattern ensures you maintain the safety of parameterized queries while retaining the flexibility of native SQL syntax when necessary.

Our network