Nesting queries that require a reference to the "parent" query

Using Django Using the ORM
1

I have two models:

class UserCredit(models.Model):
    belongs_to = models.ForeignKey(CustomUser, on_delete=models.PROTECT)
    credits = models.PositiveIntegerField(validators=[MinValueValidator(1)], null=False, blank=False) 
    price_paid = models.PositiveIntegerField(null=False, blank=False)
    expires = models.DateTimeField()
    credit_pool = models.UUIDField(default=uuid.uuid4, unique=True)
    created = models.DateTimeField(auto_now_add=True)

class Photo(models.Model):
    photo_file_name = models.CharField(max_length=1000)
    status = models.CharField(max_length=1000, null=True, blank=True)
    credit_pool = models.ForeignKey(UserCredit, on_delete=models.PROTECT)

I need to select all UserCredit instances where the sum of how many Photos have the credit_pool of the UserCredit are less than the credits in UserCredit.

I have these two queries

This one gets the count of how many photos have used a certain credit pool

Photo.objects.filter(credit_pool=id, status='success').count()

This one gets all the user credits that belong to a user and have not expired

UserCredit.objects.filter(belongs_to=user, expires__gte=current_date_time).order_by('expires', 'created')

How would I “mash” these two so I can get all the UserCredit objects for a particular user where the credits is greater than how many Photo objects reference the credit_pool in UserCredit?

2

Needing clarification-

You wrote:

Rephrasing to understand the requirement:
You want to select all UserCredit relating to a single CustomUser where the count (not “sum”) of Photos related to a UserCredit is less than the value of the credits field.

If I’m understanding you correctly, then you could do something like:
UserCredit.objects.filter(belongs_to=user, expires__gte=current_date_time).annotate(photo_count=Count('photo', filter=Q(photo__status='success'))).filter(credits__gt=photo_count).order_by('expires', 'created')

Disclaimer - I’m winging this a bit. It may work as is. It may not. Try it to see what’s going to happen. Don’t blame me if it deletes your entire database or changes your screen resolution to 640x480.

1 Like
3

@KenWhitesell that is exactly what I meant. When I try that, I get an error that “photo_count” is not defined. I have tried switching it up so it is

.filter(photo_count__lt=credits)

That did not work either. It gave an error

TypeError: int() argument must be a string, a bytes-like object or a number, not '_Printer'

How do I either get the query to recognize photo_count as defined or how do I get the UserCredit credits to be recognized as an integer?

4

Please show the complete query that you are trying. That filter is not going to work without the annotation prior to it adding that value to the entity.

Also,

should probably be:
.filter(credits__gt=F('photo_count'))

5

@KenWhitesell adding F to the photo_count variable made it work. Thank you so much

6

@KenWhitesell I have a follow up question if you don’t mind. What would be the best way to go about subtracting in a queryset. Say for example I want a queryset like this one you had

UserCredit.objects.filter(belongs_to=user, expires__gte=current_date_time).annotate(photo_count=Count('photo', filter=Q(photo__status='success'))).filter(credits__gt=photo_count).order_by('expires', 'created')

but I wanted the querset to return credits subtract photo_count?

7

Yes, you can perform (some) math within an annotation expression.

See Query expressions for some ideas and examples.