CODEWITHSUNDEEP
yii2
ChrisB
ChrisB

Reputation: 631

Yii2 - replacement for beforeFind to optimize nested views in MySql

Yii1 used to have beforeFind method in which you could modify the query or whatever else you might want to do.

In Yii2 the suggested alternative is to use the modelQuery solution for example

class MyModel extends \yii\db\ActiveRecord
{

  public static function find()
  {
      return new MyModelQuery(get_called_class());
  }
/* ... */
}

and 

 class MyModelQuery extends \yii\db\ActiveQuery
 {
   public function init( )
   {
     /** do something here **/
   }
 }

But how do I pass or reference MyModel within MyModelQuery? For example:-

class MyModelQuery extends \yii\db\ActiveQuery
{
   public function init( )
   {
     $sql = "SET @variable = {$MyModel->variable1}";
   }
 }

EDIT

For completeness, I've added a use case to help others in future.

I have nested views with group by's running under MySql and it runs VERY badly.

In my case, I have orders, order-items and order-item-fees tables, each one-to-many to the next and I want to sum the order totals. I have nested view, one at each level to sum to the level above, but at the order-item and order-item-fee levels MySql is grouping the whole table first (I cannot use algorithm=merge as I have a GROUP BY).

I'm implementing the Pushdown method where you define a SQL variable to use in sub-views to narrow down the search as outlined here: http://code.openark.org/blog/mysql/views-better-performance-with-condition-pushdown and also here https://www.percona.com/blog/2010/05/19/a-workaround-for-the-performance-problems-of-temptable-views/

In this way, if I can add a 'WHERE order_id=' to the where clause of the two sub-views, I reduce a 3.5 second query down to 0.003 second query.

So using, Salem's suggestion below, I can execute a SQL statement 'SET @order_id=1234' before my query, which is then picked up in the order-item and order-item-fee views using a function. Note: this is connection specific, so no danger of collisions between sessions.

A bit convoluted but fast.

It would be interesting, though, to see a performance comparison between SQL and looping in PHP perhaps....

EDIT 2

In fact, you normally use find() as a static method, so there is no way of using $this->order_id, so I changed this to over-ride the findOne method

public static function findOne( $orderId )
{
    if ( isset($orderId) )
    {
        $sql = "SET @orderId='{$orderId}'";
        Yii::$app->db->createCommand($sql)->execute();
    }

    return parent::findOne( $orderId );
}

I also use this view with other searches, so in the view I need to check whether the orderId is set or not ...

where  (
        CASE 
        WHEN ( NOT isnull( get_session_orderId() ) )
          THEN `order`.order_id = get_session_cartref()
          ELSE `order`.order_id LIKE '%'
          END
          )

Upvotes: 4

Views: 1087

Answers (1)

Salem Ouerdani
Salem Ouerdani

Reputation: 7886

About how to involve an ActiveQuery class check my answer here:

Yii2 : ActiveQuery Example and what is the reason to generate ActiveQuery class separately in Gii?

But if what you are trying to do doesn't require building named scopes then you may simply override the find method by something like this:

public static function find()
{
    return parent::find()->where(['variable' => 'some value']);
}

Upvotes: 1

Related Questions