jalanga jalanga - 3 months ago 5
MySQL Question

Django query in one to many

I have 2 tables,

User
and
Payment
with a relationship of One-TO-Many.

On payment I have the fields
date_updated
and
status
.

Can I make in one query, to get all the users which have payments with
date_updated < 3 months ago
and if has other payments bigger than 3 months ago than the status should be different than completed. If such payments are found on an user than the user should be returned else not.

For example I have the user with just one payment made last year, I want to be returned, but if the same user has another payment made recently than 3 months ago, and the status is completed than the user should not be returned.

What I'm trying:

User.objects.filter(
Q(orders__payments__date_updated__lte=time_x_months) &
Q(Q(orders__payments__date_updated__gte=time_x_months) &
~Q(orders__payments__status=Payment.STATUS_COMPLETED))
)

Answer

It might archived querying from Payment like this:

from django.db.models import Count, Q 
users = Payment.objects.filter(
    Q(date_updated__lte=time_until) &
    Q(date_updated__gte=time_from) &
    ~Q(status=Payment.STATUS_COMPLETED)
).values('user').annotate(count=Count('pk'))

Or just:

from django.db.models import Count 
users = Payment.objects.filter(
    date_updated__lte=time_until, 
    date_updated__gte=time_from
).exclude(
    status=Payment.STATUS_COMPLETED)
).values('user').annotate(count=Count('pk'))

(indentation might wrong)

Comments