Complex query where sum of associated model is variable in query

Need Help
1

Hi everyone,

I don’t even know how to correctly state my issue in one scentence (topic), but I’ll try:
I need to make a query that takes a sum of an associated model as a variable for the query.

First, the situation (of course: simplyfied):

I have a PRODUCTS table
with field ‘id’ and ‘name’

I have a PRODUCT_STOCKS table (joined with PRODUCTS through ‘product_id’)
with fields ‘free_stock’ and ‘minimum_stock’
The ‘free_stock’ are the number of items I actually have in stock right now. The ‘minimum_stock’ is the number of items I want to have in stock as a minimum to have enough items to sell that day

I have a ORDER_ITEMS table (joined with PRODUCTS through ‘product_id’)
with fields ‘qty_in_backorder’ and ‘status_id’
(status_id can be 1=ordered and 2=cancelled)

At the start of the day, I want to know my stockvalues, so I do this query:

<?php
$query = $this->Products
    ->find('all')
    ->contain(['ProductStocks']);
?>

The result could be:

id name free_stock minimum_stock
1 ItemA 5 5
2 ItemB 1 1
3 ItemC 2 2
3 ItemD 7 3

That day, I only sell 3 ItemA, and nothing more.
At the end of the day, I want to know what to re-order at my suppliers.
So I do the the following query on my Products:

<?php
$query = $this->Products
    ->find('all')
    ->contain(['ProductStocks'])
    ->where('ProductStocks.stock_free < ProductStocks.stock_minimum');
?>

And I get:

id name free_stock minimum_stock to_be_ordered (calculated field)
1 ItemA 2 5 3

So, I now know to order ItemA three times. So far so good. I will now start ordering these products.

My supplier-A can sell me just 1 ItemA.
Supplier-B (who is more expensive) can also supply 1 of ItemA (and there are no other suppliers).
So, I order accordingly.
My OrderItems table (which was empty before) now has these rows:

id supplier product_id status_id qty_in_backorder (items ordered but not yet received)
1 supplier-A 1 1 (ordered) 1
1 supplier-B 1 1 (ordered) 1

So: I now have ordered ItemA 2 times. At 2 different suppliers, both having status_id 1 (ordered)

Let’s say, the next day I do not sell anything. And at the end of the day, I want to make the same ‘what to re-order’ query.

But I now already have ordered ItemA 2 times.
So I would like to know how many I have to re-order. In this case that would be: 1
Because: I want 5 (minimum stock), I have 2 in stock. I already ordered 2 (so: 4 in total), so, I now only need 1 to meet my minimum_stock.

What I would like the outcome to be, is:

id name free_stock minimum_stock already ordered (in backorder) to_be_ordered (calculated field)
1 ItemA 2 5 2 1

So, written out in plain text, that would be something like:
1: Make a query on the Products table and find all items that have a free_stock < minimum_stock.
2: for each item that meets those criteria: Store the difference between free_stock and minimum_stock in a variable ‘to_be_ordered’
3: Then, for each item in step 1: check in OrderItems table if there are any items on there with that product_id AND with status=1. And SUM all ‘qty_in_backorder’ to find out how many have already been ordered (and store that SUM in variable ‘sum_allready_ordered’).
4: Now, if (minimum_stock > (‘sum_allready_ordered’'+‘free_stock’)) THEN show that product, and suggest the number 1 to be ordered (not 3). But if not, don’t show product at all (because: I already ordered the number of items that I want. I just did not yet receive them).

Before you ask: What kind of query do you have now… To be honest: nothing. I simply don’t know how to build such a query. I don’t know to use matching, notMatching, having, etc.

My best guess would be something like:

<?php
$query = $this->Products
    ->find('all')
    ->contain([
        'ProductStocks',
        'OrderItems' => function ($q) {
            return $q
                ->select([
                    'OrderItems.id',
                    'OrderItems.product_id',
                    'total_backorder' => 'SUM(OrderItems.qty_in_backorder)'
                ])
                ->where(['OrderItems.status_id' => 1]);

        }
    ])
    ->where(['ProductStocks.stock_minimum > ' => ('total_backorder + ProductStocks.stock_free')]);

Hopefuly someone has the time and patience to help me out. Thanks for your attention!

P.S I am using CakePHP 3.8.*

2 Likes
2

You have to use sql functions and also you can use leftJoinWIth, innerJoinWith and matching (see the book.

Your first query looks like this:

$query = $this->Products
    ->find('all')
    ->innerJoinWith('OrderItems')
    ->innerJoinWith('ProductStocks')
    ->select($this->Products) // includes all columns from products table
    ->select([
        'ProductStocks.stock_free',
        'ProductStocks.stock_minimum',
        'to_be_ordered' => $query->newExpr()
            ->setConjunction('-')
            ->add($query->identifier('ProductStocks.stock_minimum'))
            ->add($query->identifier('ProductStocks.stock_free')),
            // this should result in "ProductStocks.stock_minimum - ProductStocks.stock_free"
    ])
    ->where([
        'ProductStocks.stock_free <' => $query->identifier('ProductStocks.stock_minimum'),
    ])
    ->group(['Products.id']);

This shows all the products that should be ordered.

Now, to make your second query, include whats ordered and update the to_be_ordered column.
I’m using leftJoinWith to not filter items without order.

$query = $this->Products
    ->find('all')
    ->innerJoinWith('OrderItems')
    ->innerJoinWith('ProductStocks')
    ->leftJoinWith('OrderItems', function (Query $query) {
        return $query->where(['status_id' => 1]); // ordered items
    })
    ->select($this->Products) // includes all columns from products table
    ->select([
        'ProductStocks.stock_free',
        'ProductStocks.stock_minimum',
        'already_ordered' => $query->func()->sum('OrderItems.qty_in_backorder'),
        'to_be_ordered' => $query->newExpr()
            ->setConjunction('-')
            ->add($query->identifier('ProductStocks.stock_minimum'))
            ->add($query->identifier('ProductStocks.stock_free'))

            // you cant reference a calcualted column in select
            ->add($query->func()->sum('OrderItems.qty_in_backorder')),

            // this should result in
            // "ProductStocks.stock_minimum - ProductStocks.stock_free - SUM(ordered)"
    ])
    ->group(['Products.id'])
    ->having([
        'ProductStocks.stock_minimum >' => $query->newExpr()
            ->setConjunction('+')
            ->add($query->identifier('ProductStocks.stock_free'))
            ->add($query->identifier('to_be_ordered'))
    ]);

I cant test this now (since i would have to create a db, etc) but should work.

PS. I really love the whole explanation and the formatting is top notch!

2 Likes
3

WOW! Raul!!
First of all: THANK YOU. This is very, very helpful. It gives me a lot of insights already on how to handle issues like this one (for instance: I did not know this: ->select($this->Products) includes all columns from products table ).
So: Thank you for your time and complete answer. I am very grateful.

Now, for the lesser news :wink: The query does not yet work. When I run the first query, it gives me this error:

# Call to a member function newExpr() on null

My IDE (PHPStorm) also gives me an indication that the first use of ‘$query’ (the one right before $query->newExpr() ) is not correct. It says "Undefined variable ‘$query’ ".

If I change the query to this:

           $query = $this->Products->find('all');
           $query->innerJoinWith('OrderItems')
            ->innerJoinWith('ProductStocks')
            ->select($this->Products) // includes all columns from products table
            ->select([
                'ProductStocks.stock_free',
                'ProductStocks.stock_minimum',
                'to_be_ordered' => $query->newExpr()
                    ->setConjunction('-')
                    ->add($query->identifier('ProductStocks.stock_minimum'))
                    ->add($query->identifier('ProductStocks.stock_free')),
                // this should result in "ProductStocks.stock_minimum - ProductStocks.stock_free"
            ])
            ->where([
                'ProductStocks.stock_free <' => 'ProductStocks.stock_minimum',
            ])
            ->group(['Products.id']);

Then that error message is gone, but I get another one:

Cannot convert value of type string to integerInvalidArgumentException

And now, I’m stuck. This might be something I can figure out (and it might not), but if by any chance you (or anyone) could point out what to do to fix this, I would be much obliged!
(and if not: I am already very happy with the given answer)

(ps: thanks for the formatting compliment :blush: )

1 Like
4

I edited the answer.
The problem was in the where clause.

->where([
    'ProductStocks.stock_free <' => $query->identifier('ProductStocks.stock_minimum'),
])

it was missing the $query->identifier(), i guess cakephp was expecting an integer or a QueryExpression

1 Like
5

Hi Raúl,

The query now runs flawlessly and returns the desired outcome!

Again: I can not thank you enough! I have been struggeling with this for weeks (if not months) because I hoped to be able to figure it out myself. But I don’t think I would have ever thought of this. I have learned A LOT from this. So again: Thank you!

1 Like