Re: group by 3 fields

On Wed, Mar 1, 2017 at 8:01 AM, <marcin.j.nowak@gmail.com> wrote:
>
>>
>> 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.
>
>
> Queries like that aren't "compatible" with Django ;)
> You may also consider creating a view and map it to a unmanaged django
> model, but do not forget to add on_delete/on_update=DO_NOTHING for FKs.
> But I have no idea what Django migration system do with such mapped view - I
> kicked off builtin migrations completely and I am using Liquibase to manage
> dbs.

I had thought of using a view, but that would have been a lot of
overhead on such a large table. I also considered adding a column and
running a one time script to update the existing rows, and modifying
the script that loads data to populate the new column. But doing an
alter on such a large table takes longer then we can afford to have
the table locked for.

Also, we don't use django migrations on this project. We have found
them very hard to manage in an environment where you have 40
deployments, all with different versions of the code and database.

--
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/CACwCsY6QRAZ6YMWBgcMoEEvfF9nNre2zfzY4vC9H7nx4Cshh_g%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.