running example: # fake table in which result of recursive select will be temporary stored # id-values will be inherited from parent_child table db.define_table('entry_collector', Field('child', 'integer'), Field('xpath', 'json'), # array of ids, xpath[0] == root, xpath[-1] == child Field('root', 'integer'), Field('xdepth', 'integer'), migrate_enabled = False, fake_migrate = True )
def with_recursive(parent, child, roots_select, q, *fields, **select_kwargs): """ parent, child - fields obj ( like db.parent_child.parent, db.parent_child.child ) roots_select - sql string (like 'select 123 as id' or db(db.person.id.belongs([11,22,33])._select(db.person.id)) q, fields, select_kwargs - args that will pass to dal: db(q).select(*fields, **select_kwargs) select_kwargs may include 'entry_collector' - name of fake table for recursive (default is 'entry_collector') returns a regular rows dal object (nothing new) """ entry_collector = select_kwargs.pop('entry_collector', 'entry_collector') args = Storage( entry = parent.table._tablename, parent = parent.name, child = child.name, entry_collector = entry_collector, roots = roots_select ) rec_sql_s = \ """ WITH RECURSIVE %(entry_collector)s(id, child, xpath, root, xdepth) AS (SELECT NULL, id, "[" || id || "]", id, 0 FROM (%(roots)s) UNION SELECT %(entry)s.id, %(entry)s.%(child)s, rtrim(xpath,"]") || "," || %(entry)s.%(child)s || "]", %(entry_collector)s.root, %(entry_collector)s.xdepth + 1 FROM %(entry_collector)s JOIN %(entry)s ON NOT instr(%(entry_collector)s.xpath, %(entry)s.%(parent)s || "," ) AND %(entry)s.%(parent)s = %(entry_collector)s.child ORDER BY 5 DESC /* means BY xdepth */ ) """ % args q = db(q) dal_select = q._db._adapter._select_aux def patch_select(*args, **kwargs): if args: is_recursive = False for fld in args[1]: if fld.table._tablename == 'entry_collector': is_recursive = True break if is_recursive: args = list(args) args[0] = rec_sql_s + args[0] print 'with rec: ', args[0] return dal_select(*args, **kwargs) q._db._adapter._select_aux = patch_select try: ret = q.select(*(fields + (db[entry_collector].id,)), **select_kwargs) finally: q._db._adapter._select_aux = dal_select return ret On Thursday, November 22, 2018 at 2:41:23 AM UTC+3, BigBaaadBob wrote: > > 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.