The use case is manufacturing. Large complicated manufacturing with special requirements. And SAP need not apply... :-)
On Wednesday, November 21, 2018 at 1:26:56 PM UTC-8, Dave S wrote: > > > > On Wednesday, November 21, 2018 at 10:33:13 AM UTC-8, BigBaaadBob wrote: >> >> I'm just trying to find a good solid way of doing the BOM pattern using >> the DAL, and pretty much all of the decent articles I've found say the >> Closure Table method is the best trade-off, especially for large-ish and >> deep-ish BOM structures. >> > > It would be interesting to hear your use case. Are you into a scheduling > problem like the airport/flight example? Or an organizational example > where you need to quickly find the director in the hierarchy above one us > grunts? > > >> But, I'm not dogmatic. How would you code up a version using "with >> recursive" queries using the DAL? If you post a running example it would be >> great at informing the group! >> >> On Wednesday, November 21, 2018 at 9:56:48 AM UTC-8, Val K wrote: >>> >>> Why do you have to use this crutches (despite they are genius)? Now, >>> even Sqlite3 supports 'with recursive' queries. >>> And what do you mean under BOM and large tree? If we are talking about >>> BOM of real (physical) object like a car or even an aircraft carrier, I >>> think it is not large tree >>> only if you don't want to have BOAOM (bill of atoms of materials) >>> >>> > My BOM experience is more with circuit boards, and there would probably a > dozen part numbers for resistors and and a dozen part numbers for > capacitors, and more than a dozen ICs. But there could be a dozen or a > hundred boards using part X, and if you need to figure out which boards are > affected when the manufacturer stops manuffing the part, it starts getting > interesting. If you also make boxes the boards go into, then the hierarchy > gains another level (although not many entries at that level). > > > >> On Wednesday, November 21, 2018 at 7:58:48 PM UTC+3, BigBaaadBob wrote: >>>> >>>> I went ahead and coded something up, inspired by Massimo's Preorder >>>> Traversal example. I wouldn't be offended if people suggest how to make it >>>> better/faster, perhaps by combining stuff in the Link function into one >>>> query instead of many. >>>> >>>> # Demonstrate closure tables. Deletion of nodes is left as an exercise >>>> to the reader. >>>> # See: >>>> http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html >>>> >>>> >>>> from gluon import DAL, Field >>>> >>>> db=DAL('sqlite://closure.db') >>>> >>>> db.define_table( >>>> 'thing', >>>> db.Field('name') >>>> ) >>>> db.thing.truncate() >>>> >>>> db.define_table( >>>> 'closure', >>>> db.Field('parent', type='reference thing'), >>>> db.Field('child', type='reference thing'), >>>> db.Field('depth', type='integer') >>>> ) >>>> db.closure.truncate() >>>> >>>> def link(parent_id,child_id): >>>> """ link(1,3) """ >>>> p = db.closure.with_alias('p') >>>> c = db.closure.with_alias('c') >>>> rows = db((p.child==parent_id) & (c.parent==child_id)).select( >>>> p.parent.with_alias('parent'), >>>> c.child.with_alias('child'), >>>> (p.depth+c.depth+1).with_alias('depth')) >>>> for row in rows: >>>> db.closure.insert(parent=row.parent, child=row.child, >>>> depth=row.depth) >>>> >>>> def add_node(name,parent_name): >>>> """ add_node('Fruit','Food') """ >>>> child_id=db.thing.insert(name=name) >>>> db.closure.insert(parent=child_id, child=child_id, depth=0) >>>> if parent_name is not None: >>>> parent_id=db(db.thing.name==parent_name).select().first().id >>>> link(parent_id, child_id) >>>> >>>> def ancestors(name): >>>> """ print ancestors('Red')""" >>>> node=db(db.thing.name==name).select().first() >>>> return db((db.closure.child==node.id) & (db.closure.parent != >>>> node.id)).select( >>>> db.thing.name, left=db.thing.on(db.thing.id==db.closure.parent), >>>> orderby=db.closure.depth) >>>> >>>> def descendants(name): >>>> """ print descendants('Fruit')""" >>>> node=db(db.thing.name==name).select().first() >>>> return db((db.closure.parent==node.id) & (db.closure.child != >>>> node.id)).select( >>>> db.thing.name, left=db.thing.on(db.thing.id==db.closure.child), >>>> orderby=db.closure.depth) >>>> >>>> def closure(): >>>> """ print closure() """ >>>> parent = db.thing.with_alias('parent') >>>> child = db.thing.with_alias('child') >>>> return db().select(db.closure.id, parent.name, child.name, >>>> db.closure.depth, >>>> left=(parent.on(parent.id == db.closure.parent), >>>> child.on(child.id == db.closure.child))) >>>> >>>> def test(): >>>> add_node('Food',None) >>>> db.commit() >>>> print closure() >>>> >>>> add_node('Vehicle',None) >>>> db.commit() >>>> print closure() >>>> >>>> add_node('Fruit','Food') >>>> db.commit() >>>> print closure() >>>> >>>> add_node('Meat','Food') >>>> db.commit() >>>> print closure() >>>> >>>> add_node('Red','Fruit') >>>> db.commit() >>>> print closure() >>>> >>>> add_node('Chevy','Vehicle') >>>> db.commit() >>>> print closure() >>>> >>>> print "descendants of 'Food'" >>>> print descendants('Food') >>>> >>>> print "ancestors of 'Red'" >>>> print ancestors('Red') >>>> >>>> test() >>>> >>>> >>>> >>>> On Tuesday, November 20, 2018 at 5:02:33 PM UTC-8, BigBaaadBob wrote: >>>>> >>>>> Has anyone implemented a closure table with triggers >>>>> <http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html> >>>>> approach >>>>> to hierarchy (specifically for a Bill of Materials (BOM) pattern) in >>>>> Web2Py's DAL? >>>>> >>>>> I've seen Massimo's implementation of Preorder Traversal which doesn't >>>>> work for BOM patterns where there are multiple roots. The Adjacency Table >>>>> method is slow for large trees. >>>>> >>>>> In a Bill of Materials situation >>>>> <http://www.vertabelo.com/blog/technical-articles/identifying-the-bill-of-materials-bom-structure-in-databases>, >>>>> >>>>> there are multiple roots in the main table, like this: >>>>> >>>>> db.define_table('item', >>>>> Field('name', type='string', length=128, label=T('Name'))) >>>>> >>>>> db.define_table('bill_of_materials', >>>>> Field('parent_item_id', type='reference item', notnull=True, >>>>> label=T('Parent Item')), >>>>> Field('child_item_id', type='reference item', notnull=True, >>>>> label=T('Child Item')), >>>>> Field('quantity', type='decimal(8,2)', default='1.0', >>>>> label=T('Quantity'))) >>>>> >>>>> >>>>> > Interesting reading. > > /dps > > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
