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 [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
-~----------~----~----~----~------~----~------~--~---
