0

I want to summarize all attendance records of each person every day，in my current solution, i need to get all records and then use for loop, is there a better way to get the queryset like the following:

class AttendanceRecord(models.Model):
    user_id = models.IntegerField()
    device_id = models.IntegerField()
    f_time = models.DateTimeField(auto_created=True)


# How to group record by user and get a queryset like the following?
[
    [
        {'user_id': 1, 'device_id': 1, 'f_time': '2020-11-11 8:00:00'},
        {'user_id': 1, 'device_id': 1, 'f_time': '2020-11-11 18:00:00'}
    ],
    [
        {'user_id': 2, 'device_id': 2, 'f_time': '2020-11-11 8:00:00'},
        {'user_id': 2, 'device_id': 2, 'f_time': '2020-11-11 18:00:00'}
    ],
]
# or like this
[
    {user_id1: [
        {'device_id': 1, 'f_time': '2020-11-11 8:00:00'},
        {'device_id': 1, 'f_time': '2020-11-11 18:00:00'}
    ]},
    {user_id2: [
        {'device_id': 2, 'f_time': '2020-11-11 8:00:00'},
        {'device_id': 2, 'f_time': '2020-11-11 18:00:00'}
    ]},
]
fitz
  • 540
  • 4
  • 11
  • Group by in Django orm is using values(). Check out the doc. – ha-neul Dec 11 '20 at 06:01
  • Check this out: https://stackoverflow.com/questions/629551/how-to-query-as-group-by-in-django It might help you. – manzt Dec 11 '20 at 06:44
  • @manzt Thanks for your help, it can't do this, because i need to get the detail info from grouped records, not just to use `Aggregate function` like count, sum. – fitz Dec 11 '20 at 07:16

1 Answers1

0

The dataset you need can be efficiently built with prefetch_related.

userinfo = UserInfo.objects.all().prefetch_related("attendancerecord_set")

for ui in userinfo:
    for ar in ui.addendancerecord_set.all():
        print(ar)

in template code

{% for ui in userinfo %}
    {% for ar in ui.addendancerecord_set.all %}
        f_Device_id:{{ ar.f_Device_id }} f_time:{{ ar.f_time }}
    {% endfor %}
{% endfor %}
Michael Lindsay
  • 1,290
  • 1
  • 8
  • 6
  • Thanks, this can be an alternative, but I hope to use the orm statement directly to achieve this purpose and avoid using `for loop` after having already used orm. – fitz Dec 11 '20 at 08:54