Sorting by a field of another table referenced by a foreign key in SQLObject

Question

Is it possible to sort results returned by SQLObject by a value of another table?

I have two tables:

    class Foo(SQLObject):
        bar = ForeignKey('Bar')

    class Bar(SQLObject):
        name = StringCol()
        foos = MultipleJoin('Foo')

I'd like to get foos sorted by the name of a bar they are related to.

Doing:

    foos = Foo.select().orderBy(Foo.q.bar)

...would sort the output by bar's ids, but how do I sort them by bar's name?

Answer 1

Below is the answer of a SQLObject maintainer (he has trouble posting it himself because captcha is not displayed):

Do an explicit join:

foos = Foo.select(Foo.q.barID==Bar.q.id, orderBy=Bar.q.name)

This generates a query:

SELECT foo.id, foo.bar_id FROM foo, bar WHERE ((foo.bar_id) = (bar.id)) ORDER BY bar.name

PS. I am the current maintainer of SQLObject. I don't visit stackoverflow.com; a friend of mine pointed me to the question. If you have more questions about SQLObject I invite you to the SQLObject mailing list .