The sense foreign_keys parameter to orm.relationship is unclear

[Andrew Lutomirski]

I just spent several hours cursing at truly bizarre errors involving circular dependencies and then, once I fixed those, bogus SQL queries being generated. The problem is that the documentation for foreign_keys does not indicate which ''side'' of the foreign key relationship is supposed to be specified.

Something like:

foreign_keys - a list of columns which are to be used as “foreign key” columns. These are the columns on the table that is constrained to reference another table, not the columns being referenced.

would help a lot. (It's also unhelpful that all of the examples given are rather arcane. A completely trivial example showing a normal ForeignKey and the foreign_key and primaryjoin parameters that are implied would make this much easier to follow.)

[Mike Bayer]

well foreign_keys is the kind of parameter that you don't really use unless an error message tells you to. The main introduction to this concept is here: http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#handling-multiple-join-paths. The example is a customer with a "billing" and "shipping" address. Is that the example you're saying is "arcane"? some specifics here would be helpful.

[Andrew Lutomirski]

Hmm. That particular example is actually not bad, although I got thrown off a bit since I don't use Declarative. (My project predates declarative.)

The actual situation I have is that there are three tables, A, B, and C. B is a child of A and C is a child of both A and B.

A has a_id, B has a_id (a foreign key to A) and b_id (its primary key). C has a_id and b_id. The catch is that, for each row in C, we require that a_id match both a row in A and the a_id from the corresponding row in B. So the ForeignKey to A in the schema is unnecessary, but I still want to map it.

I still think that making the documentation for the foreign_key parameter (as opposed to just an example) unambiguous would improve the situation.

[Mike Bayer]

Replying to amluto:

Hmm. That particular example is actually not bad, although I got thrown off a bit since I don't use Declarative. (My project predates declarative.)

The declarative use case is the focus of the documentation since that's the standard use pattern. The classical pattern is now an edge case for legacy apps and that small minority of users who prefer that system to be explicit.

The actual situation I have is that there are three tables, A, B, and C. B is a child of A and C is a child of both A and B.

A has a_id, B has a_id (a foreign key to A) and b_id (its primary key). C has a_id and b_id. The catch is that, for each row in C, we require that a_id match both a row in A and the a_id from the corresponding row in B. So the ForeignKey to A in the schema is unnecessary, but I still want to map it.

that's a significantly more esoteric use case than the one where a Customer has to link to two different Address objects ;). The C.a_id column here is essentially redundant.

I still think that making the documentation for the foreign_key parameter (as opposed to just an example) unambiguous would improve the situation.

I'm guessing that you're referring to the docstring on relationship itself, so I have updated this to be more modern and to also refer to the previously mentioned sections in f35be1d578e89f8727cd015e90f3a3f0a43213cf.

[Mike Bayer]

Removing milestone: 0.8.xx (automated comment)