[sqlalchemy] multiple foreign keys to the same table
Hi,
I'm using sqlalchemy 0.5 beta 3 and I am trying to have a Call object
that contains two relations to a Contact object. One is the callee
and the other is the caller. The code is as follows:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData,
ForeignKey
from sqlalchemy.orm import relation, backref, mapper
Base = declarative_base()
metadata = Base.metadata
contact_table = Table('contact', metadata,
Column('id', Integer, primary_key=True),
Column('first_name', String(20)),
Column('last_name', String(30)))
call_table = Table('call', metadata,
Column('id', Integer, primary_key=True),
Column('subject', String(255)),
Column('callee_id', Integer, ForeignKey('contact.id')),
Column('caller_id', Integer, ForeignKey('contact.id')))
class Contact(object):
def __init__(self, first_name, last_name):
self.first_name = first_name
self.last_name = last_name
def __repr__(self):
return self.first_name + ' ' + self.last_name
mapper(Contact, contact_table)
class Call(object):
def __init__(self, subject, callee, caller):
self.subject = subject
self.callee = callee
self.caller = caller
def __repr__(self):
return self.subject
mapper(Call, call_table, properties={
'callee':relation(Call,
primaryjoin=call_table.c.callee_id==contact_table.c.id,
backref='callee_calls'),
'caller':relation(Call,
primaryjoin=call_table.c.caller_id==contact_table.c.id,
backref='caller_calls')
})
c = Contact('my_first_name', 'my_last_name')
I get a long error:
sqlalchemy.exc.ArgumentError: Could not locate any equated, locally
mapped column pairs for primaryjoin condition 'call.caller_id =
contact.id' on relation Call.caller. For more relaxed rules on join
conditions, the relation may be marked as viewonly=True.
Can someone tell me what I'm doing wrong, please?
Thanks,
Rob
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Multiple foreign keys to the same table from another table
I have a table that has two columns that reference the same table in a
foreign-key relationship. What do I need to specify and where to
avoid the error below?
: Error determining
primary and/or secondary join for relationship 'FeatureLoc.feature
(Feature)'. If the underlying error cannot be corrected, you should
specify the 'primaryjoin' (and 'secondaryjoin', if there is an
association table present) keyword arguments to the relation()
function (or for backrefs, by specifying the backref using the
backref() function with keyword arguments) to explicitly specify the
join conditions. Nested error is "Can't determine join between
'featureloc' and 'feature'; tables have more than one foreign key
constraint relationship between them. Please specify the 'onclause' of
this join explicitly."
I am reflecting these tables from the database:
feature_t = Table('feature',metadata,autoload=True)
featureloc_t = Table('featureloc',metadata,autoload=True)
class Feature(object):
pass
class FeatureLoc(object):
pass
sqlalchemy.orm.mapper(Feature,feature_t,properties={
'elements' : relation(Element,backref='feature')
})
sqlalchemy.orm.mapper(FeatureLoc,featureloc_t,properties={
'srcfeature' : relation(Feature),
'feature': relation(Feature)
})
Thanks,
Sean
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Multiple Foreign Keys to the same table, problems with second object.
Hi,
I am using the recipe for multiple foreign keys to the same table at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/MultiFkRelations
I am also using SessionContext, and assign_mapper
It works well, except for one issue. The second object (ie that which
the second foreign key references) is only sometimes loaded, and other
times it is None.
Here is my code (with other fields removed):
stockreceipt_table = Table('stockreceipt', meta,
Column('user_id', Integer, ForeignKey('user.id')),
# Cancellation
Column('cancelled_user_id', Integer, ForeignKey('user.id')),
)
class Receipt(object):
"""Stock Receipt"""
assign_mapper(ctx, Receipt, stockreceipt_table,
properties=dict(
user=relation(User, backref='receipts',
primaryjoin=stockreceipt_table.c.user_id ==
users_table.c.id),
cancelled_user=relation(User, backref='cancelled_receipts',
lazy=False,
primaryjoin=stockreceipt_table.c.cancelled_user_id ==
users_table.c.id),
)
)
There is also a table "user" with an id column, which is reverenced
here.
So in an example
# Connect to the database, and create the tables etc
>>> u = User(name='Ali')
>>> u.flush()
>>> r = Receipt(user_id=u.id)
>>> r.flush()
>>> r.user is u
True
>>> r.cancelled_user_id = u.id
>>> r.flush()
>>> r.cancelled_user is u
True
So in this example it works perfectly as expected. However in my code
(a single threaded PyGTK application, it doesn't work all the time,
and r.cancelled_user returns None more often than not. When the
application is restarted, the value is always correct.
The way I found to force it to work was to explicitly look up the
value like:
class Receipt(object):
"""Stock Receipt"""
# A hack to force the getting of the second object
def get_cancelled_user_hack(self):
return User.get_by(id=self.cancelled_user_id)
cancelled_user_hack = property(get_cancelled_user_hack)
I realise this is ugly and breaks all sorts of things in what it
expects User to be able to do with sessions etc, but I could not find
another reliable way to do it. The fact that it occurs only sometimes
may imply some threading issues I guess, but I am not sure where to
look.
When echoing is turned on and r.cancelled_user is accessed after
flushing, a new query is not made (in the application), but it is made
in the example above.
Thank-you for any assistance,
Ali
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
