count() with left join

[Jim S]

Hi

I'm trying to get the count of records to be returned in a query using:

db(query).count()

Adding complexity to the situation is that query may sometimes be over multiple tables with need a left clause added.  When selecting records you do this by passing the left= parameter inside the .select().

But, you can't pass anything into the .count() method.

Has anyone found an efficient way to get a .count() when a left join is in use?

-Jim

(cross-posting to py4web as well)

[Clemens]

Hi Jim,

I had this issue some time ago and I solved it by the workaround of using len(db(query).select()). The count()-method is a little more performant, but in my case it didn't matter. If you need the rows object of the select for further processing anyway, you can have the len() on the rows object.

It's not perfect, but it works :-)

Best regards

Clemens

[Jim S]

Clemens

Thanks so much, that worked perfect.  I guess I was a little concerned about the performance, doing the whole select, but then realized that this should cut down on result set size, so shouldn't matter that much.

Again, thank you

-Jim

[valq...@gmail.com]

u_cnt = db.auth_user.id.count().with_alias('user_count')

user_count = db(db.auth_user).select(u_cnt,  left = ...).first().user_count

or just passing raw SQL as field:

user_count = db(db.auth_user).select('count(id) AS user_count',  left = ...).first().user_count  

четверг, 20 августа 2020 г. в 22:27:04 UTC+3, Jim S:

[Jim S]

Thanks, I'll check that out too.  Might be more efficient than the other alternative.

-Jim

[valq...@gmail.com]

len(db(query).select())  - is bad solution, since it loads all records into memory  + parsing/transforming into pydal records 

пятница, 21 августа 2020 г. в 03:35:33 UTC+3, Jim S:

[AGRogers]

I have just resorted to pure SQL using db.executesql (I know that doesnt help you). Can't get much faster than that though. I like how the results can be returned as a simple dictionary. But i realised i need to be careful if i ever target a different DB provider. And permissions and common filters etc. 

I find the DAL really great to work with for inner joins on any number of tables . Especially when i have particular query parts that i want to reuse. But for outer joins I struggle and prefer to go back to SQL if i can.

--
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+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/ac06edd8-4ccf-4866-b327-3277abb6ec42n%40googlegroups.com.

[Jim Steil]

Thanks Andrew, but for my situation I need to use a dal query. I'm going to give the previous suggestion a try in the morning.

Jim

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/PG-iBg3d6u4/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/CACWMBMN%2BddrNeKEiY_w6%3Db4i%2Bena1H%2Br5-7k8bVciGV1nT%3De_Q%40mail.gmail.com.