On Thursday 30 July 2009 12:56:19 pm Michael Bayer wrote: > at this point, invoices[0] is the invoice that is subject to the given > filter criterion.
With you so far. > > print invoices[0].BillingInfo.typeship > now this part is very unusual and is something I haven't tested. Your > foreign key is to only one column of a composite primary key - very > strange. So SQLA probably sees this as "many-to-one" but the result in > which the lazy load will incur is essentially random since many > BillingInfo entries may have that same value. That's exactly it. I tried forcing an eager load, but the resulting left outer join yielded the same random results. I suppose that until we restructure the database, a workaround would be to split the query into two steps where I fetch the BillingInfo rows and loop through them manually, but I'm lazy and I'd much rather let SA do the work if I can teach it how. > Its very likely that your linkage here is incorrect, and you in fact want > to declare, at least within SQLAlchemy-land, a composite foreign key > (using ForeignKeyConstraint) on Invoice that matches both invoice.xrscust > and invoice.pay2addrid to both of the corresponding columns on > BillingInfo. The explicit join condition on the relation() would then no > longer be needed (the need to explicitly declare things SQLA should be > figuring out may be considered a code smell here). But Invoice doesn't have both of those columns. It has a String reference to Customer, while BillingInfo has an Integer reference to Customer [1]. That's why I ended up with the cyclic relation, and wouldn't otherwise need to involve Customer at all. [1] Again, legacy. The numeric primary key was added *relatively* recently and new tables use it. At this exact moment, we have both String and Integer references to the Customer table. This whole mess is based on data that are copied from Visual FoxPro tables on an hourly basis, and all new code is written to query PostgreSQL instead of VFP. -- Kirk Strauser --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
