[FIXED] Passing raw sql to yii2 active dataProvider

Issue

Am passing raw sql via yii2 create command but i always get an error of property must be an instance of a class that implements the QueryInterface e.g. yii\db\Query or its subclasses

This is the code

      $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,
    ]);

When i try var_dump($dataProvider) thats when it returns the error

Solution

If you want use raw sql you should use 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' => [
              ......
        ],
    ],
]);

otherwise if you want activeDataProvider you can use

Assiming that the activeRecord is related to the Pritems model

  $query  =  Pritems::findBySql($sql)


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

Answered By - ScaisEdge