That works great!
It revealed a bug though :
When there are columns prefixed by a '_' in the mapper but not in the
DB (quite usual), they are not properly loaded using the 'neighbors'
property.
In your example :
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)
_data = Column('data', Integer)
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, _data=1)
t2 = Tile(id=2, _data=2)
t3 = Tile(id=3, _data=3)
t4 = Tile(id=4, _data=3)
session.add_all([t1, t2, t3, t4])
session.commit()
session.expunge(t1)
session.expunge(t2)
session.expunge(t3)
session.expunge(t4)
t4 = session.query(Tile).get(4)
print t4.neighbors[0]._data
will print None
On Sep 9, 11:12 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> 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 [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
-~----------~----~----~----~------~----~------~--~---
