afaik, this is like traversing a tree, just maybe worse (much bigger
length). i would either load them all somehow (unless they are
zillions), or create a parasitic named groupings, say list1, list2,
list3 and link them via m2m. e.g. list1 links to a1,a2,a4; list 2
links to a6,a5,a7 etc. no idea if these would help in your case
(legacy db)...
svil
On Wednesday 07 January 2009 22:56:22 MikeCo wrote:
> This may not be an SA specific question, but maybe there is an
> answer here.
>
> I have a table that contains data that is actually a linked list.
> Each record has a previd column that is the id of the previous
> record in the list. The code to maintain the list exists and works
> well (a legacy non-SA application).
>
> The problem is how to efficiently retrieve the records in sorted
> order.
>
> Sample code:
>
> # experiment with querying linked list stored in database
> # test data for table x
> # (id, nam, previd)
> # previd creates a lnked list of names ('one', 'two', 'three',
> 'four') xdata = ((1, 'four' ,3),
> (2, 'one' ,0),
> (3, 'three',4),
> (4, 'two' ,2),)
>
> from sqlalchemy import (Column, Integer, String, ForeignKey,
> create_engine, MetaData)
> from sqlalchemy.orm import relation, backref, sessionmaker
> from sqlalchemy.ext.declarative import declarative_base
>
> engine = create_engine('sqlite:///')
> metadata = MetaData(bind=engine)
> Base = declarative_base(metadata=metadata)
> Session = sessionmaker()
>
> class X(Base):
> __tablename__ = 'x'
> id = Column(Integer, primary_key = True)
> name = Column(String)
> previd = Column(Integer, ForeignKey('x.id'))
> nextx = relation('X', uselist=False,
> backref=backref('prevx', uselist=False,
> remote_side='X.id')) def __init__(self, id, name, previd):
> self.id=id
> self.name=name
> self.previd=previd
> def __repr__(s):
> return "--<X> %s %s %s--" % (s.id,s.name,s.previd)
>
> engine.echo=False
> metadata.create_all()
>
> sess = Session()
> for x in xdata:
> xobj = X(x[0], x[1], x[2])
> sess.add(xobj)
> sess.commit()
> sess.expunge_all()
>
> print '# retrieve unsorted'
> query = sess.query(X)
> for r in query:
> print r
> sess.expunge_all()
>
> print '# retrieve sorted'
> query = sess.query(X).filter(X.previd == 0)
> r = query.first()
> while r:
> print r
> r=r.nextx
>
> The sorted retrieval technique used here issues a separate SELECT
> for each row in the list. Is there a way to construct the query to
> retrieve all rows with one SELECT? I'm not sure this can be done
> with plain SQL, but if it is possible we should be able to do it
> with SA too.
>
> --
> Mike
>
>
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
