How to sort a join by count

[Alex Glaros]

How to write a statement that counts which projects are the priority of most people

PROJECT
id
project_name

PERSON
id
person_name

PERSON_PROJECT_PRIORITY
person_fk
project_fk

prioritySet = db((db.PERSON.id == PERSON-PROJECT-PRIORITY.person_fk) & (PROJECT.id == PERSON-PROJECT-PRIORITY.project_fk)).select()

How to sort by count of projects which have priority in order of the most persons' priority?

Output looks like this:

Paint-the-house (10)  [Means is the top priority for 10 people]

Plant-a-garden (5)

Clean-out-garage (2)

If you have additional time, how to write so output looks like:

Paint-the-house (10) Tom, Sue, Tony, Ted, Mary, Fred, Sal, Chris, Ed, Sally

Plant-a-garden   (5) Harry, George, Joanne, Tony, Janet

Clean-out-garage (2) Clyde, Jane

Thanks,

Alex Glaros

[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) & (PROJECT.id == PERSON-PROJECT-PRIORITY.project_fk)).select(

...,

person_cnt, 

'GROUP_CONCAT(person.person_name,",") AS person_list'

groupby = db.PERSON-PROJECT-PRIORITY.project_fk, 

)

суббота, 14 ноября 2020 г. в 09:40:17 UTC+3, alexg...@gmail.com:

[valq...@gmail.com]

orderby = ~person_cnt

суббота, 14 ноября 2020 г. в 19:23:11 UTC+3, valq...@gmail.com:

[Alex Glaros]

sorry, did not mean direct SQL through db.executesql , meant through DAL

[valq...@gmail.com]

There is no db.executesql

суббота, 14 ноября 2020 г. в 19:51:52 UTC+3, alexg...@gmail.com:

[valq...@gmail.com]

Just in case : pydal doesnt support backend specific aggregate functions

суббота, 14 ноября 2020 г. в 19:54:35 UTC+3, valq...@gmail.com:

[Alex Glaros]

I have postgres. How would it look for that?

[valq...@gmail.com]

  just replace GROUP_CONCAT with string_agg  

воскресенье, 15 ноября 2020 г. в 02:19:52 UTC+3, alexg...@gmail.com:

[Alex Glaros]

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.

[valq...@gmail.com]

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:

[valq...@gmail.com]

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:

[valq...@gmail.com]

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:

[Alex Glaros]

Thanks for detailed responses Val, I appreciate it.