OK this is actually a classic issue, for which no solution has been built yet.
Mostly because its an incredibly obscure case - there's a way to do it in the
internals but its a bit of a killer.
we get the relationship's local side:
(Pdb) self.local_side
OrderedSet([Column('accountid', Integer(), ForeignKey('folder.accountid'),
table=<folder>, primary_key=True, nullable=False), Column('folderid',
Integer(), table=<folder>, primary_key=True, nullable=False)])
we get the relationship's remote side:
(Pdb) self.remote_side
OrderedSet([Column('accountid', Integer(), ForeignKey('folder.accountid'),
table=<folder>, primary_key=True, nullable=False), Column('parent', Integer(),
ForeignKey('folder.folderid'), table=<folder>, nullable=False)])
and the primaryjoin:
(Pdb) primaryjoin.__str__()
'folder.accountid = folder.accountid AND folder.folderid = folder.parent'
we tell ClauseAdapter, "change all the columns in the "folder" table, which are
not in the "local side", to come from the alias "folder_1"".
when we chain this along several joins, we also have to say:
"change all the columns in the "folder" table, which are not in the "remote
side", to come from the alias "folder_1"".
the solution has to involve adding annotations to just one side of
"folderid=folderid", then enhancing ClauseAdapter to be able to take cues from
callables that look at annotations, instead of just presence in a set.
so the answer right now is that we don't have this one case covered. Its
ticket 1401 which is two years old, I've added some thoughts there.
the two workarounds are to use joined load with contains_eager(), or the
hand-rolled subqueryload recipe at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisjointEagerLoading .
On May 6, 2011, at 11:15 AM, Taavi Burns wrote:
> 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>
--
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.
