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

Where are you using $dataProvider->getAttributes() ?

Nico Savini
– Nico Savini

2016-08-24 13:53:22 +00:00
Commented Aug 24, 2016 at 13:53 — Nico Savini
– Nico Savini, Commented Aug 24, 2016 at 13:53

ScaisEdge · Accepted Answer · 2016-08-23 19:57:41Z

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

answered Aug 23, 2016 at 19:57

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nico Savini Over a year ago

For complex queries, you could also use Yii::$app->db->createCommand($sql)->queryAll(); which will not return the models (it will return an array) but it's much cheaper from the resources point of view

Collectives™ on Stack Overflow

Changing sql query to yii2 format

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related