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, valq...@gmail.com <valq7...@gmail.com> 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, valq...@gmail.com
>> 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 web2py+unsubscr...@googlegroups.com.
> 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 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/CACWMBMN%2BddrNeKEiY_w6%3Db4i%2Bena1H%2Br5-7k8bVciGV1nT%3De_Q%40mail.gmail.com.
