使用 CDBCriteria 进行连接查询

2022-01-04 00:00:00 php inner-join yii criteria

我正在尝试在 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.

我的做法如下

$criteria = new CDbCriteria;

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

$criteria->select = '*';

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

当我运行它时，我只能看到显示的邮件 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在table1和table2中是通用的.

here command_id is common in table1 and table2.

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

This is how I am using the criteria code.

推荐答案

正如我所说，Yii 的 Active Record 实现将只使用在表本身或您通过 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.

相关文章