orderby=['alias'] - works in sqlite, but not in postgres

[Vlad]

This works perfect in SQLite:

   rows = db(query).select(db.cart_sharing.created_by.with_alias('sharer'),
                           db.cart_sharing.stats.count().with_alias('carts'),
                           db.cart_sharing.stats.sum().with_alias('shares'),
                           groupby=db.cart_sharing.created_by,
                           orderby=['~shares'])

'shares' is alias to sum(), and sqlite understands exactly what's needed - sorts by the number of shares. 

Posgres doesn't like it: 

      <class 'psycopg2.ProgrammingError'> column "shares" does not exist LINE 1: ...NULL) GROUP BY "cart_sharing"."created_by" ORDER BY ~shares; ^

What's the proper DAL syntax for such a thing compatible with Postgres? 

[Val K]

Try [~db.cart_sharing.stats.sum()]

[Eliezer (Vlad) Tseytkin]

Thank you!

On Tue, Jun 18, 2019, 1:19 AM Val K <valq...@gmail.com> wrote:

Try [~db.cart_sharing.stats.sum()]

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/87RAPjGI0_k/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/026bdb99-f659-4d35-849c-3df119671f9e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Anthony]

In SQLite, the tilde (~) is a unary operator used for bitwise negation. If used as a prefix for an order by column, SQLite will not throw an error (as it is a valid operator), but nor will sort they way you expect. In Postgres, the tilde is not a unary operator but a regular expression operator, so when used in this way, it will throw an error. In any case, in SQL, "DESC" is used to indicate descending order ("ASC" for ascending).

So, you can do:

orderby='shares DESC'

Anthony

[Vlad]

Anthony, it works like a charm, thank you very much!! 

(Val's solution with also worked great, but it felt awkward to use the whole thing when an alias was created, so emotionally this solution with alias feels better :)

On a side note, for SQLite orderby=['~shares'] works perfect - it indeed sorts it out properly. Just in case I've verified it again now. 

By the way, Val's solution was orderby=[~db.cart_sharing.stats.count()] - with tilda, and it worked even in Postgres. Somehow tilda doesn't work specifically with an alias - but without an alias it's just fine on both dbs. 

But in any case, orderby=['shares DESC'] works everywhere flawlessly and feels good too, so it's a winner :)

Thank you! 

[Anthony]

On Tuesday, June 18, 2019 at 11:04:16 AM UTC-4, Vlad wrote:

Anthony, it works like a charm, thank you very much!! 

(Val's solution with also worked great, but it felt awkward to use the whole thing when an alias was created, so emotionally this solution with alias feels better :)

You can always do:

sum = db.cart_sharing.stats.sum()

And then use sum in multiple places.

 

On a side note, for SQLite orderby=['~shares'] works perfect - it indeed sorts it out properly. Just in case I've verified it again now.

I suppose it works in your case, as you are sorting integers, so the bitwise negation yields the expected order. However, it won't work in all cases (e.g., sorting strings). It is simply not the appropriate syntax for ordering in SQL.

 

By the way, Val's solution was orderby=[~db.cart_sharing.stats.count()] - with tilda, and it worked even in Postgres. Somehow tilda doesn't work specifically with an alias - but without an alias it's just fine on both dbs.

~db.cart_sharing.stats.sum() is Python code, not SQL. Here, the ~ is part of the DAL syntax, and the DAL translates that to:

SUM("cart_sharing"."stats") DESC

So, Postgres never sees the tilde, only "DESC".

But in any case, orderby=['shares DESC'] works everywhere flawlessly and feels good too, so it's a winner :)

Note in this case, 'shares DESC' is just a string, not Python code. The DAL therefore simply passes that string directly to the database as SQL code. Also, note there is no reason to put it inside a list.

Anthony

[Eliezer (Vlad) Tseytkin]

Got it,

Thank you very much

--

Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/87RAPjGI0_k/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/d89ca392-0d5f-4320-81b5-633ca95d51c2%40googlegroups.com.