I'm using SQLAlchemy 0.5.6, Python 2.5.4, Debian Linux
I have a defined schema but am experimenting with a different way to
display the data. While I have gotten a portion of this working, I am
stuck putting the second condition into the primaryjoin. Classes have
been clipped for brevity.
class ccm_device(DeclarativeBase):
__tablename__ = 'ccm_device'
device_id = Column(mysql.MSBigInteger(20, unsigned = True),
primary_key=True, autoincrement = True)
devicename = Column(Unicode(40))
class cp_package_device(DeclarativeBase):
__tablename__ = 'cp_package_device'
packagedevice_id = Column(mysql.MSBigInteger(20, unsigned = True),
primary_key=True, autoincrement = True)
client_id = Column(mysql.MSBigInteger(20, unsigned = True))
device_id = Column(mysql.MSBigInteger(20, unsigned = True),
ForeignKey('ccm_device.device_id'))
device = relation('ccm_device', uselist=False,
backref='cp_package_device')
users = relation('cp_users', primaryjoin=and_
('cp_package_device.device_id==cp_users.device_id','cp_package_device.client_id==cp_users.client_id'),foreign_keys=
['cp_users.device_id','cp_users.client_id'])
class cp_users(DeclarativeBase):
__tablename__ = 'cp_users'
user_id = Column(mysql.MSBigInteger(20, unsigned = True),
primary_key=True,
autoincrement = True)
client_id = Column(mysql.MSBigInteger(20, unsigned = True),
ForeignKey('clients.client_id')) username = Column(Unicode(64))
device_id = Column(mysql.MSBigInteger(20, unsigned = True),
ForeignKey('ccm_device.device_id'))
The query I am trying to make work is:
tmpl_context.domaintree = meta.Session.query
(cp_package_device).filter(cp_package_device.client_id==client_id).all
()
If I do:
users = relation('cp_users',
primaryjoin='cp_package_device.device_id==cp_users.device_id',foreign_keys='cp_users.device_id')
The query works, but, leaves out the condition to verify that the
client_id's also match.
The SQL I'm trying to emulate is
select * from cp_package_device,cp_users where
cp_package_device.client_id==1 and
cp_package_device.device_id=cp_users.device_id and
cp_package_device.client_id=cp_users.client_id;
When I add the second condition in the primaryjoin as:
users = relation('cp_users', primaryjoin=and_
('cp_package_device.device_id==cp_users.device_id','cp_package_device.client_id==cp_users.client_id'),foreign_keys=
['cp_users.device_id','cp_users.client_id'])
I get:
ArgumentError: Could not determine relation direction for primaryjoin
condition 'cp_package_device.device_id==cp_users.device_id AND
cp_package_device.client_id==cp_users.client_id', on relation
cp_package_device.users. Do the columns in 'foreign_keys' represent
only the 'foreign' columns in this join condition ?
Based on the documentation on
http://www.sqlalchemy.org/docs/05/mappers.html#multiple-relations-against-the-same-parent-child
and the debug message, it appears that it is properly building the
query. Based on orm/properties.py, it appears that foreign_keys can
be a list.
Thanks.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
