Jump to content

stock_available - multiple records for same product_id


Jeff A

Recommended Posts

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 by Jeff A
incomplete initial posting (see edit history)
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 5 months later...

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

Link to comment
Share on other sites

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. :(

Link to comment
Share on other sites

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: 

 

 

Annotation 2020-08-10 094050.png

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