sqlobject-discuss

[SQLObject] Subquery MAX().

[Ben T. <bt...@gm...>]

What is the most expedient way to do the following?

--
serial = Path.selectBy().max('serial')
return Path.getOne(serial=serial)
---

I would like some SQL like this:

--
select * from path where serial = (select max(serial) from path);
--

Thanks.

Re: [SQLObject] Subquery MAX().

[Oleg B. <ph...@ph...>]

On Wed, Dec 22, 2010 at 10:18:49PM -0500, Ben Timby wrote:
> What is the most expedient way to do the following?
> 
> serial = Path.selectBy().max('serial')
> return Path.getOne(serial=serial)
> 
> I would like some SQL like this:
> 
> select * from path where serial = (select max(serial) from path);

    .select().max() produces a simple query SELECT MAX(). If you need a
query with a subquery you need to add the subquery explicitly using
sqlbuilder.Select():

serial = Path.select(
    Path.q.serial==Select('max(serial)', staticTables=['path']))
return serial.getOne()

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Subquery MAX().

[Ben T. <bt...@gm...>]

The python code was wrong in my last email, it was what I THOUGHT it
should be, the python that produces the bad SQL is:

--
query = Sync.select(
    AND(
        Sync.q.syncpoint==self,
        Sync.q.serial==sqlbuilder.Select('max(serial)',
Sync.q.syncpoint==self, staticTables=('sync',))
    )
)
--

Re: [SQLObject] Subquery MAX().

[Ben T. <bt...@gm...>]

On Thu, Dec 23, 2010 at 5:58 PM, Oleg Broytman <ph...@ph...> wrote:
>    .select().max() produces a simple query SELECT MAX(). If you need a
> query with a subquery you need to add the subquery explicitly using
> sqlbuilder.Select():
>
> serial = Path.select(
>    Path.q.serial==Select('max(serial)', staticTables=['path']))
> return serial.getOne()

Thanks for your reply Oleg. I am sorry, but the above produces a bad query:

--
query = Sync.select(
    AND(
        Sync.q.syncpoint==self,
        Sync.q.serial==sqlbuilder.Select(MAX('serial'),
Sync.q.syncpoint==self, staticTables=('sync',))
    )
)
--

--
SELECT sync.id, sync.syncpoint_id, sync.serial, sync.stamp,
sync.status, sync.message FROM sync WHERE (((sync.syncpoint_id) = (1))
AND ((sync.serial) = (SELECT 'max(serial)' FROM sync WHERE
((sync.syncpoint_id) = (1)))));
--

As you can see, the 'max(serial)' ends up inside quotes within the
subselect. I can't find any MAX aggregate function to use in place of
the string...

Any additional hints?

Re: [SQLObject] Subquery MAX().

[Oleg B. <ph...@ph...>]

On Fri, Dec 24, 2010 at 10:09:49AM -0500, Ben Timby wrote:
> Thanks for your reply Oleg. I am sorry, but the above produces a bad query:
> 
> --
> query = Sync.select(
>     AND(
>         Sync.q.syncpoint==self,
>         Sync.q.serial==sqlbuilder.Select(MAX('serial'),
> Sync.q.syncpoint==self, staticTables=('sync',))
>     )
> )
> --
> SELECT sync.id, sync.syncpoint_id, sync.serial, sync.stamp,
> sync.status, sync.message FROM sync WHERE (((sync.syncpoint_id) = (1))
> AND ((sync.serial) = (SELECT 'max(serial)' FROM sync WHERE
> ((sync.syncpoint_id) = (1)))));
> --
> 
> As you can see, the 'max(serial)' ends up inside quotes within the
> subselect. I can't find any MAX aggregate function to use in place of
> the string...

   Oops, columns in Select cannot be strings - to allow strings in
queries.

   Use sqlbuilder.Select(sqlbuilder.func.MAX(sqlbuilder.const.serial)

from sqlobject.sqlbuilder import Select, func, const
Select(func.MAX(const.serial)

   looks better for my eyes.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Subquery MAX().

[Ben T. <bt...@gm...>]

On Fri, Dec 24, 2010 at 10:44 AM, Oleg Broytman <ph...@ph...> wrote:
>   Oops, columns in Select cannot be strings - to allow strings in
> queries.
>
>   Use sqlbuilder.Select(sqlbuilder.func.MAX(sqlbuilder.const.serial)
>
> from sqlobject.sqlbuilder import Select, func, const
> Select(func.MAX(const.serial)
>
>   looks better for my eyes.

Oleg, once again, thank you.

I was looking for the MAX function and was unable to find it. Const is
interesting as well, it takes the provided attr and transforms it to
verbatim in the resulting SQL, neat.

The above works perfectly!

Re: [SQLObject] Subquery MAX().

[Oleg B. <ph...@ph...>]

On Fri, Dec 24, 2010 at 11:56:01AM -0500, Ben Timby wrote:
> Oleg, once again, thank you.
> 
> I was looking for the MAX function and was unable to find it. Const is
> interesting as well, it takes the provided attr and transforms it to
> verbatim in the resulting SQL, neat.

   ``const`` and ``func`` are aliases for the same object! (-: Look them
up in sqlbuilder.py.

> The above works perfectly!

   You are welcome!

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.