We have a multi-tenant database that includes a heirarchical layout. We tried to apply subqueryloads across the board, but tests fail in this one case.
class Folder(Base):
__tablename__ = 'folder'
__table_args__ = (
ForeignKeyConstraint(['accountid', 'parent'],
['folder.accountid', 'folder.folderid']),
{}
)
folderid = Column(Integer(), primary_key=True, nullable=False)
accountid = Column(Integer(), primary_key=True,
autoincrement=False, nullable=False)
parent = Column(Integer(), primary_key=False, nullable=False)
name = Column(String(length=50), primary_key=False, nullable=False)
Folder.child_folders = relation(
Folder,
backref=backref(
'parent_folder',
remote_side=[Folder.accountid, Folder.folderid]
)
)
We have multiple hierarchies with overlapping folderids, so we need
any walk to related resources to include that restriction. With normal
lazy loads, things work fine. With either eagerloads or subqueryloads,
we get too many rows back. It looks like the accountid part of the
foreign key doesn't get specified properly in the query:
"folder.accountid = folder.accountid".
eagerload:
SELECT …
FROM folder LEFT OUTER JOIN folder AS folder_1 ON
folder.accountid = folder.accountid --< lol, wut
AND folder.folderid = folder_1.parent
WHERE folder.folderid = ? AND folder.accountid = ?
subqueryload:
SELECT …
FROM (SELECT … FROM folder WHERE folder.folderid = ? AND
folder.accountid = ?) AS anon_1
JOIN folder ON
folder.accountid = folder.accountid --< lol, wut
AND anon_1.folder_folderid = folder.parent
ORDER BY anon_1.folder_accountid, anon_1.folder_folderid
I've attached a full sqlite repro script and tried it against 0.6.4,
0.6.8dev, and 0.7b5; same result every time.
Thoughts? Thanks!
--
taa
/*eof*/
--
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.
subquery_self_referential_foreign_key.py
Description: Binary data
