Jump to content

Help with SQL query


madpugger

Recommended Posts

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

Link to comment
Share on other sites

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 !!

Link to comment
Share on other sites

  • 3 weeks later...

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.

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

Hi, thanks.

Perhaps this will make it clearer:

1528445266_Screenshot2020-12-20at07_07_22.thumb.png.e4760bd004974defb466a5c2281d00cf.png

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

 

Link to comment
Share on other sites

Admin -> Orders

obrazek.thumb.png.f7dc579513036248e664e244b79aa7b6.png

Admin -> Order settings -> Statuses

obrazek.png.7e67a01bd8a41846cc507c6ded13b801.png

 

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:

obrazek.thumb.png.c679e534a1d0eddfbedeec23f070b74b.png

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:

obrazek.png.c5dffdf1ac696da083c88224483e818a.png

 

And that's about all I can advise you.

Link to comment
Share on other sites

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}

Link to comment
Share on other sites

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 by Guest (see edit history)
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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...'

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...