On Aug 25, 2010, at 3:07 PM, Michael Elsdörfer wrote:
> I have, simplified, the following models:
>
> class Booking(db.Model):
> id = db.Column(db.Integer, primary_key=True)
>
> class PlanBooking(Booking):
> id = db.Column(db.Integer, db.ForeignKey(Booking.id),
> primary_key=True)
>
> class FeatureBooking(Booking):
> id = db.Column(db.Integer, db.ForeignKey(Booking.id),
> primary_key=True)
> plan_booking_id = db.Column(db.Integer,
> db.ForeignKey(PlanBooking.id))
rename the "id" attribute on PlanBooking to "plan_booking_id" or something
other than "id" (note the column name can be whatever, we're talking about the
attribute name), or alternatively, just create your ForeignKey against the
string, "plan_booking_table.id" - your FK is currently against Booking.id
which takes precedence, therefore there is ambiguity in how to create the
inheriting join condition.
The way the "id" column works is just like that described in this example:
http://www.sqlalchemy.org/docs/mappers.html#mapping-a-class-against-multiple-tables
where "user_id" is linked to two columns at once. joined-table inheritance
always puts the basemost column first.
Revisiting why this is, its largely historical. If I had oodles of time to
write tests I might try switching this for 0.7 since test failures don't seem
unreasonable when the order is reversed.
>
> That is, for each plan, additional features can be purchased, and both
> a purchased plan, and a purchased feature have the same set of base
> fields. I'm using joined-inheritance.
>
> SQLAlchemy will complain "Can't determine join between 'booking' and
> 'feature_booking'; tables have more than one foreign key constraint
> relationship between them. Please specify the 'onclause' of this join
> explicitly.", for the declaration of the FeatureBooking class.
>
> Using Google, I managed to avoid this by adding, to the FeatureBooking
> class:
>
> __mapper_args__ = {
> 'inherit_condition': (Booking.id == id),
> }
>
> I assume this is correct, since it seems to make it work.
>
> Now I would like to map the relationship between FeatureBooking and
> PlanBooking, so I define inside FeatureBooking:
>
> plan_booking = db.relationship(PlanBooking,
> backref=db.backref('feature_bookings',))
>
> SQLAlchemy says: Could not determine join condition between parent/
> child tables on relationship FeatureBooking.plan_booking. Specify a
> 'primaryjoin' expression.
>
> So I change this to:
>
> plan_booking = db.relationship(PlanBooking,
> backref=db.backref('feature_bookings',),
> primaryjoin=(PlanBooking.id==plan_booking_id))
>
> And indeed, that seems to work. I don't even need to repeat the
> primaryjoin for the backref, it seems to pick it up non-the-less.
>
> So what I'm unsure about is why I need to do all this - it would seem
> that SQLAlchemy could determine on it's own both how to a) join the
> inheritance relationship, as well as the "plan_booking" relationship -
> both clearly target separate models, and for both models there is a
> separate, matching ForeignKey. If I had to guess I would think that
> SQLAlchemy in some form considers the FeatueBooking.id and Booking.id
> fields to be identical, and thus sees only two identical ForeignKeys
> with the same target.
>
> In fact, if the "plan_booking_id" field is not defined as a ForeignKey
> at all, the "plan_booking" relationship is accepted by SQLAlchemy
> without an explicit primaryjoin expression - SQLAlchemy will
> automatically choose to join FeatureBooking.id == Booking.id, which is
> of course wrong.
>
> So I'm not sure - is this a bug that I should report, just a
> deficiency that might not be fixable, or am I missing something and/or
> this an easier way to do it?
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@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.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
