Yes, the problem is the one you mention. You need two left joins with
different aliases.
This should do it:
x=db.person.with_alias('x')
y=db.person.with_alias('y')
rows = db(db.likes.id>0).select(x.ALL,y.ALL,
left=[x.on(x.id==db.likes.person),
y.on(y.id==db.likes.endeared)])
for row in rows:
print row.x.name,'likes',row.y.name
On Jan 7, 8:59 pm, Christopher Helck <christopher.he...@gmail.com>
wrote:
> Perhaps this is a basic DB question: I want to model people who like other
> people. So I have a table of people and a table of relationships called
> 'likes'. The 'likes' table contains two columns: person, and endeared, both
> representing people.
>
> The problem comes when I want to join the two tables to create a human
> readable report listing the names of both people in the relationship. When I
> try this I get an empty result set. I suspect it's because the join
> references the same column twice. How do I do this the correct way?
>
> Sample code follows
>
> # Create tables
>
> db.define_table('person', Field('name'))
> db.define_table('likes', Field('person'), Field('endeared'))
>
> # Populate tables with data
>
> db.person.truncate()
> dick = db.person.insert(name='Dick')
> jane = db.person.insert(name='Jane')
> sally = db.person.insert(name='Sally')
>
> db.likes.truncate()
> db.likes.insert(person=dick, endeared=sally) # Dick likes Sally
> db.likes.insert(person=jane, endeared=sally) # Jane likes Sally
> db.likes.insert(person=sally, endeared=jane) # Sally likes Jane
>
> # Make sure tables are correct.
>
> print 'people', db().select(db.person.ALL)
> print 'likes', db().select(db.likes.ALL)
>
> # Try to create human readable report
>
> print 'pretty', db((db.likes.person==db.person.id)&(db.likes.endeared==
> db.person.id)).select()
>
> # If I just do one half of the query than I get half of what I want...
>
> print 'pretty', db((db.likes.person==db.person.id)).select()
>
> db.commit()
>
> Thanks,
> C. Helck
--
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To post to this group, send email to web...@googlegroups.com.
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en.
