Okay, that's fair. I figured it was a bit pathological, even though
it's a real-world use case. :)
Sorry I didn't find #1401 in my searches. Wrong keywords!
I'll see if I can find some time to write up a sqla test case out of
my repro script. Too bad my netbook seems dead set on corrupting my
flash media. :(
Thanks for the prompt response!
On Fri, May 6, 2011 at 11:55 AM, Michael Bayer <mike...@zzzcomputing.com> wrote:
> 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.
>
>
--
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.
