I used django for a model named Topics and the table data in the mysql database with default order value 1 like this:
| id | has_sub_topic | parent_id | subject_module_level | order |
|---|---|---|---|---|
| 1 | 0 | NULL | 1 | 1 |
| 2 | 0 | NULL | 2 | 1 |
| 3 | 0 | NULL | 3 | 1 |
| 27 | 1 | NULL | 25 | 1 |
| 28 | 0 | 27 | 25 | 1 |
| 29 | 0 | 27 | 25 | 1 |
| 31 | 1 | NULL | 25 | 1 |
| 32 | 0 | 31 | 25 | 1 |
| 33 | 0 | 31 | 25 | 1 |
| 34 | 0 | NULL | 25 | 1 |
| 40 | 1 | 27 | 25 | 1 |
| 41 | 1 | 40 | 25 | 1 |
| 42 | 0 | 41 | 25 | 1 |
| 43 | 0 | 40 | 25 | 1 |
| 44 | 1 | 40 | 25 | 1 |
| 45 | 0 | 44 | 25 | 1 |
| 47 | 1 | 44 | 25 | 1 |
| 48 | 0 | 47 | 25 | 1 |
I want to set the order data based on its subject_module and its parent. So, the data in the table would be like this:
| id | has_sub_topic | parent_id | subject_module_level | order |
|---|---|---|---|---|
| 1 | 0 | NULL | 1 | 1 |
| 2 | 0 | NULL | 2 | 1 |
| 3 | 0 | NULL | 3 | 1 |
| 27 | 1 | NULL | 25 | 1 |
| 28 | 0 | 27 | 25 | 1 |
| 29 | 0 | 27 | 25 | 2 |
| 31 | 1 | NULL | 25 | 2 |
| 32 | 0 | 31 | 25 | 1 |
| 33 | 0 | 31 | 25 | 2 |
| 34 | 0 | NULL | 25 | 3 |
| 40 | 1 | 27 | 25 | 3 |
| 41 | 1 | 40 | 25 | 1 |
| 42 | 0 | 41 | 25 | 1 |
| 43 | 0 | 40 | 25 | 2 |
| 44 | 1 | 40 | 25 | 3 |
| 45 | 0 | 44 | 25 | 1 |
| 47 | 1 | 44 | 25 | 2 |
| 48 | 0 | 47 | 25 | 1 |
Here the example data in pandas:
# Example data for topics
import pandas as pd
topics = pd.DataFrame({
'id': [1, 2, 3, 27, 28, 29, 31, 32, 33, 34, 40, 41, 42, 43, 44, 45, 47, 48],
'has_sub_topic': [0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 1, 0],
'parent_id': [None, None, None, None, 27, 27, None, 31, 31, None, 27, 40, 41, 40, 40, 44, 44, 47],
'subject_module_level_id': [1, 2, 3, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25],
'order': [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
})I have correctly set the order by this code:
topics = Topics.objects.all().order_by('order')
for topic in topics:
re_order_topics(topic)
def re_order_topics(topic):
sibling_order = 1
child_order = 1
siblings = Topics.objects.filter(
parent=topic.parent,
subject_module_level=topic.subject_module_level
).order_by('order')
if topic.has_sub_topic:
childs = Topics.objects.filter(parent=topic).order_by('order')
for child in childs:
child.order = child_order
child_order += 1
child.save()
re_order_topics(child)
if len(siblings) > 1:
for sibling in siblings:
sibling.order = sibling_order
sibling_order += 1
sibling.save()
else:
topic.order = 1
topic.save()But, as you can see my code is very inefficient because it set the order for topic that has been reorder more than once by the recursive and the first for-loop. If there are much more data in the database, the runtime will be very long because of this. Anyone know how to efficiently do this?