Django ORM filter() for multiple results

Question

I have the following models:

class DisputeAssignments(models.Model):
    case_id = models.ForeignKey(Case, on_delete=models.CASCADE, related_name='dispute_assigned_to')
    user_id = models.ForeignKey('users.User', on_delete=models.CASCADE, blank=False, null=False, related_name='assigned_disputes')
    assignment_date = models.DateTimeField()

class Case(models.Model):
    sn = models.CharField(max_length=100, verbose_name=_('Serial number'))
    user = models.ForeignKey('users.User', on_delete=models.PROTECT, related_name='cases')

class FullVector(models.Model):
    full_feature_vector = models.BinaryField()
    version = models.FloatField()
    processed_on = models.DateTimeField()
    case = models.ForeignKey(Case, on_delete=models.CASCADE, related_name='full_vectors')

class ShortVector(models.Model):
    id = models.BigAutoField(primary_key=True)
    detailed_fv_version = models.BinaryField()
    full_vector = models.ForeignKey(FullVector, on_delete=models.CASCADE, related_name='short_vectors')
    short_vector = models.BinaryField(null=True)

class Prediction(models.Model):
    id = models.BigAutoField(primary_key=True)
    short_vector = models.ForeignKey(ShortVector, on_delete=models.CASCADE, related_name='predictions', null=True, default=None)
    verif_req = models.SmallIntegerField(default=1)

Now, this relationship allows situations, where more than one entry from FullVector model points to the same Case entry. Moreover, there is always more than 1 ShortVector entry pointing to the same FullVector entry, and there is always one Prediction entry pointing to each ShortVector entry.

Example:
Case --> FullVector1 --> ShortVector1 --> Prediction1
     |               |-> ShortVector2 --> Prediction2
     |               |-> ShortVector3 --> Prediction3
     |               \-> ShortVector4 --> Prediction4
     |
     \-> FullVector2 --> ShortVector5 --> Prediction5
                     |-> ShortVector6 --> Prediction6
                     |-> ShortVector7 --> Prediction7
                     \-> ShortVector8 --> Prediction8

I need to write a Django ORM query, which for each DisputeAssignment entry will check, if ALL Prediction entries associated with it (through Case -> different FullVector and ShortVector entries), have verif_req value, which is greater than -1.

I started out with this:

DisputeAssignments.objects.filter(case_id__full_vectors__short_vectors__predictions__verif_req__gt=-1)

But the above approach fails, if e.g. FullVector1 related Predictions feature all 0s, while FullVector2 ones feature all -1s. I would expect that situation to result in omission of this DisputeAssignment entry, but it is left in the QuerySet due to FullVector1 related entries fulfilling requirement for verif_req > -1.

weAreStarsDust · Accepted Answer · 2020-05-19 06:54:04Z

1

I need to write a Django ORM query, which for each DisputeAssignment entry will check, if ALL Prediction entries associated with it (through Case -> different FullVector and ShortVector entries), have verif_req value, which is greater than -1.

You can get DisputeAssignments where verif_req is not <= -1 with Q

from django.db.models import Q

DisputeAssignments.objects.filter(~Q(case_id__full_vectors__short_vectors__predictions__verif_req__lte=-1))

answered May 19, 2020 at 6:54

weAreStarsDust

2,7503 gold badges14 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Greem666 Over a year ago

That worked beautifully, thank you. Can you explain please, how it works? In my mind, I needed a query which would recursively apply filter to every result. Django's webpage does not describe operation of the Q() expression as a recursive query, rather like a wrapper around different filter expressions, which can be chained with AND / OR operators.

weAreStarsDust Over a year ago

Q() expression also gives opportunity to use NOT with ~ operator when filtering. In your case it's just a filter with NOT condition. Complex lookups with Q objects

Greem666 Over a year ago

Um, ok - but what does ability to negate query results give us in my case? Sorry, I am having a total noob moment here, but I just don`t see how asking "which cases are linked with predictions which are less-than-equal -1" brings a correct result for my problem :(

weAreStarsDust Over a year ago

You are filtering DisputeAssignments objects, if one of related Case --> FullVector --> ShortVector --> Prediction of DisputeAssignments object doesn't match the condition, this DisputeAssignments object will not get into the filtering result, and so on.

Greem666 Over a year ago

Ah, got it now. Thanks! :)

Collectives™ on Stack Overflow

Django ORM filter() for multiple results

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related