Hey there! Let's break down how to translate your native SQL with an inner join subquery into Yii2's Query Builder. Your original query is fetching all full rows from the class table where each row corresponds to the highest score for a specific name. Here's the step-by-step conversion:

Step 1: Build the Subquery #

First, we'll recreate the subquery that calculates the maximum score per name:

use yii\db\Query;

// Subquery: SELECT name, MAX(score) AS Maxscore FROM class GROUP BY name
$subQuery = (new Query())
    ->select(['name', 'MAX(score) AS Maxscore'])
    ->from('class')
    ->groupBy('name');

Step 2: Build the Main Query with Inner Join #

Next, we'll set up the main query that joins the class table with our subquery using the matching conditions:

// Main query: SELECT * FROM class INNER JOIN [subquery] topscore ON ...
$query = (new Query())
    ->select('*')
    ->from('class')
    ->innerJoin(
        ['topscore' => $subQuery], 
        'class.name = topscore.name AND class.score = topscore.Maxscore'
    );

Step 3: Execute the Query (Optional) #

If you want to run the query and get the results, just call all() (or one() if you expect a single row):

// Get all matching records as an array of arrays
$results = $query->all();

Quick Explanation #

• The $subQuery variable directly mirrors your grouped subquery, computing the highest score for each unique name.
• In innerJoin(), we pass an array where the key is the alias (topscore) and the value is our subquery. The second parameter is the exact ON condition from your original SQL.
• This approach maintains the same logic as your native SQL while leveraging Yii2's Query Builder, making the code more maintainable and compatible with different database drivers.

内容的提问来源于stack exchange，提问作者Serhii Barkar