I'm wrapping SA 0.5.5 around a legacy database that we're in the process of
replacing with something properly normalized. In the mean time, I'm still
trying to bend it to my will. The problem I'm having is that I'm getting
some unexpected results from queries. Here's a minimal test case that
demonstrates it:
###################################
class Invoice(Base):
__tablename__ = 'invoice'
__table_args__ = dict(schema='fro')
# Each invoice has a unique invid
invid = Column(Integer, primary_key=True)
# This is the name of the customer on this invoice
customer = Column('xrscust', String(10), ForeignKey('fro.xrscust.xrscust'))
# Some customers have multiple payment addresses, so point to the
# one used for this specific invoice
pay2addrid = Column(Integer, ForeignKey('fro.bllginfo.pay2addrid'))
BillingInfo = relation('BillingInfo',
primaryjoin='Invoice.pay2addrid==BillingInfo.pay2addrid')
class BillingInfo(Base):
__tablename__ = 'bllginfo'
__table_args__ = dict(schema='fro')
# This matches another column in the Customer table
xrscustid = Column(Integer, ForeignKey('fro.xrscust.xrscustid'),
primary_key=True)
# One of several values, indicating which type of shipment an
# invoice is for
typeship = Column(String(3))
# Points to an additional 'address' table, not defined here for clarity
pay2addrid = Column(Integer, primary_key=True)
# Those two columns, xrscustid and pay2addrid, are a composite
# primary key for this table
Customer = relation('Customer', backref='BillingInfo')
class Customer(Base):
__tablename__ = 'xrscust'
__table_args__ = dict(schema='fro')
xrscustid = Column(Integer, primary_key=True)
customer = Column('xrscust', String(10))
###################################
So, a customer has an invoice. The 'invoice' table refers to the 'xrscust'
"customer" table and the 'bllginfo' "billing information" table. There may
be several rows in bllginfo with the same pay2addr value or xrscustid
value, but the two columns together are a unique key.
Now, trying a query on the above:
invoices = session.query(Invoice)
invoices = invoices.join(BillingInfo)
invoices = invoices.join((Customer, BillingInfo.xrscustid==Customer.xrscustid))
invoices = invoices.filter(BillingInfo.typeship=='GBL')
invoices = invoices.filter(Invoice.invid==2663703)
print invoices[0].BillingInfo.typeship
The print statement emits 'FSM'. Now, I've verified by hand that this isn't
possible. The bllginfo table has several rows with the correct pay2addrid,
but only one where bllginfo.xrscustid = xrscust.xrscust and xrscust.xrscust
= invoice.xrscust. (I apologize if that gets confusing)
And even if extraneous rows had snuck in, shouldn't the filter on
(BillingInfo.typeship=='GBL') make that result impossible?
--
Kirk Strauser
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
