why the hassle of using joins like those ones ?
If you're not fond of searching through left joins, and you still want your
whole dataset "consistent", and a search "a-la-fulltext".....better do
something like this
whole_set = (
(db.person.id == db.clothing_person.person_id) &
(db.clothing.id == db.clothing_person.clothing_id) &
(db.item_person.person_id == db.person.id) &
(db.item_person.item_id == db.item.id)
)
then, you can search it as
what_I_want = (
(db.person.name == 'Bob') &
....
(db.item.name == 'item1')
)
rows = db(whole_set)(what_I_want).select()
On Wednesday, January 29, 2014 6:29:10 AM UTC+1, Apple Mason wrote:
>
> I want to search across some many to many tables, but with certain
> conditions.
>
>
> db.define_table('person',
> Field('name', 'string'),
> Field('nickname', 'string'))
>
> db.define_table('clothing',
> Field('name', 'string'))
>
> db.define_table('item',
> Field('name', 'string'))
>
> db.define_table('item_person',
> Field('person_id', 'reference person'),
> Field('item_id', 'reference item'))
>
> db.define_table('clothing_person',
> Field('person_id', 'reference person'),
> Field('clothing_id', 'reference clothing'))
>
>
>
> How would I find all people who have the name 'Bob' or nickname 'Bobcat'
> AND have items called 'item1' and 'item2' AND have clothing 'clothing1' ?
>
> For example, these are valid results:
>
> Bob has item1, item2 and clothing1
> Bobcat has item1, item2 and clothing1
>
> Would I use a join for this? Maybe something like:
>
> db( (db.person.name.like('Bob')) |
> (db.person.name.like('Bobcat')).select(db.person.ALL, join=[
> db.item_person.on( (db.item.id==db.item_person.item_id)
> & ((db.item.name=='item1') & (db.item.name='item2'))),
> db.clothing_person.on(
> (db.clothing.id==db.clothing_person.clothing_id)
> & (db.clothing.name=='clothing1'))
> ])
>
> But that doesn't seem correct.
>
--
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/groups/opt_out.
