使用CDBCriteria进行联接查询 [英] Doing Join query using CDBCriteria

问题描述

我正在尝试使用Yii框架中的CDBCriteria进行Join查询。问题是联接查询可以成功运行，但不会显示其他表中的列。

I am trying to do a Join query using CDBCriteria in Yii framework. The issue is the join query works successfully but it does not display the columns from other tables.

我正在按照以下方式进行操作

I am doing in the following way

$criteria = new CDbCriteria;

$criteria->order = 't.id desc';

$criteria->select = '*';

$criteria->join = ' INNER JOIN table2 INNER JOIN table3 INNER JOIN table4';

运行此命令时，我只能看到显示的mail table1列。其他列未显示。

When i run this, I can see only the mail table1 columns displayed. Other columns are not shown.

在我的模型类中，我具有以下关系：

In my model class, I have the relation has

public function relations()
{
  // NOTE: you may need to adjust the relation name and the related
  // class name for the relations automatically generated below.
    return array(
      'user' => array(self::BELONGS_TO, 'DmPhoneUser', 'user_id'),
      'command' => array(self::BELONGS_TO, 'DmOtaCommand', 'command_id'),
      'partner' => array(self::BELONGS_TO, 'DmPartner', 'partner_id'),
    );
}

** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * **

********************************************************

public function actionGetHistory($start, $per_page)
{
    $partner_id = '10';
    $criteria = new CDbCriteria;
    $criteria->order = 't.history_id desc';
    $criteria->select = 't.*, table2.*';
    $criteria->join = ' INNER JOIN table2 ON t.command_id = table2.command_id';
    $result = Table_1_class::model()->with('command')->findAll();
    $history_data = CJSON::encode($result);
    echo $history_data;
}

其中，command_id在表1和表2中是常见的。

here command_id is common in table1 and table2.

这就是我使用标准代码的方式。

This is how I am using the criteria code.

推荐答案

正如我所说，Yii的活动记录实现将仅使用表本身或通过 with 链接到的表中定义的列，而不使用结果集中返回的任意列。

As I said, Yii's Active Record implementation will only use columns which are defined in the table itself or the tables you are linking to through with, not arbitrary columns you return in the resultset.

解决方案1：定义与 table2 的关系，并将该关系添加到 with ，并摆脱 join 。然后，您可能需要将每个返回的对象转换为数组- CJSON :: encode 将无法很好地处理具有关联关系的模型。

Solution 1: Define a relation to table2, add that relation to with, and get rid of join. Then you'll probably need to convert each returned object to an array - CJSON::encode will not handle a model with relations well.

解决方案2：使用 Yii :: app（）-> db-> createCommand（ SQL query）-> queryAll（）; 而不是Active Record。这将产生一个数组数组，其中 CJSON :: encode 不会有问题。

Solution 2: Use Yii::app()->db->createCommand("SQL query")->queryAll(); instead of Active Record. This will produce an array of arrays, which CJSON::encode will have no problem with.

这篇关于使用CDBCriteria进行联接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！