When you do a join, the DAL returns rows the same way the SQL database
returns them. Sounds like you're looking for more of a nested structure,
which would have to be created in Python. web2py doesn't do that out of the
box with joins, but you can get similar functionality using recursive
selects, albeit less efficiently (in this case, it would require two
additional database selects per person, though without requiring any joins):
persons = db(db.person).select()
for person in persons:
pets = person.pet.select()
things = person.thing.select()
person.pet is a Set object defining the set of db.pet records with
db.pet.person_id == person.id. You can then call .select() to retrieve
those records (optionally passing any of the usual arguments to .select()).
Anthony
On Friday, January 24, 2014 5:13:45 PM UTC-5, Apple Mason wrote:
>
> Is there a way to get all the information returned as one row, with the
> focus on a particular person? For example,
>
> <Row {'pet': [{''name': 'Pet1'}, {''name':'Pet2'}], 'person': {'name':
> 'Bob', 'id': 1L}, 'thing': [{'name': 'thing1'}, {'name':'thing2'}]}>
>
> ... Or something similar? This would give me a single row that I can
> iterate through his things or pets if I need to. For example, to print out
> people's information:
>
> for person in persons:
> things = .....
> pets = .......
>
> print "%s %s" % (things, pets)
>
>
>
> Right now the code I have is:
>
> a = db( db.person.id==1).select(db.person.ALL, db.pet.ALL, db.
> thing.ALL,
> left=[db.pet.on(db.person.id==db.pet.person_id),
> db.thing.on(db.person.id==db.thing.person_id)])
>
>
> When I print this out, I get a combination of the same thing, which is a
> mess:
>
>
> <Row {'pet': {'person_id': 1L, 'id': 1L, 'name': 'Pet1'}, 'person': {
> 'name': 'Bob', 'id': 1L}, 'thing': {'person_id': 1L, 'id': 1L, 'name':
> 'thing1'}}>
> <Row {'pet': {'person_id': 1L, 'id': 1L, 'name': 'Pet1'}, 'person': {
> 'name': 'Bob', 'id': 1L}, 'thing': {'person_id': 1L, 'id': 2L, 'name':
> 'thing2'}}>
> <Row {'pet': {'person_id': 1L, 'id': 2L, 'name': 'Pet2'}, 'person': {
> 'name': 'Bob', 'id': 1L}, 'thing': {'person_id': 1L, 'id': 1L, 'name':
> 'thing1'}}>
> <Row {'pet': {'person_id': 1L, 'id': 2L, 'name': 'Pet2'}, 'person': {
> 'name': 'Bob', 'id': 1L}, 'thing': {'person_id': 1L, 'id': 2L, 'name':
> 'thing2'}}>
> <Row {'pet': {'person_id': 1L, 'id': 3L, 'name': 'pet3'}, 'person': {
> 'name': 'Bob', 'id': 1L}, 'thing': {'person_id': 1L, 'id': 1L, 'name':
> 'thing1'}}>
> <Row {'pet': {'person_id': 1L, 'id': 3L, 'name': 'pet3'}, 'person': {
> 'name': 'Bob', 'id': 1L}, 'thing': {'person_id': 1L, 'id': 2L, 'name':
> 'thing2'}}>
>
>
>
>
>
>
> On Wednesday, January 22, 2014 4:40:31 PM UTC-5, Niphlod wrote:
>>
>> I'm guessing you had it wrong....
>>
>> db(main_table.filter == something).select(
>> main_table, other_table,
>> left=[other_table.on(main_table.some_id == other_table.reference),
>> ....
>> ]
>> )
>>
>>
>>
>> On Wednesday, January 22, 2014 10:24:14 PM UTC+1, Apple Mason wrote:
>>>
>>>
>>> I have the following tables:
>>>
>>>
>>> db.define_table('person',
>>> Field('name', 'string'))
>>>
>>> db.define_table('pet',
>>> Field('name', 'string'),
>>> Field('person_id', 'reference person'))
>>>
>>> db.define_table('thing',
>>> Field('name', 'string'),
>>> Field('person_id', 'reference person'))
>>>
>>> I have a person_id given to me, so I want all information about that
>>> person (person's name, all pets, all things).
>>>
>>> I am trying left join, but I am not getting it quite right:
>>>
>>> person = db(db.person.id==person_id).select(db.person.ALL, db.pet.ALL,
>>> db.thing.ALL,
>>>
>>> left=[db.person.on(db.person.id==db.pet.person_id),
>>>
>>>
>>> db.person.on(db.person.id==db.thing.person_id)])
>>>
>>> I get an error:
>>>
>>> OperationalError: ambiguous column name: person.id
>>>
>>
--
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/groups/opt_out.
