hi all,
i'm trying to use mappers to set up some nested objects in a many to
many to many relationship.
i have three tables, account, contact, and role, with a map table
account_contact_role, with the primary keys of the first three tables.
i'd like to setup some mappers so i can use code like this:
<code>
account = session.query(Account).get(<account_id>)
for contact in account.contacts:
for role in contact.roles:
print role.name
</code>
the sql I want a account.contacts[<index>].roles attribute to represent:
SELECT role.id, role.name
FROM role
INNER JOIN account_contact_role AS acr
ON acr.role_id = role.id
WHERE acr.contact_id = :contact_id
AND acr.account_id = :account_id
where account_id and contact_id are inferred from the parent account
and contact objects.
attached is a file with my failed attempts so far, and a pastebin here:
http://pastebin.ca/102958
is this possible at the mapper level? or will i need to use the
account and contact id's in code higher up the stack to get the role
objects?
thanks in advance.
justin
import sys
from sqlalchemy import *
engine = create_engine('sqlite:///many2many.db', echo=True)
meta = BoundMetaData(engine)
account = Table('account', meta,
Column('id', Integer, primary_key=True),
Column('name', String))
contact = Table('contact', meta,
Column('id', Integer, primary_key=True),
Column('name', String))
role = Table('role', meta,
Column('id', Integer, primary_key=True),
Column('name', String))
account_contact_role = Table('account_contact_role', meta,
Column('account_id', Integer, ForeignKey(account.c.id)),
Column('contact_id', Integer, ForeignKey(contact.c.id)),
Column('role_id', Integer, ForeignKey(role.c.id)))
try:
account.drop()
contact.drop()
role.drop()
account_contact_role.drop()
except:
pass
account.create()
contact.create()
role.create()
account_contact_role.create()
account.insert().execute(
{'id':1, 'name':'acme co'},
{'id':2, 'name':'widget co'},)
contact.insert().execute(
{'id':1, 'name':'jane doe'},
{'id':2, 'name':'john doe'},
{'id':3, 'name':'johnoe'},)
role.insert().execute(
{'id':1, 'name':'sales'},
{'id':2, 'name':'technical'},
{'id':3, 'name':'billing'},)
account_contact_role.insert().execute(
{'account_id':1, 'contact_id':1, 'role_id':1},
{'account_id':1, 'contact_id':1, 'role_id':2},
{'account_id':1, 'contact_id':1, 'role_id':3},
{'account_id':1, 'contact_id':2, 'role_id':1},
{'account_id':1, 'contact_id':2, 'role_id':3},
{'account_id':1, 'contact_id':3, 'role_id':1},
{'account_id':1, 'contact_id':3, 'role_id':2},
{'account_id':1, 'contact_id':3, 'role_id':3},
{'account_id':2, 'contact_id':2, 'role_id':1},
{'account_id':2, 'contact_id':2, 'role_id':3},
{'account_id':2, 'contact_id':3, 'role_id':2},
{'account_id':2, 'contact_id':3, 'role_id':3},)
"""
I'd like to have code like this:
<code>
account = session.query(Account).get(<account_id>)
for contact in account.contacts:
for role in contact.roles:
print role.name
</code>
The sql I want a nested roles attribute to represent:
SELECT role.id, role.name
FROM role
INNER JOIN account_contact_role AS acr
ON acr.role_id = role.id
WHERE acr.contact_id = :contact_id
AND acr.account_id = :account_id
Where account_id and contact_id are inferred from the parent account and contact objects.
"""
class Account(object):
def blah(self):
pass
class Contact(object):
def getRoles(self, account_id):
pass
class Role(object):
pass
class AccountContactRole(object):
pass
class AccountContact(object):
def __init__(self, name=None):
self.name = name
mapper(Role, role)
mapper(Account, account,
properties = {
'contacts': relation(AccountContact),
},
)
mapper(Contact, contact,
properties = {
'roles': relation(AccountContact, lazy=True, association=AccountContactRole,
cascade='all, delete-orphan'),
}
)
mapper(AccountContact, account_contact_role,
primary_key = [account_contact_role.c.contact_id,
account_contact_role.c.account_id,],
properties = {
'contact': relation(Contact),
'role': relation(Role),
},
)
mapper(AccountContactRole, account_contact_role,
primary_key = [account_contact_role.c.account_id,
account_contact_role.c.contact_id,
account_contact_role.c.role_id,],
)
session = create_session()
a = session.query(Account).get(1)
for contact in a.contacts:
print "contact name %s" % contact.contact.name
for role in contact.contact.roles:
print "rid %d cid %d aid %d" % (role.role_id, role.contact_id, role.account_id)
print role.role.name
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
