you can manage this by making SQLAlchemy fully aware of "table_alias"
by creating a separate mapper. Since you are viewonly=True you have
more flexiblity in this regard:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite://', echo=True)
Session = scoped_session(sessionmaker(bind=engine))
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Tile(Base):
__tablename__ = "tiles"
id = Column(Integer, primary_key=True)
Base.metadata.create_all(engine)
tile_table = Tile.__table__
tile_alias = tile_table.alias()
related_tiles = mapper(Tile, tile_alias, non_primary=True)
Tile.neighbors = relation(related_tiles, primaryjoin=tile_table.c.id >
tile_alias.c.id, foreign_keys=[tile_alias.c.id], viewonly=True)
session = sessionmaker(bind=engine)()
t1 = Tile(id=1)
t2 = Tile(id=2)
t3 = Tile(id=3)
t4 = Tile(id=4)
session.add_all([t1, t2, t3, t4])
session.commit()
assert t2.neighbors == [t1]
assert t4.neighbors == [t1, t2, t3]
assert t1.neighbors == []
On Sep 9, 2008, at 4:50 PM, GustaV wrote:
>
> Hello,
>
> I'm looking into something a bit tricky : having a relation between a
> table and itself (an adjacency list then) with a non-trivial join
> condition.
> Let's say for example, in the case of a Checkers game board for
> example, to query the neighboring tiles of a given tile. To do the
> query itself is easy, but then it can't be eagerloaded which is a
> shame...
>
> I tried something like (with a "stupid" condition) :
> table_alias = mytable.alias()
> mapper(Tile, mytable, properties={
> 'neighbors' = relation(Tile, primaryjoin=mytable.c.x >
> table_alias.c.x,
> foreign_keys=[mytable.c.x],
> viewonly=True)
> })
>
> but that does not do what I could expect : it queries both tables
> (normal and aliased) but returns result from the non-alias table
> (without eagerloading) or fails (with eagerload) because it uses
> incoherent aliased tables.
>
> The problem comes from we have no access to the table being
> automatically aliased when doing an adjacency relation here ; and
> building a new aliased table is a non-sense.
>
> In one word : HELP !
>
> Gus
> >
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
