[web2py] PostgreSQL - column "COLUMN" must appear in the GROUP BY clause or be used in an aggregate function LINE 1:

for grp in db((db.pay_ins_stack.ref_==db.pay_ins.id) & (db.pay_ins.ref_ == db.deal_acc_addrs.id ) & (db.deal_acc_addrs.xcurr_id == xcurr.id) ).select(groupby=(db.deal_acc_addrs.id)): ERROR: column "pay_ins_stack.id" must appear in the GROUP

[web2py] PostgreSQL error on db.currs_stats[0] = {..}

if not currs_stats: db.currs_stats[0] = { 'curr_id': curr_id, 'deal_id': deal_id, 'average_': volume_out, 'count_': 1, } on MySQl all work error: No such record: 0 -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Sou

Re: [web2py] PostgreSQL - column "COLUMN" must appear in the GROUP BY clause or be used in an aggregate function LINE 1:

I think @Leonel Câmara would know best what is going on here. My uneducated guess is that you are using Postgresql12 and that there were some major changes from postgres11 to postgres12. Either Pydal has not caught up yet, or since pydal is not strictly an ORM, you just have to actually adapt you

Re: [web2py] PostgreSQL error on db.currs_stats[0] = {..}

Probably also something for @Leonel Câmara . Can you compare the raw sql of Mysql and PostgreSQL of what you are trying to do please? And post it here, then we can probably see what needs to be done. Thanks! Am Sa., 14. Nov. 2020 um 10:04 Uhr schrieb Dmitrii Ermolaev < ermolaev.icrea...@gmail.

[web2py] Re: How to sort a join by count

Assuming sqlite: person_cnt = db.PERSON_PROJECT_PRIORITY.person_fk.count().with_alias('person_cnt') prioritySet = db((db.PERSON.id == PERSON-PROJECT-PRIORITY.person_fk) & (PROJECT.id == PERSON-PROJECT-PRIORITY.project_fk)).select( ..., person_cnt, 'GROUP_CONCAT(person.p

[web2py] Re: How to sort a join by count

orderby = ~person_cnt суббота, 14 ноября 2020 г. в 19:23:11 UTC+3, valq...@gmail.com: > Assuming sqlite: > person_cnt = > db.PERSON_PROJECT_PRIORITY.person_fk.count().with_alias('person_cnt') > > > prioritySet = db((db.PERSON.id == > PERSON-PROJECT-PRIORITY.person_fk) &

[web2py] Re: How to sort a join by count

sorry, did not mean direct SQL through db.executesql , meant through DAL On Saturday, November 14, 2020 at 8:23:11 AM UTC-8 valq...@gmail.com wrote: > Assuming sqlite: > person_cnt = > db.PERSON_PROJECT_PRIORITY.person_fk.count().with_alias('person_cnt') > > > prioritySet = db((db.PERSON.id

[web2py] Re: How to sort a join by count

There is no db.executesql суббота, 14 ноября 2020 г. в 19:51:52 UTC+3, alexg...@gmail.com: > sorry, did not mean direct SQL through db.executesql , meant through DAL > > On Saturday, November 14, 2020 at 8:23:11 AM UTC-8 valq...@gmail.com > wrote: > >> Assuming sqlite: >> person_cnt = >> db.PE

[web2py] Re: How to sort a join by count

Just in case : pydal doesnt support backend specific aggregate functions суббота, 14 ноября 2020 г. в 19:54:35 UTC+3, valq...@gmail.com: > There is no db.executesql > > суббота, 14 ноября 2020 г. в 19:51:52 UTC+3, alexg...@gmail.com: > >> sorry, did not mean direct SQL through db.executesql ,

[web2py] Re: How to sort a join by count

I have postgres. How would it look for that? On Saturday, November 14, 2020 at 9:03:13 AM UTC-8 valq...@gmail.com wrote: > Just in case : pydal doesnt support backend specific aggregate functions > > > суббота, 14 ноября 2020 г. в 19:54:35 UTC+3, valq...@gmail.com: > >> There is no db.executesql

[web2py] Re: How to sort a join by count

just replace *GROUP_CONCAT* with *string_agg * воскресенье, 15 ноября 2020 г. в 02:19:52 UTC+3, alexg...@gmail.com: > I have postgres. How would it look for that? > > On Saturday, November 14, 2020 at 9:03:13 AM UTC-8 valq...@gmail.com > wrote: > >> Just in case : pydal doesnt support backen

[web2py] Re: How to sort a join by count

looks like need executeSQL needed for string_agg: Is it possible to do more advanced queries (like using string_agg) with DAL in the meantime? Massimo Di Pierro Apr 12, 2017, 4:21:36 AM to web...@googlegroups.com You have to use db.executesql for that. Sorry. On Saturday, November 14, 2020 at 3:

[web2py] Re: How to sort a join by count

It was in 2017 Now you can pass to select raw-sql-strings with any expression, just end with ' ... *AS* some_name' (*AS* must be in uppercase ) воскресенье, 15 ноября 2020 г. в 02:54:53 UTC+3, alexg...@gmail.com: > looks like need executeSQL needed for string_agg: > Is it possible to do more

[web2py] Re: How to sort a join by count

for example, you can use window function to get records with count in one query: db(...).select( ... , 'count(id) OVER() AS cnt' ) воскресенье, 15 ноября 2020 г. в 03:35:34 UTC+3, valq...@gmail.com: > It was in 2017 > Now you can pass to select raw-sql-strings with any expression, just end

[web2py] Re: How to sort a join by count

I made even CTE+recursive: https://github.com/web2py/pydal/issues/627 But have no time to make a PR воскресенье, 15 ноября 2020 г. в 03:41:24 UTC+3, valq...@gmail.com: > for example, you can use window function to get records with count in > one query: > db(...).select( ... , 'count(id)