Django database query: How to filter objects by date to warn user if >= 8 hours?

Question

I've got a field in one model like

class Project(models.Model):
    date = fields.DateField(auto_now=False)
    user = models.ManyToManyField(User, related_name="projects", blank=True)
    work_times = models.FloatField(default=1, verbose_name="work times(hours)")

Now, I would like to filter a user's work_times(different value) by a date(same date), the purpose is to warn or remind users when they create new project, because user can't have new project if his projects' work_times >= 8 hours on a day, I already got values as below

[{'work_times': 1.0, 'date': datetime.date(2018, 6, 25)}, {'work_times': 1.0, 'date': datetime.date(2018, 6, 28)}, {'work_times': 1.0, 'date': datetime.date(2018, 6, 28)}, {'work_times': 1.0, 'date': datetime.date(2018, 6, 28)}, {'work_times': 1.0, 'date': datetime.date(2018, 6, 28)}, {'work_times': 1.0, 'date': datetime.date(2018, 6, 28)}, {'work_times': 1.0, 'date': datetime.date(2018, 6, 28)}, {'work_times': 1.0, 'date': datetime.date(2018, 6, 28)}, {'work_times': 1.0, 'date': datetime.date(2018, 6, 28)}, {'work_times': 1.0, 'date': datetime.date(2018, 6, 28)}, {'work_times': 1.0, 'date': datetime.date(2018, 6, 28)}, {'work_times': 1.0, 'date': datetime.date(2018, 6, 28)}, {'work_times': 1.0, 'date': datetime.date(2018, 6, 28)}]

one user has several projects with different work_times on a date, how to filter user A's total work_times on a date in his all projects?

Thanks for your help!

Take a look at this question (but use Sum instead of Count): https://stackoverflow.com/questions/629551/how-to-query-as-group-by-in-django — thebjorn, Aug 15 '18 at 08:53

neverwalkaloner · Accepted Answer · 2018-08-15T09:25:19.190

1

Try to use annotation like this:

from django.db.models import Sum, Q
User.objects.annotate(total_times=Sum('projects__work_times', filter=Q(projects__date=some_date))).filter(total_times__gt=8)

edited Aug 15 '18 at 09:25

answered Aug 15 '18 at 08:57

neverwalkaloner

46,181
7
92
100

Hi, thank you, but there is no `total_times` in User model, I think I need to filter it dynamically firstly? – Elsa Aug 15 '18 at 09:00
@Begin2Pip total_times will be annotated with `annotate` method. – neverwalkaloner Aug 15 '18 at 09:01
Hi, I will try it right now, thank you so much, I also need to filter them by the day, not all projects on all dates, one day >= 8 hours – Elsa Aug 15 '18 at 09:05
if you have `create_on/updated_on` column in your table you can filter via `date` – Akhilendra Aug 15 '18 at 09:08
Do you mean add_time for project model? – Elsa Aug 15 '18 at 09:09
I still don't know how to filter via `date`, how to define `date`? Thank you so much for your great help – Elsa Aug 15 '18 at 09:20
@Begin2Pip try to use Sum's `filter` argument. See updates. – neverwalkaloner Aug 15 '18 at 09:25
1

Thank you so much for your help, I must learn from you Sir, hope to be your friendsI will find you!!!! Thanks and best regards, Elsa from China – Elsa Aug 15 '18 at 09:28

Django database query: How to filter objects by date to warn user if >= 8 hours?

1 Answers1