Yii2 rest api join query with ActiveDataProvider


Yii2 rest api join query with ActiveDataProvider

我在ActiveController中有一个自定义操作,需要通过连接两个表来获取一些数据。我写了以下查询。

$query = Item::find()->joinWith(['subcategory'])->select(['item.*', 'sub_category.name'])->where(['item.active' => 1])->addOrderBy(['item.id' => SORT_DESC]);
    $pageSize = (isset($_GET["limit"]) ? $_GET["limit"] : 1) * 10;
    $page = isset($_GET["page"]) ? $_GET["page"] : 1;
    $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => $pageSize, "page" => $page]]);
    $formatter = new ResponseFormatter();
    return $formatter->formatResponse("", $dataProvider->getTotalCount(), $dataProvider->getModels());

但它抛出了一个异常

"message": "Setting unknown property: common''models''Item::name",

这是包含所有字段和关系的项目模型。

    <?php
namespace common'models;
use Yii;
use yii'behaviors'TimestampBehavior;
use yii'db'BaseActiveRecord;
use yii'db'Expression;
/**
 * This is the model class for table "item".
 *
 * @property integer $id
 * @property integer $subcategory_id
 * @property string $title
 * @property resource $description
 * @property integer $created_by
 * @property integer $updated_by
 * @property string $created_at
 * @property string $updated_at
 * @property string $image
 * @property integer $active
 *
 * @property SubCategory $subcategory
 */
class Item extends 'yii'db'ActiveRecord
{
    public $imageFile;
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 'item';
    }
    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['created_by', 'updated_by'], 'required'],
            [['subcategory_id', 'created_by', 'updated_by', 'active'], 'integer'],
            [['description'], 'string'],
            [['created_at', 'updated_at'], 'safe'],
            [['title', 'image'], 'string', 'max' => 999],
            [['title'], 'unique'],
            [['imageFile'], 'file', 'skipOnEmpty' => true, 'extensions' => 'png, jpg'],
        ];
    }
    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'id' => 'ID',
            'subcategory_id' => 'Subcategory ID',
            'title' => 'Title',
            'description' => 'Description',
            'created_by' => 'Created By',
            'updated_by' => 'Updated By',
            'created_at' => 'Created At',
            'updated_at' => 'Updated At',
            'image' => 'Image',
            'active' => 'Active',
            'imageFile' => 'Image',
        ];
    }

    /**
     * @return 'yii'db'ActiveQuery
     */
    public function getSubcategory()
    {
        return $this->hasOne(SubCategory::className(), ['id' => 'subcategory_id']);
    }
    /**
     * @return 'yii'db'ActiveQuery
     */
    public function getCreatedBy()
    {
        return $this->hasOne(User::className(), ['id' => 'created_by']);
    }
    /**
     * @return 'yii'db'ActiveQuery
     */
    public function getUpdatedBy()
    {
        return $this->hasOne(User::className(), ['id' => 'updated_by']);
    }
    public function behaviors()
    {
        return [
            'timestamp' => [
                'class' => TimestampBehavior::className(),
                'attributes' => [
                    BaseActiveRecord::EVENT_BEFORE_INSERT => ['created_at', 'updated_at'],
                    BaseActiveRecord::EVENT_BEFORE_UPDATE => 'updated_at',
                ],
                'value' => new Expression('NOW()'),
            ],
        ];
    }
}

joinWith 使用请求的连接进行查询,但结果数据在源模型(在本例中为 Item)中映射。

由于您有select(['item.*', 'sub_category.name']),框架将尝试填充 Item 模型的"name"字段,该字段不存在,这会生成错误。

根据文档 (http://www.yiiframework.com/doc-2.0/guide-rest-resources.html#overriding-extra-fields),默认情况下,您应该从 db 填充 db 关系子类别,但我在您的模型中看不到子类别关系。

因此,您只需在模型中创建子类别关系,例如:

public function getSubcategory() { return $this->hasOne(Subcategory::className(), ['id' => 'subcategory_id']); }

所以你应该解决你的问题。

从更多模型中拥有自定义字段的其他解决方案可能是:

1)使用所需的字段创建一个sql视图(并从中创建模型),并将其传递给ActiveDataProvide。

2) 覆盖模型extraFields方法 (http://www.yiiframework.com/doc-2.0/yii-base-arrayabletrait.html#extraFields%28%29-detail)

再次,我建议您阅读这篇好文章:http://www.yiiframework.com/wiki/834/relational-query-eager-loading-in-yii-2-0/