[sqlalchemy] Re: Self Join via Join Table ?
page 199 of the SQLAlchemy oreilly book talks about association proxy ;)
On Feb 25, 2009, at 6:12 PM, Stephen Telford wrote:
> 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
>
> On Wed, Feb 25, 2009 at 4:21 PM, Michael Bayer > wrote:
> 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.
>
>
> On Feb 25, 2009, at 4:11 PM, Stephen Telford wrote:
>
>> 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
>>
>> On Wed, Feb 25, 2009 at 4:29 AM, wrote:
>>
>> 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.
>>
>> On Wednesday 25 February 2009 03:39:20 Stef wrote:
>> > 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
>> >
>>
>>
>>
>>
>>
>>
>
>
>
>
>
> >
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Self Join via Join Table ?
and of course, both the passwords -are- the same (duh ;) .. the 'get()'
works fine (obviously ;)
Regards
Stef
On Wed, Feb 25, 2009 at 7:53 PM, Stephen Telford wrote:
> 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:sxta...@192.168.2.198/compass_master'
>
> engine = create_engine('postgres://
> root:mypassw...@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
> 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
>
>
>
> On Wed, Feb 25, 2009 at 7:05 PM, Bobby Impollonia wrote:
>
>>
>> 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')
>>
>> On Wed, Feb 25, 2009 at 3:12 PM, Stephen Telford
>> wrote:
>> > 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
>> >
>> > On Wed, Feb 25, 2009 at 4:21 PM, Michael Bayer <
>> mike...@zzzcomputing.com>
>> > wrote:
>> >>
>> >> 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.
>> >>
>> >> On Feb 25, 2009, at 4:11 PM, Stephen Telford wrote:
>> >>
>> >> 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
>> >>
>> >> On Wed, Feb 25, 2009 at 4:29 AM, wrote:
>> >>>
>> >>> 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.
>> >>>
>> >
[sqlalchemy] Re: Self Join via Join Table ?
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:sxta...@192.168.2.198/compass_master'
engine = create_engine('postgres://
root:mypassw...@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
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
On Wed, Feb 25, 2009 at 7:05 PM, Bobby Impollonia wrote:
>
> 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')
>
> On Wed, Feb 25, 2009 at 3:12 PM, Stephen Telford
> wrote:
> > 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
> >
> > On Wed, Feb 25, 2009 at 4:21 PM, Michael Bayer >
> > wrote:
> >>
> >> 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.
> >>
> >> On Feb 25, 2009, at 4:11 PM, Stephen Telford wrote:
> >>
> >> 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
> >>
> >> On Wed, Feb 25, 2009 at 4:29 AM, wrote:
> >>>
> >>> 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.
> >>>
> >>> On Wednesday 25 February 2009 03:39:20 Stef wrote:
> >>> > 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
[sqlalchemy] Re: Self Join via Join Table ?
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')
On Wed, Feb 25, 2009 at 3:12 PM, Stephen Telford wrote:
> 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
>
> On Wed, Feb 25, 2009 at 4:21 PM, Michael Bayer
> wrote:
>>
>> 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.
>>
>> On Feb 25, 2009, at 4:11 PM, Stephen Telford wrote:
>>
>> 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
>>
>> On Wed, Feb 25, 2009 at 4:29 AM, wrote:
>>>
>>> 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.
>>>
>>> On Wednesday 25 February 2009 03:39:20 Stef wrote:
>>> > 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
>>> >
>>>
>>>
>>
>>
>>
>>
>>
>>
>>
>
>
> >
>
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Self Join via Join Table ?
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
On Wed, Feb 25, 2009 at 4:21 PM, Michael Bayer wrote:
> 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.
>
>
> On Feb 25, 2009, at 4:11 PM, Stephen Telford wrote:
>
> 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
>
> On Wed, Feb 25, 2009 at 4:29 AM, wrote:
>
>>
>> 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.
>>
>> On Wednesday 25 February 2009 03:39:20 Stef wrote:
>> > 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
>> >
>>
>>
>>
>
>
>
>
>
> >
>
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Self Join via Join Table ?
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.
On Feb 25, 2009, at 4:11 PM, Stephen Telford wrote:
> 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
>
> On Wed, Feb 25, 2009 at 4:29 AM, wrote:
>
> 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.
>
> On Wednesday 25 February 2009 03:39:20 Stef wrote:
> > 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
> >
>
>
>
>
> >
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Self Join via Join Table ?
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
On Wed, Feb 25, 2009 at 4:29 AM, wrote:
>
> 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.
>
> On Wednesday 25 February 2009 03:39:20 Stef wrote:
> > 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
> >
>
> >
>
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Self Join via Join Table ?
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.
On Wednesday 25 February 2009 03:39:20 Stef wrote:
> 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
>
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
