It can be done by joining on the aliased child table in relational fashion:
>>> db.define_table('p', Field('name'))
>>> db.define_table('c', Field('name'), Field('p','reference p'),
Field('value'))
>>> c_alias=db.c.with_alias('c_alias')
>>> db.p.insert(name='uno')
>>> db.p.insert(name='due')
>>> db.c.insert(name='c1', value='DVM', p=1)
>>> db.c.insert(name='c2', value='DVM', p=2)
>>> db.c.insert(name='c3', value='Suggestion', p=1)
>>> db.c.insert(name='c4', value='DVM', p=2)
>>> print db((db.c.value=='DVM') & (db.c_alias.value=='Suggestion') &
(db.c.p == db.c_alias.p)).select()
c.id,c.name,c.p,c.value,c_alias.id,c_alias.name,c_alias.p,c_alias.value
1,c1,1,DVM,3,c3,1,Suggestion
2014-12-26 11:23 GMT+01:00 Massimo Di Pierro <[email protected]>:
> Given this example:
>
> In [1]: db = DAL()
> In [2]: db.define_table('rel',Field('name'))
> In [3]: db.define_table('rol',Field('name'),Field('parent','reference
> rel'),Field('value'))
> In [4]: db.rel.insert(name="Max")
> In [5]: db.rel.insert(name="John")
> In [6]: db.rol.insert(name="Child1", parent=1, value="DMV")
> In [7]: db.rol.insert(name="Child2", parent=1, value="Suggestion")
> In [8]: db.rol.insert(name="Child3", parent=2, value="DMV")
> In [9]: db.rol.insert(name="Child4", parent=2, value="DMV")
>
> You can do:
>
> In [10]: rows =
> db(db.rel.id.belongs(db(db.rol.value=='DMV')._select(db.rol.parent)))
> (db.rel.id.belongs(db(db.rol.value=='Suggestion')._select(db.rol.parent))).select()
>
> It will select Max but not John.
>
>
> On Friday, 26 December 2014 01:39:12 UTC-6, Alex Glaros wrote:
>>
>> I have a parent (Relationship) table and a child (Role) table.
>>
>> Some of the child (Role) records contain the value "DMV", and other child
>> (Role) records contain the value "Suggestion".
>>
>> I want to find a parent (Relationship) table that has Role children that
>> contain both of the above values. In other words, Relationship has both a
>> child that has "DMV", and another child that has the value "Suggestion".
>>
>> The problem is that I can't use the "or" operator because both children
>> have to exist with the same parent. I can't use the "&" operator because
>> the values are mutually exclusive in the same child record.
>>
>> To re-describe the question, here is an example of how it might be solved
>> using pseudo code.
>>
>> FirstPass=db(Relation.id == Role.relationId) & (Role.value=="DMV")
>> SecondPass=db(Relation.id == Role.relationId) & (Role.value=="Suggestion")
>> ThirdPass=(FirstPass.relationId==SecondPass.relationID)
>>
>> Now in ThirdPass I have only the Relation records that have both "DMV"
>> and "Suggestion" Role children.
>>
>> Thanks,
>>
>> Alex Glaros
>>
> --
> 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 [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
