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.
