Re: group by 3 fields

On Mon, Feb 27, 2017 at 3:41 AM, <marcin.j.nowak@gmail.com> wrote:
>
>
> On Monday, February 27, 2017 at 3:52:38 AM UTC+1, Larry....@gmail.com wrote:
>>
>> [SQL] That is a language I
>> have worked in for over 20 years, and when I see a querying need that
>> is how I think, and then I see how can I do that with the ORM.
>
>
> So I shouldn't give advices for you. We have similar experience, I think.

No, I will always take advice. Because you don't know what you don't know.

> With Django you may play with .aggregate() and .annotate() functions.
> You should be able to do grouping you need Just tell a ORM about aggregates
> you want to use, and it will automagically add group by.
>
> Let's try group by col1, col2:
>
>> from django.db.models import Count
>> from django.contrib.auth.models import User
>
>> print User.objects.values('is_staff',
>> 'is_superuser').annotate(cnt=Count('*')).query
> SELECT `auth_user`.`is_staff`, `auth_user`.`is_superuser`, COUNT(*) AS `cnt`
> FROM `auth_user` GROUP BY `auth_user`.`is_staff`, `auth_user`.`is_superuser`
> ORDER BY NULL
>
> You must tell Django that you need some aggregate, and then ask for
> selecting other values explicitely. These columns will be added to a group
> by.
> Please note that as a result you will get iterable of dicts instead of model
> instances, which is pretty reasonable.

As is so often the case, the requirements changed. Now what I had to
do, if I was doing it in SQL would have been:

(CASE
WHEN TRIM(IFNULL(roiname, '')) IN ('', 'None') THEN CONCAT_WS('.',
CONVERT(roi_type_id, CHAR), roi_id)
WHEN CONCAT_WS('.', CONVERT(roi_type_id, CHAR), roi_id) = roiname THEN roiname
ELSE CONCAT_WS('.', CONVERT(roi_type_id, CHAR), roi_id, roiname)
END) REGEXP '%s'

But the table I am selecting from has 600,000 rows or more, and that
query would cause a table scan. So I did not add that to the existing
query, and instead iterated over the result set in python and did that
filtering.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CACwCsY5HPqPiogABwk1hrJgnwqaJ36VUR6tDF%3D%2Bde%3DXckqG%2BYQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.