[Advanced] How would you do this? (return data quickly over 7 tables)

Hello, I have an advanced question. I might be doing this to difficult, hence me trying to see how someone else would solve this. I have several tables. First products:

products (id, name): 1 Magazine 2 Novel 3 Pencil Red 4 Coloringbook 5 Ruler 6 T-shirt 7 Pants

Next I have titles to group products (id, name) 1 Written material 2 Accessoires 3 Discount 4 Clothing 5 Men

Now I can add product to titles (many to many, via product_title): id, product_id, title_id. So far so good. I can easily see what product belongs to which title and the other way around.

Now I'd like to add product options. For instance gift_wrapping. I have table orderitem_options (id, name) 1 Gift wrapping 2 Personalize Name 3 Free Delivery

I'd like to assign these to products, but it is way too much to do this on a per product option->product base. So I am introducing productgroups (compareable to titles). Productgroups (id, name) 1 Expensive Items 2 Cheap Items 3 All Items but not pants

orderitem_options and productgroups are a 1 on 1 relation via orderitemoptions_productgroups

Now I want to add titles and/or product to this productgroups (or explicitly remove). So I have productgroup_title (id, productgroup_id, title_id) productgroup_product (id, productgroup_id, product_id, remove). Where Remove defines whether the product is allowed or explicitly not allowed. So Now I can add for instance to productgroup 3 everything without pants by doing productgroup_title (all product_groups): 1 3 1 1 3 2 1 3 3 1 3 4 1 3 5

And explicitly removing the pants by productgroup_product: 1 3 7 1

And now comes the difficult part: How can I quickly see if an orderitem_options is allowed for a particular product? I am doing this in a difficult way:

Get all the orderitem_options (table orderitem_options)
For each orderitem_options->get the productgroup (orderitemoptions_productgroups)
For the orderitemoptions_productgroups (productgroup_id)retrieve the products in 2 steps 3.1.1) through titles as-> Retrieve title_id from productgroup_title using (productgroup_id)is the one from step 2. 3.1.2) Retrieve all products for these titles @foreach Title->product_title->Product 3.2.1) through products as -> Retrieve all products to add via productgroup_product where remove = 0 3.2.2) Retrieve all products to add via productgroup_product where remove = 1
Combines all these product_id as: 3.1.1 + 3.2.1 - 3.2.2
Find if my product_id is in the resulting set from 4.

Does anyone have a more elegant solution? I think it can't be that I am the first one who wants to do something like this. I just can't figure out a more elegant way and this takes much time. Can't it be done in one long eloquent query? (Although I don't know how).

Thanks in advance (although I wonder if anyone will reply :D)

jlrdw

8 years ago

Level 75

Hire @tykus to write the app. Of course I have written pedigree programs, but not quickly: Like here is a progeny report on a dog.

DECENDANTS OF
SANDYLANDS BLAZE,Y,M    156/158
====================================
AROSCAS I'M QUEENS BLAZE, ,F   157/1952  
.    .AROSCAS BLAZE GLORY, ,F   1762/1951  
.    .    .AROSCAS JOY AND GLORY,B,F   2853/1946  
.    .    .    .ACCOLADE CASBARS CLASSIC,Y,M   3681/3682  
.    .    .    .    .BEECHCROFTS TRILLIUM,B,M   3674/3673  
.    .    .AROSCAS THE SWEDISH CONQUEROR,Y,M   1945/1946  
.    .    .    .WILLOWBROOK SNOW GOOSE HUNTER,Y,M   1944/3683  
.    .    .    .    .ACCOLADE CASBARS CLASSIC,Y,M   3681/3682  
SANDYLANDS BUSY LIZ,Y,F   157/160   
.    .SANDYLANDS ABBA,Y,F   86/159   
.    .    .SANDYLANDS SPECIAL SONG,Y,F   1860/1861  
.    .    .    .SANDYLANDS GENTRY,B,M   1857/1859  
.    .    .    .    .KIMVALLEY GEISHA GIRL AT WOLVERCOTE, ,M   3129/3382  
.    .    .    .SANDYLANDS OBSESSION,Y,F   691/1859  
.    .    .    .    .SANDYLANDS GUARDSMAN,B,M   1857/1858  
.    .    .SANDYLANDS SPECIAL VINTAGE, ,M   1860/1861  
.    .    .    .FOXRUSH STRAWBERRY FIZZ,Y,F   2317/2318  
.    .SANDYLANDS RIP VAN WINKLE,Y,M   151/159   
.    .    .ALBELARM AMBRFLD S HILL PRIDE, ,F   155/2816  
.    .    .    .SNOWDEN HILLS LIGHT N RIGHT, ,F   66/2815  
.    .    .    .    .SNOWDEN HILLS EBENEZER,B,M   439/2814  
.    .    .BELQUEST TO THE POINT,B,F   155/172   
.    .    .    .BELQUEST POINT WELL MADE,B,F   2968/359   
.    .    .    .BELQUEST TO THE POINT N BACK,Y,M   2923/359   
.    .    .    .BELQUEST TUC-N-ROLL KEEPSAKE,B,M   78/359   
.    .    .BLACKWING BALLON JUMPER,Y,M   155/1694  
.    .    .CHILBROOK SOLITAIRE,B,F   155/172   
.    .    .    .CHILBROOK KEEPSAKE MARCUS,B,M   217/290   
.    .    .    .CHILBROOK LEGACY,B,F   1926/290   
.    .    .    .    .CHILBROOK BLACK BRITANNIA,B,M   719/1925  
.    .    .    .CHILBROOK MANHATTAN,B,M   217/290   
.    .    .    .CHILBROOK MISS CHATELAINE,C,F   217/290   
.    .    .CIRQUES CURTAIN CALL, ,F   155/1971  
.    .    .    .BOROWICK CEDAR HILL FANFARE, ,F   741/1968  
.    .    .    .    .ACREWOODS DUE ME JUSTICE,Y,M   2/2316  
.    .    .    .BOROWICK CEDAR HILL NIGHTCAP, ,M   741/1968  
.    .    .    .    .BOROWICKS QUEST,Y,M   1966/1967  
.    .    .MUSKELUNGES ONE OF A KIND,Y,F   155/2353  
.    .    .RICKWAYS TUSCAROURA, ,F   155/2659  
.    .    .    .BREEZYS CYCLONE,Y,M   1935/2658  
.    .    .    .    .VISIONS BLUSH OF HYGATE, ,M   2657/1600  
.    .    .SALTMEADOW BLACKWATCH,B,M   155/1448  
.    .    .    .COLTONS AGAINST THE WIND,B,M   1447/1988  
.    .    .    .SALTMEADOW MOTOWN, ,M   1447/3563  
.    .    .    .    .BAYVIEW  REET PETITE, ,M   3407/3561  
.    .    .U KNOW WHO OF TINTAGEL WINDS, ,F   155/1961  
.    .    .    .SWEETTREES MAKE MY DAY, ,F   427/1960  
.    .    .    .    .SWEETTREES PATRIOT,B,M   1954/1955  
.    .    .VISIONS LADY ALEXI, ,F   155/1603  
.    .    .    .AUTUMNS THUNDER VISIONS,Y,M   1599/1600  
.    .    .    .    .BORADORS SWEET AUTUMN MIST,Y,M   1598/2362  
.    .    .    .VISIONS BLUSH OF HYGATE, ,F   2657/1600  
.    .    .    .    .BEECHCROFT STONECREST BO SUN,Y,M   2652/2541  
.    .    .    .    .VISIONS STATE OF THE ART,B,M   439/2541  
.    .SANDYLANDS STEPTOE,Y,M   234/159   
.    .SANDYLANDS STORMY WEATHER, ,M   234/159   
.    .    .ELYSIUMS THUNDERSTORM, ,M   264/233   
.    .    .    .MARSHLAND PAISLEY BROONE,Y,F   228/229   
.    .    .    .    .MARSHLAND BLITZ,B,M   164/227   
.    .    .    .    .MARSHLAND MEGAN, ,M   3351/227   
SOMERSET CIDER OF KIMVALLEY, ,M   157/503   
.    .BEAVERS LAVINIA OF MOORWOOD,B,F   501/443   
.    .    .MOORWOOD JEWEL, ,F   220/200   
.    .    .    .DICKENDALLS RUFFY,B,M   9/441   
.    .    .    .    .BROAD REACH BOCEPHUS,B,M   440/466   
.    .    .    .    .BROAD REACH JASMINE,Y,M   440/2943  
.    .    .    .    .DICKENDALL ARNOLD,B,M   440/424   
.    .    .    .    .LOBUFFS BARE NECESSITIES,B,M   440/1846  
.    .    .    .    .MIJANS HOT STUFF,B,M   440/1089  
.    .    .    .    .SECOND SIGHT SMARTY PANTS,B,M   440/1846  
.    .    .MOORWOODS MADRIGAL, ,F   220/200   
.    .    .    .DICKENDALL MOORWOOD TOKEN,Y,F   427/428   
.    .    .    .    .BUCKSTONE REVEILLE, ,M   439/425   
.    .    .    .    .DICKENDALL MOORWOOD TIGER,Y,M   9/425   
.    .    .    .DICKENDALLS RAZ-MA-TAZ,B,F   427/428

I'd say take a deep breath and take the months and years to learn this stuff.

A helpful tip I did Enterprise Java Logistics application and I reduced as much as possible everything into a series of one to many, just makes things easier.

JacDev

8 years ago

Level 1

Thanks, but this was not what I was asking for. I already have an application which is very extensive. I was only asking for someone to share their toughts, I was not looking to hire a programmer or something like that. Just someone to share their thoughts on my question.

Please or to participate in this conversation.