将原始sql传递给yii2活动数据提供程序 - Passing raw sql to yii2 active dataProvider

Passing raw sql to yii2 active dataProvider

本文关键字：数据活动程序 yii2 原始 sql | 更新日期: 2023-09-27

我通过yii2创建命令传递原始sql，但我总是得到属性的错误必须是实现QueryInterface的类的实例，例如yii'db'Query或其子类

代码

      $sql ="
        SELECT
    tblpritems.PRlineID
    , tblpritems.Tracking_Code
    , tblpritems.Description
    , tblpritems.Quantity
    , tblpritems.Unit_Price
    , tblpritems.Extended_price
    , tblpritems.PRID
    , tblpritems.pr_solicitation_id
    , tblpritems.date_item_received
    , tblpritems.Quantity_received
    , tblpritems.Remarks_on_receipt
    , tblpritems.Received_by
FROM
    prts.tblpritems
    INNER JOIN prts.tblpr 
        ON (tblpritems.PRID = tblpr.PRID)
    INNER JOIN prts.tblprsolicitations 
        ON (tblprsolicitations.PRID = tblpr.PRID) AND (tblpritems.pr_solicitation_id = tblprsolicitations.pr_solicitation_id)
    INNER JOIN prts.tblprsuppliers 
        ON (tblprsuppliers.pr_solicitation_id = tblprsolicitations.pr_solicitation_id)
    INNER JOIN prts.tblpo 
        ON (tblpo.pr_supplier_id = tblprsuppliers.pr_supplier_id)
  where tblpr.PRID=".$val." and tblpo.PO_Status_ID=7 and item_received_status=0
    ";
    $connection = Yii::$app->getDb();
    $command = $connection->createCommand($sql);
    $result = $command->query();

    $dataProvider = new ActiveDataProvider([
        'query' => $result,
    ]);

当我尝试var_dump($dataProvider)是当它返回错误

如果你想使用原始sql，你应该使用sqlDatProvider http://www.yiiframework.com/doc-2.0/guide-output-data-providers.html#sql-data-provider

    $sql ="
        SELECT
    tblpritems.PRlineID
    , tblpritems.Tracking_Code
    , tblpritems.Description
    , tblpritems.Quantity
    , tblpritems.Unit_Price
    , tblpritems.Extended_price
    , tblpritems.PRID
    , tblpritems.pr_solicitation_id
    , tblpritems.date_item_received
    , tblpritems.Quantity_received
    , tblpritems.Remarks_on_receipt
    , tblpritems.Received_by
FROM
    prts.tblpritems
    INNER JOIN prts.tblpr 
        ON (tblpritems.PRID = tblpr.PRID)
    INNER JOIN prts.tblprsolicitations 
        ON (tblprsolicitations.PRID = tblpr.PRID) AND (tblpritems.pr_solicitation_id = tblprsolicitations.pr_solicitation_id)
    INNER JOIN prts.tblprsuppliers 
        ON (tblprsuppliers.pr_solicitation_id = tblprsolicitations.pr_solicitation_id)
    INNER JOIN prts.tblpo 
        ON (tblpo.pr_supplier_id = tblprsuppliers.pr_supplier_id)
  where tblpr.PRID=".$val." and tblpo.PO_Status_ID=7 and item_received_status=0
    ";

 $sqlCount = "
        SELECT
    count(*)
FROM
    prts.tblpritems
    INNER JOIN prts.tblpr 
        ON (tblpritems.PRID = tblpr.PRID)
    INNER JOIN prts.tblprsolicitations 
        ON (tblprsolicitations.PRID = tblpr.PRID) AND (tblpritems.pr_solicitation_id = tblprsolicitations.pr_solicitation_id)
    INNER JOIN prts.tblprsuppliers 
        ON (tblprsuppliers.pr_solicitation_id = tblprsolicitations.pr_solicitation_id)
    INNER JOIN prts.tblpo 
        ON (tblpo.pr_supplier_id = tblprsuppliers.pr_supplier_id)
  where tblpr.PRID=".$val." and tblpo.PO_Status_ID=7 and item_received_status=0
    ";

    $count = Yii::$app->db->createCommand($sqlCount)->queryScalar();
    $provider = new SqlDataProvider([
    'sql' => $sql,
    'params' => [':status' => 1],
    'totalCount' => $count,
    'pagination' => [
        'pageSize' => 10,
    ],
    'sort' => [
        'attributes' => [
              ......
        ],
    ],
]);

否则如果你想要activeDataProvider你可以使用

假设activeRecord与Pritems模型相关

  $query  =  Pritems::findBySql($sql)

  $provider = new ActiveDataProvider([
    'query' => $query,
    'pagination' => [
        'pageSize' => 10,
    ],
    'sort' => [
        'defaultOrder' => [
             ....
        ]
    ],
]);