Some of the orderBy operations I need to perform occur with joins
between lots of tables. Instead of having to hand write all the
expressions prescribed by this post:
http://osdir.com/ml/python.sqlobject/2005-06/msg00055.html.
I instead wrote a BreadthFirstSearch algorithm to scan through my
SQLObject's to build the needed expression. The only requirement on
the objects is that they have ForeignKey's to traverse over like edges
in a graph. If a route can not be found a ValueError exception is
raised.
I'm putting it up here for comment. Is there something inherently evil
about this approach? Was there a particular portion I should have
wrote differently? It's written in two functions to keep the traversal
separate from the expression building, they could be merged together,
but I think that would obscure the meaning a bit. Can I safely include
other types of joins?
It works as follows:
class Foo(SQLObject):
bar = ForeignKey("Bar")
class Bar(SQLObject):
blah = ForeignKey("Blah")
class Blah(SQLObject):
sortme = Col()
expr = BuildJoinExpression(Foo, Blah.q.sortme)
results = Foo.select(expr, orderBy=Blah.q.sortme)
And the functions:
from sqlobject import sqlbuilder
from sqlobject.classregistry import findClass
def BreadthFirstSearch(table, column):
queue = [(table, [])]
while queue:
node, fkeys = queue.pop(0)
for col in node.sqlmeta.columnList:
if isinstance(col, SOForeignKey):
cls = findClass(col.foreignKey)
pfkeys = fkeys + [cls]
if cls.sqlmeta.table == column.tableName:
return pfkeys
queue.append((cls, pfkeys))
return None
def BuildJoinExpression(baseTable, column):
tables = BreadthFirstSearch(baseTable, column)
if tables is None:
raise ValueError, "%s relation to %s can not be found" %
(baseTable, column)
exprs = []
prevTable = getattr(sqlbuilder.table, baseTable.sqlmeta.table)
for table in tables:
left = getattr(prevTable, table.sqlmeta.idName)
nextTable = getattr(sqlbuilder.table, table.sqlmeta.table)
rght = getattr(nextTable, table.sqlmeta.idName)
exprs.append(left == rght)
prevTable = nextTable
return AND(*exprs)
I do realize I don't do cycle detection and thus could end up in an
infinite loop. I didn't bother putting this in because I've never seen
a database layout with cyclic ForeignKey references. Do they exist?
Are they common? Am I missing out on some cool new paradigm =-)?
Thanks
-Brian
-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
sqlobject-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
