CODEWITHSUNDEEP
pythondjangodjango-orm
Evgeniy Kirov
Evgeniy Kirov

Reputation: 534

Count rows of a subquery in Django 1.11

I have a couple models

class Order(models.Model):
    user = models.ForeignKey(User)

class Lot(models.Model):
    order = models.ForeignKey(Order)
    buyer = models.ForeignKey(User)

What I'm trying to do is to annotate Lot objects with a number of buys made by a given user to the same seller. (it's not a mistake, Order.user is really a seller). Like “you’ve bought 4 items from this user recently”.

The closest I get was

recent_sold_lots = Lot.objects.filter(
    order__user_id=OuterRef('order__user_id'),
    status=Lot.STATUS_SOLD,
    buyer_id=self.user_id,
    date_sold__gte=now() - timedelta(hours=24),
)

qs = Lot.objects.filter(
    status=Lot.STATUS_READY,
    date_ready__lte=now() - timedelta(seconds=self.lag)
).annotate(same_user_recent_buys=Count(Subquery(recent_sold_lots.values('id'))))

But it fails when recent_sold_lots count is more than one: more than one row returned by a subquery used as an expression.

.annotate(same_user_recent_buys=Subquery(recent_sold_lots.aggregate(Count('id'))) doesn't seem to work also: This queryset contains a reference to an outer query and may only be used in a subquery.

.annotate(same_user_recent_buys=Subquery(recent_sold_lots.annotate(c=Count('id')).values('c')) is giving me Expression contains mixed types. You must set output_field.. If I add output_field=models.IntegerField() to the subquery call, it throws more than one row returned by a subquery used as an expression.

I'm stuck with this one. I feel I'm close to the solution, but what am I missing here?

Upvotes: 2

Views: 2526

Answers (1)

firecast
firecast

Reputation: 1038

The models you defined in the question do not correctly reflect the query you are making. In any case i'll use the model as a reference to my query.

from django.db.models import Count

user_id = 123 # my user id and also the buyer
buyer = User.objects.get(pk=user_id)

Lot.objects.filter(buyer=buyer).values('order__user').annotate(unique_seller_order_count=Count('id'))

What the query does is:

  1. Filters the lot objects to the ones you have bought
  2. Groups the Returned lots into the user who created the order
  3. Annotates/Counts the responses for each group

Upvotes: 1

Related Questions