Help with SQL query

[madpugger]

Hi and thanks for any help.

I am looking to make an SQL query to allow staff to query the database to see which order numbers have a particular product in it - preferably in the reserved state.

I have asked this question in a different way before and not really gained much response so I'm trying again!

 

Basically the issue is we need a way of seeing who has ordered an item when it comes back into stock (It could be two years down the line in our industry!).

 

Thanks

[Guest]

If you are a company and you have employees, it pays to invest in a tailor-made module.
If Prestashop should have all the features that everyone needs, then Prestashop has 5 GB of installation and the whole run would be slow.
Realize that this is an open source solution for free and that Prestashop has the basic functions.
Do you want something extra?
Invest !!

[madpugger]

I am not a large enough company to pay for lots of modules yet, if I was, I probably wouldn't use prestashop...

Anyway, your response isn't really helpful to the question.

I am looking for an SQL Query to find this info. I would also be keen to know where "reserved" is noted in the database if anyone can help.

[ClassyDevs]

Hello, @madpugger if you give me the details I can help you in the query. We have experience application level software development in the past.

 

Please describe details .

Thank you 

[madpugger]

Thank you!

 

As mentioned, it should be a very generic query useful for all prestashop users. 
 

we are on 1.7.7.0

 

Query should identify order numbers associated with a particular product in the reserved state. 
 

as example:

I have 0 physical stock of product SL-E195

i have 8 items on back order across four orders, so “reserved” (as seen in Cataglogue/Stocks) is 8 and Available is -8

 

When this item comes in stock, physical would be 50, reserved 8, Available 42

We need to be given a list of order numbers that are causing the “Reserved” number to show 8 so we can process these. 
 

the stock code would obviously be dynamic. 

[musicmaster]

Prestools can do such searches. SO you can just look how it does it.

[madpugger]

On 12/15/2020 at 8:44 PM, musicmaster said:

Prestools can do such searches. SO you can just look how it does it.

I’ve had a look at the demo and can’t see a way of finding this?

[musicmaster]

Order Search, where else?

 

If you enable Verbose you will see the query.

[madpugger]

3 hours ago, musicmaster said:

Order Search, where else?

 

If you enable Verbose you will see the query.

The demo version on their website doesn’t have this I don’t think! It’s on a very old version of presta. 
 

thanks. 

[Guest]

Only now did I understand what you were asking for.
An SQL query can be created by order status id, product id, and product attribute id.
There are two columns in ps_order_detail table, product_quantity and product_quantity_refunded.
So you need to find out the number of product_quantity_refunded which is less than 1 and the order status is eg out of stock.
Is that right?

[madpugger]

Hi, thanks.

Perhaps this will make it clearer:

I need to know which order numbers these two reserved items belong to.

This item was at:

Physical: 0

Reserved: 2

Available: -2

Then some stock came in from the supplier, so I add 19 so it then shows as per the image:

Physical: 19

Reserved: 2

Available: 17

It may have been months - or years in our industry since these orders were made, so I need to be able to find who ordered these items easily.

 

I hope this helps to understand!

[Guest]

Taken logically, the ps_order_deatail table is missing a column such as reserved_product_quantity. This column should always be filled in when completing the order and finding out the current state of the warehouse.
And in the case of replenishing the warehouse and changing the status of the order, change the value in this column to zero. Then everything would be easier.
Prestashop developers should think about this.
Now a completely meaningless value is written to the ps_order_detail table.

[Guest]

Yes I understand.
As I wrote, it is possible to find out by the id status of the order.

[madpugger]

1 minute ago, 4you.software said:

Taken logically, the ps_order_deatail table is missing a column such as reserved_product_quantity. This column should always be filled in when completing the order and finding out the current state of the warehouse.
And in the case of replenishing the warehouse and changing the status of the order, change the value in this column to zero. Then everything would be easier.
Prestashop developers should think about this.
Now a completely meaningless value is written to the ps_order_detail table.

Could not agree more!

Ideally, this "Stock" page should show a dropdown like the new "Orders" page which reveals all orders that are in the reserved state for that item. I.e. we click a small downward chevron that then shows a list of (clickable links to ) orders.

 

Currently the Order_Detail is missing Reserved. It must be calculated in the "Stocks" page itself. 

I guess the SQL query for now is:

• Search for Product X
• In orders
• In Current State: (List of all states not yet considered completed)

The trouble is, I don't know SQL well enough to do this!

 

[madpugger]

Actually, is there not an order completed record in order detail? (I'll go look!)

If so:

• Search for Product X
• In Order
• Where order Completed is 0

All in Order_Detail table should be the SQL query?

[Guest]

Admin -> Orders

Admin -> Order settings -> Statuses

 

So you can find out all orders with status id 9 and id 12 in the database accordingly.
For example:

SELECT * FROM `ps_orders` WHERE `current_state` IN (9,12);

Result:

Now you know which orders have the status on the backorder.

And now it is no longer a problem to find out which products are not in stock.

SELECT a.`id_order`, b.`product_id`, b.`product_attribute_id`, b.`product_name`, b.`product_quantity` 
FROM `ps_orders` a
LEFT JOIN `ps_order_detail` b ON (a.`id_order` = b.`id_order`)  
WHERE a.`current_state` IN (9,12) AND b.`product_quantity_in_stock` < 1;

Result:

 

And that's about all I can advise you.

[madpugger]

Thanks!

OK, so in the Table "orders" there is a record for "valid"

I want to list order numbers of a specific product.

So the query I need is:

SELECT * FROM `ps_orders` WHERE `valid` = 0;

(This is the bit I don't know)

In 'ps_order_detail' there will be a list of orders agains the product.

So I guess:

SELECT a.`id_order`, b.`product_id`, b.`product_attribute_id`, b.`product_name`, b.`product_quantity` FROM `ps_orders` a LEFT JOIN `ps_order_detail` b ON (a.`id_order` = b.`id_order`) WHERE a.`valid` = 0 AND b.`product_id` = X; {   - X being the product we want to look for}

[Guest]

all product( valid = 0):

SELECT a.`id_order`, b.`product_id`, b.`product_attribute_id`, b.`product_name`, b.`product_quantity` 
FROM `ps_orders` a
LEFT JOIN `ps_order_detail` b ON (a.`id_order` = b.`id_order`)  
WHERE a.`valid` = '0' AND b.`product_quantity_in_stock` < 1;

specific product (valid = 0 and id product = 46 and attribute = 0):

SELECT a.`id_order`, b.`product_id`, b.`product_attribute_id`, b.`product_name`, b.`product_quantity`
FROM `ps_orders` a
LEFT JOIN `ps_order_detail` b ON (a.`id_order` = b.`id_order`)  
WHERE a.`valid` = '0' AND b.`product_quantity_in_stock` < 1 AND b.`product_id` = 46 AND b.`product_attribute_id` = 0 ;

 

Edited December 20, 2020 by Guest (see edit history)

[madpugger]

7 hours ago, 4you.software said:

all product( valid = 0):

SELECT a.`id_order`, b.`product_id`, b.`product_attribute_id`, b.`product_name`, b.`product_quantity` 
FROM `ps_orders` a
LEFT JOIN `ps_order_detail` b ON (a.`id_order` = b.`id_order`)  
WHERE a.`valid` = '0' AND b.`product_quantity_in_stock` < 1;

specific product (valid = 0 and id product = 46 and attribute = 0):

SELECT a.`id_order`, b.`product_id`, b.`product_attribute_id`, b.`product_name`, b.`product_quantity`
FROM `ps_orders` a
LEFT JOIN `ps_order_detail` b ON (a.`id_order` = b.`id_order`)  
WHERE a.`valid` = '0' AND b.`product_quantity_in_stock` < 1 AND b.`product_id` = 46 AND b.`product_attribute_id` = 0 ;

 

Amazing thanks!

I'll have a play around with it to see if we can get it just the way we need, but from that, we should be able to make an adjustment on "Stocks" to show order numbers against "Reserved"!

Thanks so much for the help!

[Guest]

I gladly helped.
You can give a like by clicking on the gray heart below my posts.

[madpugger]

Actually,

Thinking about this, it still doesn't quite work...

If this query is run after the replenishment of stock is ordered then the 'product_quantity_in_stock' will not be < 1.

So in this case, for both queries, we just need to remove the AND 'product_quantity...'

[Guest]

Are you sure about your statement?
The data in the order_detail table does not change !!!

[Guest]

You have to look at the tables in the database yourself to know the dependencies and where to write what.
I recommend that you create a new order and check what is changing in the tables.

[Guest]

Sorry.

If you want to show orders where the warehouse was less than 1, you must write the condition.
I don't think you even know the basics of programming and logic math.

[madpugger]

6 minutes ago, 4you.software said:

Sorry.

If you want to show orders where the warehouse was less than 1, you must write the condition.
I don't think you even know the basics of programming and logic math.

Harsh. 
 

I take your point about the order_detail not changing. I was thinking that the product quantity was dynamic but of course it is not. I had just worked a 14 hour night shift in my primary job, and I am also running the business on top of this as well as having to manage the website. 
 

Thanks for the help, but your criticism is uncalled for.