CODEWITHSUNDEEP
pythonweb2py
Pekka
Pekka

Reputation: 2448

Create alias referencing to a part of the table

Consider the following database structure:

from gluon import DAL, Field

db = DAL('sqlite:memory:') 

db.define_table('party',
    Field('place')
)

db.define_table('participant',
    Field('party_id', 'reference party'),
    Field('name'),
    Field('role')
)    

db.party.insert(place="Jane's home")

db.participant.insert(name='Jane',party_id=1,role='host')
db.participant.insert(name='George',party_id=1,role='organizer')
db.participant.insert(name='John',party_id=1,role='guest')
db.participant.insert(name='Mary',party_id=1,role='guest')

Now. I would like to make a selection where I select each party with it's host and organizer.

I can do it like this:

host = db.participant.with_alias('host')
organizer = db.participant.with_alias('organizer')

rows = db().select(
        db.party.place,
        host.name,
        organizer.name,
    left=(
        host.on((host.party_id==db.party.id) & (host.role=='host')),
        organizer.on((organizer.party_id==db.party.id) & (organizer.role=='organizer'))
    )
)

for row in rows:
    print [row.party.place, row.host.name, row.organizer.name]

But I think this is not the best way. In particular this sentence:

& (host.role=='host')

Sounds pretty ridiculous. Furthermore, maybe next time I have to select all hosts. Then I have to be including the above condition everywhere.

Is there a way to define the host alias so that i get only the persons who are hosts?

I am looking for some solution like:

create view host as select * from participant where role = 'host'

Also any other ideas to make the last selection prettier are welcome!

Upvotes: 1

Views: 213

Answers (1)

randomusername
randomusername

Reputation: 8105

I was just looking through pydoc gluon.DAL and I found that you can do this:

db.executesql("CREATE VIEW host AS SELECT * FROM participant WHERE role = 'host'")

Upvotes: 1

Related Questions