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!
On Tuesday, June 18, 2019 at 10:41:21 AM UTC-4, Anthony wrote:
>
> 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
>
> On Monday, June 17, 2019 at 11:48:51 PM UTC-4, Vlad wrote:
>>
>> 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?
>>
>>
>>
--
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 the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/web2py/d60f3d51-92ec-4ca7-a7e8-36fed3b1185d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
