Changing sql query to yii2 format

Question

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

This is the raw 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

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']);
}

Currently am using

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

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

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

How can i achieve this

This is the database schema

Answer 1

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();