Reducing queries Yii2

Question

I`m really stuck on this one.

I have a table with contracts, and one with items. What Im trying to achieve is to see the total quantity of an item on contracts in a date range for every day. For example, item1 appears on 3 contracts on Monday and the total quantity is 30. Currently, Im doing a while loop for each day in the selected date range and using this query

$itemQty = ContItems::find()
            ->where(['ITEMNO' => $itemNo])
            ->andWhere(['<=' , 'HIREDATE' , $today])
            ->andWhere(['>=' , 'ESTRETD' , $today])
            ->andWhere(['or',
                ['STATUS' => 0],
                ['STATUS' => 1]
            ])
            ->SUM('QTY');

It is working fine, but I want to check for every item I have for each day in the selected date range, and the number of queries goes up to 50k+ if I do that.

What I tried to do is selected every contract in a date range and stored in an array Every contract has a start and end date and I want to select every contract where the start or end date is in the date range I specified.

$ContItemsArray = ContItems::find()
            ->where(['or',
                        ['<=' , 'HIREDATE' , $dateFrom],
                        ['<=' , 'HIREDATE' , $dateTo],
                        ['>=' , 'HIREDATE' , $dateFrom],
                        ['>=' , 'ESTRETD' , $dateTo],
                        ['>=' , 'ESTRETD' , $dateFrom]
                    ])
            ->andWhere(['or',
                ['STATUS' => 0],
                ['STATUS' => 1]
            ])
            ->asArray()
            ->all();

And use this code to count the quantities

$theQty = 0;    
        foreach ($ContItemsArray as $subArray) 
        {   
            if ($subArray['ITEMNO'] == $itemNo && ($subArray['HIREDATE'] <= $today && $subArray['ESTRETD'] >= $today) && ($subArray['STATUS'] <= 1))
            {   
                $theQty += $subArray['QTY'];
            }
            else 
            {
                $theQty += 0;
            }       
        }

But it is returning incorrect numbers, contracts are missing for some reason and I can`t figure it out why. I tried to change around the query for the $ContItemsArray but with no luck. I hope I was clear enough with the question. I would appreciate any help.

Edit: ContItems table has all the contracts with the item number 'ITEMNO' start date 'HIREDATE' end date 'ESTRETD' and the quantity 'QTY' fields.

And I have an array with all the item numbers what I want to check. The expected result would be, if the day I`m checking is between the contract start date and end date, sum the qty of the items.

Still unclear to me. Can you please provide sample data and expected output also? — Insane Skull

Zsolt Turu Zsolt Turu · Accepted Answer · 2018-12-03T13:56:58

I solved the problem, had to use date('Y-m-d') and strtotime on the dates coming from the table (HIREDATE, ESTRETD) and now it is working fine

Reducing queries Yii2

1 Answers