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 On Thu, Aug 20, 2020, 9:56 PM AGRogers <[email protected]> wrote: > 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. > > On Fri, 21 Aug 2020 at 10:39, [email protected] <[email protected]> > wrote: > >> 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: >> >>> Thanks, I'll check that out too. Might be more efficient than the other >>> alternative. >>> >>> -Jim >>> >>> >>> On Thursday, August 20, 2020 at 7:28:26 PM UTC-5, [email protected] >>> wrote: >>>> >>>> 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: >>>> >>>>> 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 >>>>> >>>>> >>>>> On Thursday, August 20, 2020 at 2:14:26 PM UTC-5, Clemens wrote: >>>>>> >>>>>> 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 >>>>>> >>>>>> On Thursday, August 20, 2020 at 8:52:15 PM UTC+2 Jim S wrote: >>>>>> >>>>>>> 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) >>>>>>> >>>>>>> -- >> 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 [email protected]. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/web2py/ac06edd8-4ccf-4866-b327-3277abb6ec42n%40googlegroups.com >> <https://groups.google.com/d/msgid/web2py/ac06edd8-4ccf-4866-b327-3277abb6ec42n%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> > -- > 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/PG-iBg3d6u4/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > 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 > <https://groups.google.com/d/msgid/web2py/CACWMBMN%2BddrNeKEiY_w6%3Db4i%2Bena1H%2Br5-7k8bVciGV1nT%3De_Q%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/CAERBpoBV6sDeX7PabJhoC4Wj-r-2XQt%2BncB1C5egEGmB8j8YzQ%40mail.gmail.com.
