Re: [SQLObject] Examples of using 'func'

[Luke O. <lu...@me...>]

Martin -

func won't help you here. for SQLObject select's, you only really have control
over the where clause of the query. max (and other aggregates) are not allowed
there in SQL in general, as far as I know (definitely not in Postgres). func
is for accessing stored procs or built-in db functions.

For the same reason you can't do outer joins in SQLObject, you can't currently
use SQLObject.select() to just return a max value: it expects to return
SQLObjects of the class doing the select. So, it would be conceivable to
retrieve the object with the max value in it, although currently not using
SQLBuilder (at least, I don't know of a way to do subselects with it):

  p = Person.select("int_field = (select max(int_field) from person)")
  # returns a list of Person objects, probably one but maybe zero if the table
  # is empty, or more than one if int_field is not unique, so check!
  if len(p):
    max = p[0].intField

Alternatively, you could retrieve the value using the raw connection, and then
optionally retrieve person objects:

  c = Person._connection.cursor()
  c.execute("select max(int_field) from person")
  max = c.fetchone()[0]  #i think? been a long time since i did DBAPI stuff
  Person.select(Person.q.intField == max)

I generally don't go down this route unless absolutely necessary.

Neither of these is particularily pretty (one exposes raw SQL, the other raw db
queries + SQL), but hope it helps a bit. And if you have ideas for cleaning
this up, let us know. (I suppose the immediate fix is to have SQLBuilder
support sub-selects...)

- Luke

Quoting Martin Stoufer <MCS...@lb...>:

> Any docs or examples out there of using 'func'. Namely, I'd like to see
> how to find the max() of an integer field in a table meeting some query
> criteria I define.