Yii2技术问题：Query设数据库配置及关联模型生成ActiveDataProvider

Let's break down your two Yii questions with practical, hands-on solutions:

Question 1: Set Database Config for a New Query Without Executing It #

You don’t need to call all(), one(), or any execution methods to assign a database component to your Query/ActiveQuery object. Here are three straightforward approaches:

• Use the db() method directly on the ActiveQuery (for model-based queries):

  // Target the "db1" component for Model1's query
  $query = Model1::find()->db(Yii::$app->get('db1'));
  

• Pass the DB component during Query instantiation (for raw Query objects):

  use yii\db\Query;
  
  $query = new Query([
      'db' => Yii::$app->get('db1')
  ]);
  

• Call setDb() on an existing Query object:

  $query = Model2::find();
  // Switch to "db1" after creating the query
  $query->setDb(Yii::$app->get('db1'));
  

All these methods configure the database connection without triggering any database execution.

Question 2: Create ActiveDataProvider for Linked Models (Model1 & Model2 in db1) #

Since both models live in the db1 component, we need to ensure our query uses this connection, then build the ActiveDataProvider with the properly joined query. Here’s a revised, functional version of your search() method:

use yii\data\ActiveDataProvider;
use yii\db\Expression;
use yii\helpers\ArrayHelper;

public function search($params) {
    // Initialize query for Model1, explicitly using db1
    $query1 = Model1::find()->db(Yii::$app->get('db1'));

    // Join with Model2 - ensure the关联 query also uses db1 (if not set in Model1's relation)
    $query1->select([
        // Fix the Expression syntax (adjust concat logic based on your database)
        new Expression("'Data model1: ' || model1.your_column AS model_label"),
        'model1.id',
        'model1.your_column1', // Replace with actual column names from Model1
        'model2.your_column2', // Add needed columns from Model2
    ])
    ->joinWith(['model2' => function($q) {
        // Explicitly set db1 for the关联 query (optional if Model2's default db is db1)
        $q->db(Yii::$app->get('db1'));
    }])
    ->andWhere(new Expression('model1.id = model2.model1_id')); // Adjust your join condition

    // Load and apply search parameters if needed
    $this->load($params);

    // Build the ActiveDataProvider for GridView
    $dataProvider = new ActiveDataProvider([
        'query' => $query1,
        'pagination' => [
            'pageSize' => 20, // Adjust page size as needed
        ],
        'sort' => [
            'defaultOrder' => [
                'id' => SORT_DESC, // Set default sorting order
            ],
            // Allow sorting on custom selected columns
            'attributes' => [
                'id',
                'model_label', // Map your custom alias to enable sorting
            ],
        ],
    ]);

    return $dataProvider;
}

Key Notes: #

• Fix the Expression: Your original new Expression ("'Data model1: ' Model") has invalid syntax. Use database-appropriate string concatenation (e.g., || for PostgreSQL, CONCAT() for MySQL).
• Explicit DB Connection: Even if your models are configured to use db1 by default, explicitly setting ->db(Yii::$app->get('db1')) avoids accidental use of the default db component.
• Sorting for Custom Columns: If you use aliases in your select, add them to the sort attributes so GridView can sort by those columns.

内容的提问来源于stack exchange，提问作者Raciel Brito Dorta