Postgres FTI with SQLObject issue resolved

Friday, October 14th, 2005

Ah 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.

This entry was posted on Friday, October 14th, 2005 at 7:14 am and is filed under launchpad. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

2 Responses to “Postgres FTI with SQLObject issue resolved”

  1. Dave Says:
    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

  2. Dave Says:
    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):