Self Join via Join Table ?

[Stef]

Hello Everyone,
First of all, kudos on SQLAlchemy.. the speed is pretty amazing - I
am coming from the SQLObject world and there is a definite difference.
Excellent work. I am also getting to grips with it pretty quickly,
using object_session and all that good stuff. This said, I have hit
that 20% problem, and am hoping someone can shine a light on it.

I have a table, lets call it Foo and another table Bar. Foo should
be able to get a list of it's parents via Bar or it's children via
Bar. I am also using the declarative_base system rather than table/
mapper defined seperately.

class Foo(Base):
id = Column(Integer, primary_key=True)

class Bar(Base):
parent_id = Column(Integer, default=0)
child_id = Column(Integer, default=0)

So, I thought something like ; children = relation(Foo,
backref=backref('parents'), primaryjoin=and_(Foo.id==Bar.parent_id)

But that's where I hit the 'wall' as it were, is there a way to
setup a synonym for Foo in the primaryjoin clause ? Am I missing
something stupid ? (I am okay with that ;)

Regards
Stef

[a...@svilendobrev.com]

u mean, the Bar is an association table of Foo to Foo?
u have to use secondary_table and/or secondary_join in the relation
setup. And probably specify remote_side or it may not know which Foo
is what.

[Stephen Telford]

Hello Az,
    Yes, Bar is the association table of Foo to Foo. In essence, this is a self join through a join table.. I have tried and hit my head on this for (quite literally) hours. In the end, and for the record, I ended up creating a method on the model itself such as ;

    def children(self):
        childFoo=Foo.__table__.alias()
        return object_session(self).query(Foo).filter(and_(childFoo.c.deleted == 0, childFoo.id <> self.id, self.id == Bar.parent_id))

    Not the prettiest way, nor what I would expect, but in lieu of an actual example, and to help anyone who ends up treading the same path as me, I hope this helps.

    Regards
    Stef

[Michael Bayer]

check out the association proxy extension if you're looking to have "Bar" be "hidden" as an association object.   it will ultimately use Foo/Bar for querying but attribute access would be proxied through the names you confgure.

[Stephen Telford]

Okay, that sounds like a plan but., not to sound too much like a broken record, does anyone have an -actual- example ? looking at pages with a lot of API's is not really going to help me too much :(

This maybe slightly off-topic and it's really NOT meant as flamebait but.. I remember a while ago playing around with DBIx (the perl ORM) and one of the things that -really- made it easy to get to grips with as the DBIx::Cookbook. It maybe a good idea to fling something similiar into the sqlalchemy documentation...

if I had more experience I would write it but.. yes. It definitely is the quickest ORM I have seen/used, but, all the speed is for naught if you hit the 20% wall.

Regards
Stef

[Bobby Impollonia]

I am doing something similar. The following code works for me in SQLA .4.8
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)

bar_table = Table('bar', Base.metadata,
Column('parent_id', Integer, ForeignKey('foo.id'), nullable=False),
Column('child_id', Integer, ForeignKey('foo.id'), nullable=False))

Foo.children = relation(Foo, secondary = bar_table, primaryjoin =
bar_table.c.parent_id == Foo.id, secondaryjoin=bar_table.c.child_id ==
Foo.id, backref='parents')

[Stephen Telford]

Thank you Bobby!! That does make things more easier, and it shows then that I am being a -real- moron here..

from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, mapper, relation

meta = MetaData()
meta.bind = 'postgres://root:sxt...@192.168.2.198/compass_master'

engine = create_engine('postgres://root:mypas...@192.168.2.198/compass_master', echo=True)
Session = sessionmaker(bind=engine)
session = Session()


trip_table = Table('trip', meta, autoload=True)
class Trip(object):
   id = Column(Integer, primary_key=True)
trip = mapper(Trip, trip_table)
  
trip_pc_table = Table('trip_parent_child', meta, autoload=True)
class TripParentChild(object):
   parent_id = Column(Integer, ForeignKey('Trip.id'), nullable=False)
   parent = relation(Trip)
   child_id = Column(Integer, ForeignKey('Trip.id'), nullable=False)
   child = relation(Trip)
trip_pc = mapper(TripParentChild, trip_pc_table)

Trip.children = relation(Trip, secondary = trip_table, primaryjoin = TripParentChild.parent_id == Trip.id, secondaryjoin=TripParentChild.child_id == Trip.id)
Trip.parent = relation(Trip, secondary = trip_table, primaryjoin = TripParentChild.child_id == Trip.id, secondaryjoin=TripParentChild.parent_id == Trip.id)

s = session.query(Trip).get(194143)
print s.children

results in a very nice stack trace;

Traceback (most recent call last):
  File "./s.py", line 37, in <module>
    print s.children
  File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/properties.py", line 628, in __str__
    return str(self.parent.class_.__name__) + "." + self.key
AttributeError: 'RelationProperty' object has no attribute 'parent'


Any thoughts ? I suspect some attempt at 'black magic' in regards to the parent_id

Regards
Stef

[Stephen Telford]

and of course, both the passwords -are- the same (duh ;) .. the 'get()' works fine (obviously ;)

Regards
Stef

[Michael Bayer]

page 199 of the SQLAlchemy oreilly book talks about association proxy ;)