Postgres FTI with SQLObject issue resolved
Friday, October 14th, 2005Ah joy. Figured out that SQLObject can be extended fairly easily to handle the ranking problem I referred to previously.
The goal is to end up with a query like this:
SELECT Foo.id, Foo.name, Foo.title, rank(Foo.fti, ftq('browser')) AS rank FROM Foo WHERE Foo.fti @@ ftq('browser') ORDER BY rank
So, hacking SQLObject to add a selectAlso= option to SQLObject.select gives us the ability to write code like this:
results = Foo.select("fti @@ ftq('browser')", selectAlso="rank(fti, ftq('browser')) AS rank", orderBy='-rank')
Voila. I’ll pass the SQLObject patch on to Stub for a review and submission upstream.
November 21st, 2007 at 4:59 pm
Hi Mark,
What happened to that patch do you know? I could really do with it right now (trying to get sqlobject & fti to play). If you still have the code a) that would be amazing after 2 years and b) that would be fantastic.
Dave
November 22nd, 2007 at 11:49 am
Disregard my last comment. I’ve patched it myself – but thanks for the idea.
Here’s the patch if you want to post it to aid anyone else in this situation…
diff -u ./dbconnection.py virtual/lib/python2.4/site-packages/SQLObject-0.7.9-py2.4.egg/sqlobject/dbconnection.py
— ./dbconnection.py 2007-11-21 17:22:00.000000000 +0000
+++ virtual/lib/python2.4/site-packages/SQLObject-0.7.9-py2.4.egg/sqlobject/dbconnection.py 2007-11-21 17:11:02.000000000 +0000
@@ -404,6 +404,9 @@
else:
columns = “, “.join([“%s.%s” % (cls.sqlmeta.table, col.dbName)
for col in cls.sqlmeta.columnList])
+ also = ops.get(‘selectAlso’)
+ if also:
+ columns += “, ” + also
if columns:
q += “%s.%s, %s FROM %s” % \
(cls.sqlmeta.table, cls.sqlmeta.idName, columns,
diff -u ./main.py virtual/lib/python2.4/site-packages/SQLObject-0.7.9-py2.4.egg/sqlobject/main.py
— ./main.py 2007-11-21 17:22:00.000000000 +0000
+++ virtual/lib/python2.4/site-packages/SQLObject-0.7.9-py2.4.egg/sqlobject/main.py 2007-11-21 17:07:01.000000000 +0000
@@ -1289,7 +1289,7 @@
orderBy=NoDefault, limit=None,
lazyColumns=False, reversed=False,
distinct=False, connection=None,
– join=None):
+ join=None, selectAlso=None):
return cls.SelectResultsClass(cls, clause,
clauseTables=clauseTables,
orderBy=orderBy,
@@ -1298,7 +1298,8 @@
reversed=reversed,
distinct=distinct,
connection=connection,
– join=join)
+ join=join,
+ selectAlso=selectAlso)
select = classmethod(select)
def selectBy(cls, connection=None, **kw):