Yii 2：如何以JSON_VALUE为关联条件构建Active Record关联？

Got it, let's walk through how to set up this association properly. I've had to do this exact thing for a project where we stored external IDs in a JSON field, so I can share what worked for me.

First, let's confirm the native SQL scenario we're matching. I assume your raw query looks something like this (adjust table/field names as needed):

SELECT a.*, b.*
FROM table_a a
INNER JOIN table_b b 
    ON JSON_VALUE(a.data, '$.external_id') = b.id

Step 1: Define the Association in Your Active Record Model #

In your TableA model (the one corresponding to table A with the JSON data field), add a method for the association. The key here is using Yii2's Expression class to pass the raw JSON_VALUE call directly to the query—this prevents Yii from incorrectly escaping it as a column name.

Here's the code:

use yii\db\Expression;
use app\models\TableB; // Adjust this to your actual TableB model namespace

class TableA extends \yii\db\ActiveRecord
{
    // ... your existing model code ...

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getTableB()
    {
        // Use hasOne if each TableA has one TableB; use hasMany if multiple
        return $this->hasOne(TableB::class, [
            'id' => new Expression("JSON_VALUE(data, '$.external_id')")
        ]);
    }
}

Step 2: Adjust for Database-Specific JSON Functions #

If you're using MySQL 5.7 or older (which doesn't support JSON_VALUE), you can use the ->> operator instead to extract the JSON value as a string:

return $this->hasOne(TableB::class, [
    'id' => new Expression("data->>'$.external_id'")
]);

Step 3: Use the Association Like Any Other Yii2 Relation #

Once the association is defined, you can use it in queries just like standard Yii2 relations:

// Eager load the TableB relation
$tableARecords = TableA::find()->with('tableB')->all();

// Access the related TableB model for a single record
$tableA = TableA::findOne($id);
if ($tableA->tableB) {
    echo $tableA->tableB->some_column;
}

Pro Tips for Performance & Reliability #

• Add an Index: To avoid full table scans on large datasets, create an index for the JSON path you're querying. For MySQL, that looks like:

  CREATE INDEX idx_table_a_data_external_id ON table_a((data->>'$.external_id'));
  

  For SQL Server, use a computed column index:

  ALTER TABLE table_a ADD external_id AS JSON_VALUE(data, '$.external_id');
  CREATE INDEX idx_table_a_external_id ON table_a(external_id);
  

• Add Extra Conditions: You can extend the association with additional filters using onCondition:

  return $this->hasOne(TableB::class, [
      'id' => new Expression("JSON_VALUE(data, '$.external_id')")
  ])->onCondition(['table_b.status' => 'active']);
  

内容的提问来源于stack exchange，提问作者A. Kostenkov