Yii2：将原生SQL查询转换为ActiveRecord实现

Alright, let's take your raw SQL and translate it into clean Yii2 Query Builder/ActiveRecord code. I'll break this down step by step so you can follow along easily.

First, let's recap what your original SQL does: it creates a subquery to grab the max impression/view/click per ad_id + visitor_ip pair, then aggregates those values by ad_id and joins with the inventory table to pull in campaign details. Here's how to replicate this using Yii2's tools:

1. Build the Subquery #

First, we'll create the subquery (the t alias in your original SQL) using the AdAnalytics model's find() method:

use app\models\AdAnalytics;

// Subquery: Get max metrics per ad_id + visitor_ip combination
$subQuery = AdAnalytics::find()
    ->select([
        'id',
        'ad_id',
        'MAX(impression) AS impression',
        // Note: `view` is a SQL reserved word, so we wrap it in backticks to avoid errors
        'MAX(`view`) AS view',
        'MAX(clicks) AS clicks',
        'visitor_ip',
        'publisher_id'
    ])
    ->groupBy(['ad_id', 'visitor_ip']);

2. Build the Main Query with Join and Aggregation #

Next, we'll use Yii2's Query class to wrap the subquery, join with the Inventory model, and calculate the total aggregates:

use yii\db\Query;
use app\models\Inventory;

$mainQuery = (new Query())
    ->select([
        't.id',
        't.ad_id',
        'SUM(t.impression) AS total_impression',
        'SUM(t.view) AS total_views',
        'SUM(t.clicks) AS total_clicks',
        't.publisher_id',
        'i.budget',
        'i.name_of_campaign'
    ])
    // Treat the subquery as a temporary table `t`
    ->from(['t' => $subQuery])
    // Join with the Inventory table (using the model's table name for clarity)
    ->innerJoin(['i' => Inventory::tableName()], 'i.id = t.ad_id')
    ->groupBy(['t.ad_id']);

3. Retrieve Results #

You can fetch the results in a couple of ways depending on your needs:

Option 1: Get results as an array of associative arrays #

This is the simplest approach if you just need the raw data:

$analyticsData = $mainQuery->asArray()->all();

Option 2: Populate custom model objects #

If you want to work with model instances (for validation, custom methods, etc.), create a simple summary model like AnalyticsSummary:

// app/models/AnalyticsSummary.php
namespace app\models;

use yii\base\Model;

class AnalyticsSummary extends Model
{
    public $id;
    public $ad_id;
    public $total_impression;
    public $total_views;
    public $total_clicks;
    public $publisher_id;
    public $budget;
    public $name_of_campaign;

    // Add validation rules here if needed
    public function rules()
    {
        return [];
    }
}

Then populate the models from the query results:

use yii\helpers\ArrayHelper;
use app\models\AnalyticsSummary;

$rawData = $mainQuery->asArray()->all();
$summaryModels = array_map(function ($row) {
    return new AnalyticsSummary($row);
}, $rawData);

Key Notes #

• SQL Reserved Words: view is a reserved keyword in SQL, so wrapping it in backticks prevents syntax errors. Yii's query builder can auto-escape columns too, but explicit backticks work perfectly here.
• Model Assumptions: This code assumes you've already set up the AdAnalytics and Inventory models (either via Gii or manually) with correct table mappings and attributes.
• Performance: For large datasets, add indexes on ad_id and visitor_ip in the ad_analytics table to speed up the grouping operations.

内容的提问来源于stack exchange，提问作者arun webber