将 sql 查询更改为 yii2 格式 [英] Changing sql query to yii2 format

问题描述

我正在使用 dataProvider，我想在 sql 中转换查询，以便它遵循 dataProvider 表示数据的方式

Am using dataProvider and i would like to convert a query in sql so that it follows the dataProvider way of representing data

这是原始的 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

这些是我在 tblpritems 中的关系

These are the relations i have in the tblpritems

     public function getPR()
{
    return $this->hasOne(Tblpr::className(), ['PRID' => 'PRID']);
}

/**
 * @return \yii\db\ActiveQuery
 */
public function getPrSolicitation()
{
    return $this->hasOne(Tblprsolicitations::className(), ['pr_solicitation_id' => 'pr_solicitation_id']);
}

目前正在使用

$dataProvider = new SqlDataProvider([
        'sql' => $sql,
        'pagination' => [
            'pageSize' => 10,
        ],
    ]);

但问题是我无法在sql dataProvider中访问$dataProvider->getAttributes()

But the problems is that i cant access $dataProvider->getAttributes() in sql dataProvider

我希望上面的sql代码是这种格式

I would like the sql code above to be in this format

$query = Tblprsuppliers::find()
        ->Joinwith('prSolicitation', 'prSolicitation.pr_solicitation_id = tblprsuppliers.pr_solicitation_id')
        ->Joinwith('supplier', 'supplier.supplier_id = tblprsuppliers.supplier_id')
        ->JoinWith('currency', 'currency.CurrencyID = tblprsuppliers.currency_id ');

我怎样才能做到这一点

这是数据库架构

推荐答案

当你有复杂的 sql 相关的返回模型的实例时，你可以这样使用 findBySql

when you have complex sql related that return instance of model you can use findBySql this way

      $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';

   $model = Pritems::findBySql($sql)->all();  

这篇关于将 sql 查询更改为 yii2 格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！