How Prestashop manages the quantity of products

PrestaShop 8 manages product quantities through the ps_stock_available table, which is the central point for keeping track of available quantities for each product and combination (if the product has attributes).

Let's look at the details of how it works and some practical examples.

Stock_available table

Main structure of relevant fields:

  1. id_stock_available: unique ID of the row.
  2. id_product: ID of the product to which this row refers.
  3. id_product_attribute: ID of the product attribute (0 if it is the product without combinations).
  4. id_shop: ID of the store (useful in multi-store configurations).
  5. id_shop_group: ID of the store group (useful in multi-store configurations).
  6. quantity: Available quantity of the product or combination.
  7. physical_quantity: Actual physical quantity in the warehouse (if advanced warehouse tracking is enabled).
  8. reserved_quantity: Reserved quantity (such as for orders being processed).
  9. out_of_stock: Setting that indicates whether the product can be ordered even if out of stock.

Database access from the PrestaShop back-end

PrestaShop offers built-in tools to interact with the database directly from the back-end, without the need to access external software such as phpMyAdmin and in maximum security.

To access: Advanced Parameters → Database → SQL Manager

Examples of queries for checking stock status

1. Check the quantity available for a specific product

This query returns the quantity available for a specific product (without combinations):

[code=language-sql] SELECT quantity FROM [PREFIX]_stock_available WHERE id_product = 123 AND id_product_attribute = 0[/code]

Adapting queries to prefix

Because table prefixes can vary between installations, in SQL queries you must manually replace the placeholder [PREFIX] with the prefix actually used in your configuration.

How to verify the prefix used?

If you don't know the prefix used in your installation, you can easily check it: app/config/parameters.php and look for the setting: database_prefix

Learn More

2. Check the quantity for all combinations of a product

If a product has combinations (e.g., size, color), you can use this query to see the quantities:

[code=language-sql] SELECT sa.id_product_attribute, pa.reference, sa.quantity FROM [PREFIX]_stock_available sa JOIN [PREFIX]_product_attribute pa ON sa.id_product_attribute = pa.id_product_attributeWHERE sa.id_product = 123[/code]

Check open orders for a particular product

Orders are handled primarily in the order_detail table, which is linked to orders and products.

1. Find open orders for a specific product

To find orders in "open" status (not shipped or completed) for a specific product:

[code=language-sql] SELECT od.id_order, o.reference AS order_reference, od.product_id, od.product_attribute_id, od.product_quantity, o.current_state FROM [PREFIX]_order_detail od JOIN [PREFIX]_orders o ON od.id_order = o.id_orderWHERE od.product_id = 123 AND o.current_state IN (1, 2, 3)[/code]

Notes:

  • current_state refers to the state of the order. You can check which states correspond to "open" in the order_state table.

Learn more about the management of order states.

Manage order states

2. Total quantity ordered but not yet shipped for a product

If you want to check how many units of a product have been ordered but not yet shipped:

[code=language-sql] SELECT SUM(od.product_quantity - od.product_quantity_refunded - od.product_quantity_return) AS total_ordered_quantity FROM order_detail od JOIN orders o ON od.id_order = o.id_orderWHERE od.product_id = 123 AND o.current_state IN (1, 2, 3)[/code]

Other useful examples

1. Compare physical and available quantity

You can check the data of physical and reserved quantities to see if there are any discrepancies:

[code=language-sql] SELECT sa.id_product, sa.id_product_attribute, sa.physical_quantity, sa.reserved_quantity, sa.quantity FROM [PREFIX]_stock_available sa WHERE sa.id_product = 123[/code]

2. List of products that can be ordered even if out of stock

To identify products with the "allow out-of-stock orders" option enabled:

[code=language-sql] SELECT id_product, id_product_attribute, quantity, out_of_stock FROM [PREFIX]_stock_available WHERE out_of_stock = 1[/code]

SUMMARY

  • The [PREFIX]_stock_available table is the hub for managing available, physical and reserved quantities.
  • Main queries allow you to monitor stock status, identify out-of-stock products, and analyze current orders.
  • Related tables such as [PREFIX]_order_detail and [PREFIX]_orders provide details on open orders and reserved quantities.

Insights:

Author: Loris Modena

Loris Modena

SENIOR DEVELOPER

Per Ind Loris Modena owner of Arte e Informatica, began working in the computer industry in 1989 as a systems engineer in charge of maintenance and installation of computer systems. He starts programming for the web in 1997 dealing with CGI programming in PERL and later moving to programming in PHP and JavaScript. During this period he approaches the Open source world and Linux server management.

Product added to wishlist