How do I make directed relationship?

[Christopher Helck]

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

[mdipierro]

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: