1. Two solutions:
2. 1. Build view in SQL database to simplify the search in advance.
3. 2. Build SQL query in Yii2.
4. Steps:
5. 1. use yii\db\Expression;
6. 2. Create complexity expression in advance, example as below
7. 3. One tips for sort for that complexity expression

As for complexity SQL query we have two solutions as below, I will discuss #2 in this article.

Two solutions: ¶

1. Build view in SQL database to simplify the search in advance. ¶

2. Build SQL query in Yii2. ¶

Steps: ¶

1. use yii\db\Expression; ¶

2. Create complexity expression in advance, example as below ¶

You have to use DB expression, or else Yii2 will add special character ` into each column and cause you cannot successfully construct the correct SQL, and will encounter run time error.

public function search($params, $userId = 0, $isMyQueue = false)
    {
        if (empty($userId)) {
            if (!is_a(Yii::$app, 'yii\console\Application')) {
                $userId = yii::$app->user->id;
            }
        }

        $excludeStatus = EOPStatus::Close . "," . EOPStatus::Cancel;

        //build your complexity query here
        $FO_Due_Expression = "if(t.Status_Id in ($excludeStatus),'No',
                        if(t.Version <> 1,
                        if(DATE_ADD(CURDATE(),INTERVAL m.Approved DAY)>p.New_Version_Due_Date and IsNULL(Forecast_Qty),'Yes','No'),
                        if(DATE_ADD(CURDATE(),INTERVAL m.Approved DAY)>p.Mass_Lock_Due_Date and IsNULL(Forecast_Qty),'Yes','No')
                        )
                    )";

        //Another complexity SQL
        $PO_Due_Expression = "if(t.Status_Id in ($excludeStatus),'No',
                        if(t.Version <> 1,
                        if(DATE_ADD(CURDATE(),INTERVAL m.Close DAY)>p.New_Version_Due_Date and IsNULL(PO_Qty),'Yes','No'),
                        if(DATE_ADD(CURDATE(),INTERVAL m.Close DAY)>p.Mass_Lock_Due_Date and IsNULL(PO_Qty),'Yes','No')
                        )
                    )";
        $query = EOPRegionalPart::find()
            ->select(['t.*', 'part.Platform_Id', 'Platform_Name' => 'p.Platform_Name', 'Platform_Create_At' => 'p.Create_At', 'part.Part', 'part.Description',
                    'r.Sub_Region as Region',
                    'm.Milestone_Type', 'm.Color as Milestone_Color', 'm.Pending_Approval as PA_LT',
                    'm.Approved as Approved_LT', 'm.Close as PO_LT', 'm.Notify as Notify_LT', 'm.WIP as WIP_LT',
                    'type.Forecast_Type', 'p.EOP_Target_Date',
                    'Vendor' => 'v.name', 'GSM' => 'gsm.username', 'p.Mass_Lock_Due_Date',
                    'Status' => 's.Status_Description',
                    'p.Platform_Status_Id', 'p.Platform_Sales_Kpcs', 'p.Platform_LTS_Kpcs', 'p.Platform_Note', 'part.Part_Note',
                    'p.Mass_Lock_Due_Date', 'p.EOP_Target_Date',
                    'p.New_Version_Due_Date',
                    'c.Commodity',
                    'cc.Comm_Code',
                    'rp.username as Region_Planner', 'poc.username as EOP_POC', 'owner.username as Current_Owner',
                    new Expression($FO_Due_Expression . " as FO_Due"), //Attach that expression
                    new Expression($PO_Due_Expression . " as PO_Due"), //Attach another
                ]
            )
            ->from('eop_regional_part t')
            ->leftJoin('eop_status s', 't.Status_Id = s.id')
            ->leftJoin('npi_region r', 'r.id = t.Region_Id')
            ->leftJoin('eop_part part', 't.Part_Id = part.id')
            ->leftJoin('eop_platform p', 'part.Platform_Id = p.id')
            ->leftJoin('eop_milestone m', 'm.id = p.Milestone_Type_Id')
            ->leftJoin('eop_type type', 'type.id = p.EOP_Type_Id')
            ->leftJoin('npi_commodity c', 'c.id = part.Commodity_Id')
            ->leftJoin('npi_comm_code cc', 'cc.id = part.Comm_Code_Id')
            ->leftJoin('npi_vendor v', 'v.id = p.Vendor_Id')
            ->leftJoin('tbl_users gsm', 'gsm.id = p.GSM_Id')
            ->leftJoin('tbl_users rp', 'rp.id = t.Region_Planner_Id')
            ->leftJoin('tbl_users poc', 'poc.id = t.EOP_POC_Id')
            ->leftJoin('tbl_users owner', 'owner.id = t.Current_Owner_Id');

        $sort = [
            'defaultOrder' => [
                'Update_At' => SORT_DESC,
//                'Platform_Name' => SORT_ASC,
            ],
            'attributes' => ['id', 'Version', 'Part', 'Region', 'Description', 'Part_Cost', 'FIR', 'Region_Planner', 'EOP_POC', 'Current_Owner', 'Part_Note', 'Create_At', 'Update_At', 'Platform_Name', 'Milestone_Type', 'Forecast_Type', 'EOP_Target_Date',
                'Vendor', 'GSM', 'Mass_Lock_Due_Date', 'Commodity', 'Comm_Code', 'Status',
                'Commodity', 'Comm_Code', 'Description',
                'Platform_Status_Id',
                'Platform_Sales_Kpcs', 'Platform_LTS_Kpcs', 'PO_Qty', 'Forecast_Qty', 'PO', 'Region_Part_GSM_Note', 'Region_Part_Planner_Note',
                'Region_Sales_Kpcs', 'Region_LTS_Kpcs', 'Mass_Lock_Due_Date', 'New_Version_Due_Date', 'EOP_Target_Date',
                'Platform_Note', 'Part_Note', 
                'FO_Due', 'PO_Due', //**these are the two complexity column name, allow sorting.**
            ],
        ];

3. One tips for sort for that complexity expression ¶

//... ...

    $query->andFilterWhere(['like', new Expression($FO_Due_Expression), $this->FO_Due])
          ->andFilterWhere(['like', new Expression($PO_Due_Expression), $this->PO_Due]);
    
    //... ...

   return $dataProvider;