Turns out I had to make some change but I got this to work:
db.define_table('person',Field('name'))
db.define_table('product',Field('name'))
db.define_table('purchase',Field('person','reference person'),Field(
'product','reference product'))
rows = db(db.person).select().join(db.purchase.person, # the thing you want
to join by
constraint=(db.purchase.product==db.product.id), # the many2many
fields=[db.product.id, db.product.name],
# the fields you want
orderby=db.product.name, # the order
name='purchases', # how you want to call
it
)
print(rows.as_json())
"""
[{"name": "Max", "purchases": [{"id": 1, "name": "Book"}, {"id": 3, "name":
"Jewel"}], "id": 1}, {"name": "Tim", "purchases": [{"id": 2, "name":
"Bike"}], "id": 2}, {"name": "Jim", "purchases": [{"id": 1, "name":
"Book"}, {"id": 3, "name": "Jewel"}], "id": 3}, {"name": "Max",
"purchases": [], "id": 4}, {"name": "Tim", "purchases": [], "id": 5},
{"name": "Jim", "purchases": [], "id": 6}]
"""
On Tuesday, 5 July 2016 14:14:07 UTC-5, Carlos Cesar Caballero wrote:
>
> Massimo, that's great!!
>
> Could work with multiple levels of relations? Giving something like:
>
> {"owner": {"id": 1, "name": "Max", "owner_of_owner":{"id": 1, "data": "the
> data"}}, "id": 1, "name": "Max's Chair"}
>
> Or can be used to build that (
> https://groups.google.com/d/topic/web2py/89byxw7BYwc/discussion)
> functionality? (Maybe I can build it now using your code as base, with
> other name as Anthony suggests)
>
> Greetings.
>
> El 05/07/16 a las 14:20, Massimo Di Pierro escribió:
>
> db = DAL()
>
> db.define_table('person',Field('name'))
>
> db.define_table('thing',Field('name'),Field('owner','reference person'))
>
>
> for name in ('Max','Tim','Jim'):
>
> i = db.person.insert(name=name)
>
> for thing in ('Chair','Table','Bike'):
>
> db.thing.insert(owner=i, name=name+"'s "+thing)
>
>
> rows = db(db.thing).select().join(db.person.id)
>
> print(rows.as_json())
>
>
> """
>
>
>
> [{"owner": {"id": 1, "name": "Max"}, "id": 1, "name": "Max's Chair"},
>
>
>
> {"owner": {"id": 1, "name": "Max"}, "id": 2, "name": "Max's Table"},
>
>
>
> {"owner": {"id": 1, "name": "Max"}, "id": 3, "name": "Max's Bike"},
>
>
>
> {"owner": {"id": 2, "name": "Tim"}, "id": 4, "name": "Tim's Chair"},
>
>
>
> {"owner": {"id": 2, "name": "Tim"}, "id": 5, "name": "Tim's Table"},
>
>
>
> {"owner": {"id": 2, "name": "Tim"}, "id": 6, "name": "Tim's Bike"},
>
>
>
> {"owner": {"id": 3, "name": "Jim"}, "id": 7, "name": "Jim's Chair"},
>
>
>
> {"owner": {"id": 3, "name": "Jim"}, "id": 8, "name": "Jim's Table"},
>
>
>
> {"owner": {"id": 3, "name": "Jim"}, "id": 9, "name": "Jim's Bike"}]
>
>
>
> """
>
>
>
> rows = db(db.person).select().join(db.thing.owner, name="owns", fields=[
> db.thing.id, db.thing.name])
>
> print(rows.as_json())
>
>
> """
>
>
>
> [{"id": 1, "name": "Max", "owns": [
>
>
>
> {"id": 1, "name": "Max's Chair"},
>
>
>
> {"id": 2, "name": "Max's Table"},
>
>
>
> {"id": 3, "name": "Max's Bike"}]},
>
>
>
> {"id": 2, "name": "Tim", "owns": [
>
>
>
> {"id": 4, "name": "Tim's Chair"},
>
>
>
> {"id": 5, "name": "Tim's Table"},
>
>
>
> {"id": 6, "name": "Tim's Bike"}]},
>
>
>
> {"id": 3, "name": "Jim", "owns": [
>
>
>
> {"id": 7, "name": "Jim's Chair"},
>
>
>
> {"id": 8, "name": "Jim's Table"},
>
>
>
> {"id": 9, "name": "Jim's Bike"}]}
>
>
>
> ]
>
>
>
> """
>
>
> this is designed to be efficient and work on GAE too as long as rows is
> not too long.
> --
> 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.
