CODEWITHSUNDEEP
mysqldjangodjango-queryset
jalanga
jalanga

Reputation: 1576

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))
)

Upvotes: 0

Views: 73

Answers (1)

clichedmoog
clichedmoog

Reputation: 476

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)

Upvotes: 1

Related Questions