support for secondary/intermediary tables that are not used only for their association

[Former user]

If an intermediary table stores useful values other than the association data, it is possible/likely that a record will exist from one table to the intermediary one, but not through to the second table. On trying to assign that link to the second table using the relation, either a new record will be created or an SQL error will occur because of a violation of a unique/primary constraint.

Implementing the ability to first check and update the intermediary table is one solution but it may not be the best. It is probably a complex solution for a rare scenario. (If it was to be implemented, it would be a nice addition of course!)

An alternate approach would be to allow the user to test this themselves. For example, by creating two relations instead of one (table1 <-> table2 <-> table3 instead of table1 <-> table3 (where the latter is using table2 as the hidden association table)), the user is first able to test if table1.table2 is None, in which case they can set table1.table2 = table2(table3=stuff), or simply update if the link to table2 already exists: table1.table2.table3 = stuff.

When using the preferred single association relation, this check first method is not possible at the moment. table1.table3 == None regardless of whether or not the 'half-way' association has been made to table2 (assuming that if it has, it is only half-way and the link to table3 is not set yet). Would it be possible to implement a way to differentiate these different situations?

[Mike Bayer]

correct me if I'm wrong but how is this different from the association object pattern described in the documentation ?

[Former user]

trying to create a relation to map from table1 to table3 over a hidden intermediary table2, so table3 can be addressed as table1.table3. association object pattern creates two relations, so table3 would be addressed table1.table2.table3, which as i said originally, you can test whether or not the association exists yet by checking if table1.table2 is None. but with the direct relation (that hides the intermediary), you cannot test if the association exists yet as table1.table3 == None in two scenarions: if the association has not been made, or if the association has only been made half way.

[Former user]

just to explain, in the original post, i described what is essentially the association object pattern as a way of distinguishing / comparing with the problem. the association object pattern allows you to differentiate between two scenarios that are blurred together when using a single relation with a secondary table. i would like to see a way to tell these scenarios apart when not using the association object pattern!

[Mike Bayer]

if your association table stores more columns than just the association, you should be using the association object pattern. the feature you describe is not clear to me (a code example would be best).

[Former user]

while using the association object pattern will suffice, it would be nice to be able to use a table as a secondary association table while at the same time using it in a normal 1:1 relation.

will attach some code with a bit more of an explanation.

[Mike Bayer]

please see the associationproxy documentation. it provides exactly this syntax.