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.

Reply via email to