stock_available - multiple records for same product_id

[Jeff A]

Hello,

Does anyone know if there is any documentation for the Prestashop database structure?

I see that the stock_available table contains multiple records for the same id_product.

This is unexpected - I see that the records have different id_product_attribute... so I checked the tables: product_attribute, product_attribute_combination, product_attribute_image and product_attribute_shop - but these do not contain any records - so I am unable to identify what the different id_product_attribute keys in stock_available mean.

I am simply trying to find the current stock available amounts.

Thanks for any pointers.

Edited February 9, 2020 by Jeff A
incomplete initial posting (see edit history)

[Jeff A]

This is the query that I want to execute:

    select 'Name','Location','Quantity','Reference','Excl','Incl' union all 
    select l.name, p.location, s.quantity, p.reference, round(p.price,2), round(p.price*1.20,2) 
      from product p 
        left join product_lang l using (id_product) 
        left join product_shop h using (id_product) 
        left join stock_available s using (id_product) 

the multiple records in stock_available are causing duplicate rows with differing quantity.

[Jeff A]

Does anyone know who is the pumpking for the data model or the design of the stock control model in Prestashop?

There appear to be significant issues with the design - perhaps I'm misunderstanding it... no documentation that I can track down...

[Jezza]

I too am interested to understand the stock_available table. Multiple entries and multiple stock quantities for a single product means that we have multiple different (correct & incorrect) stock values showing up in different places in the Presta Dashboard !! :S

[Jeff A]

I experimented and found that I needed to modify the ``JOIN`` - 

    left join stock_available s on (s.id_product=p.id_product and s.id_product_attribute=0)

Adding the additional qualification on ``id_product_attribute`` seems to return the correct s.quantity.

I don't think anyone is responsible to keep the data-model clean - one of the reasons that the software is so slow are the sub-optimal queries. My guess is that very few of the developers know about ``EXPLAIN`` or how to read the query-plans.

[Jezza]

Hi Jeff.

Thanks for sharing this info.

In fact for us id_product_attribute=0 is not the correct stock quantity.  For simple products without variations this is correct, but mostly we use variations and this is why we have the problem.

It looks to me that the correct id_product_attribute for products with variations is the last (i.e. highest number) with the same id_product.

Does that work for you? Can we do this in an override do you think?

I have raised it as bug BTW (with no response or feedback) here: 

 

 

[Jezza]

I found this bug which I believe covers this issue: https://github.com/PrestaShop/PrestaShop/issues/12814