Orphaned Product Variants in DB?
Posted: Tue Nov 10, 2009 9:39 pm
Good evening - I am helping a client develop some inventory reports using the data in their Able store, version information as follows:
PLATFORM: ASP.NET
VERSION: 7.0.3
BUILD: 12458
MSSQL v2005
AC SCHEMA v2000
I am attempting to create a report that shows their current inventory by querying ac_products and ac_productVariants, and pulling in labels as needed from the option tables.
I am running across a lot of records in the ac_productVariants table that appear to be orphaned or otherwise invalid. For instance, if there are 10 possible variants for a product, there will be 20 rows in ac_productVariants tied to the product via the productID. When I query the DB directly, I get all 20 records, and cannot discern which are actually valid, other than the fact that the invalid records generally appear first. When I view the product 'Variants' page through the administration, I only see the valid records.
The invalid records sometimes show an 'inStock' value of 0, and sometimes have a value of 1 or more.
Here is a sample of what I am seeing for a product with the ID of 505. You can see the first and second groups of records flip-flop the value of the option1 and option2 columns, otherwise I can see no difference. The variants with an ID of 2934 and greater show in the administration, those before it do not.
ProductVariantId ProductId Option1 Option2 Option3 Option4 Option5 Option6 Option7 Option8 VariantName Sku Price PriceModeId Weight WeightModeId CostOfGoods InStock InStockWarningLevel Available
2389 505 3438 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2390 505 3439 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2391 505 3440 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2393 505 3436 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 0 0 True
2395 505 3445 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2397 505 3442 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2398 505 3444 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2399 505 3449 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2401 505 3441 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2402 505 3446 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2403 505 3447 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2404 505 3448 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2405 505 3450 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2406 505 3451 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2934 505 3432 3437 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2935 505 3432 3438 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 2 0 True
2936 505 3432 3439 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 2 0 True
2937 505 3432 3440 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2938 505 3432 3442 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2939 505 3432 3443 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 2 0 True
2940 505 3432 3444 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2941 505 3432 3445 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2942 505 3432 3446 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2943 505 3432 3447 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2944 505 3432 3448 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2945 505 3432 3450 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2946 505 3432 3451 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
3278 505 3432 3441 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 2 0 True
3279 505 3432 3449 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
I am not familiar with how the queries are structured in Able, but my question this: Is this expected behavior? If so, any suggestions on how to structure a SQL query to avoid the invalid records? (I say invalid as they don't show in the administration section). If not, is there a suggested fix?
Thanks in advance, as this has been driving me quite mad.
PLATFORM: ASP.NET
VERSION: 7.0.3
BUILD: 12458
MSSQL v2005
AC SCHEMA v2000
I am attempting to create a report that shows their current inventory by querying ac_products and ac_productVariants, and pulling in labels as needed from the option tables.
I am running across a lot of records in the ac_productVariants table that appear to be orphaned or otherwise invalid. For instance, if there are 10 possible variants for a product, there will be 20 rows in ac_productVariants tied to the product via the productID. When I query the DB directly, I get all 20 records, and cannot discern which are actually valid, other than the fact that the invalid records generally appear first. When I view the product 'Variants' page through the administration, I only see the valid records.
The invalid records sometimes show an 'inStock' value of 0, and sometimes have a value of 1 or more.
Here is a sample of what I am seeing for a product with the ID of 505. You can see the first and second groups of records flip-flop the value of the option1 and option2 columns, otherwise I can see no difference. The variants with an ID of 2934 and greater show in the administration, those before it do not.
ProductVariantId ProductId Option1 Option2 Option3 Option4 Option5 Option6 Option7 Option8 VariantName Sku Price PriceModeId Weight WeightModeId CostOfGoods InStock InStockWarningLevel Available
2389 505 3438 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2390 505 3439 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2391 505 3440 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2393 505 3436 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 0 0 True
2395 505 3445 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2397 505 3442 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2398 505 3444 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2399 505 3449 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2401 505 3441 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2402 505 3446 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2403 505 3447 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2404 505 3448 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2405 505 3450 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2406 505 3451 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2934 505 3432 3437 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2935 505 3432 3438 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 2 0 True
2936 505 3432 3439 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 2 0 True
2937 505 3432 3440 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2938 505 3432 3442 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2939 505 3432 3443 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 2 0 True
2940 505 3432 3444 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2941 505 3432 3445 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2942 505 3432 3446 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2943 505 3432 3447 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2944 505 3432 3448 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2945 505 3432 3450 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2946 505 3432 3451 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
3278 505 3432 3441 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 2 0 True
3279 505 3432 3449 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
I am not familiar with how the queries are structured in Able, but my question this: Is this expected behavior? If so, any suggestions on how to structure a SQL query to avoid the invalid records? (I say invalid as they don't show in the administration section). If not, is there a suggested fix?
Thanks in advance, as this has been driving me quite mad.