Django compares the values ​​of two objects

Question

I have a Django model that looks something like this:

class Response(models.Model): transcript = models.TextField(null=True) class Coding(models.Model): qid = models.CharField(max_length = 30) value = models.CharField(max_length = 200) response = models.ForeignKey(Response) coder = models.ForeignKey(User) 

For each Response object, there are two encoding objects with qid = "risk", one for encoder 3 and one for encoder 4. What I would like to do is get a list of all Response objects for which there is a difference in value between encoder 3 and encoder 4 is greater than 1. The value field stores the numbers 1-7.

I understand that setting the value as CharField may be a mistake, but I hope I can get around this.

I believe something like the following SQL will do what I'm looking for, but I would rather do it with ORM

 SELECT UNIQUE c1.response_id FROM coding c1, coding c2 WHERE c1.coder_id = 3 AND c2.coder_id = 4 AND c1.qid = "risk" AND c2.qid = "risk" AND c1.response_id = c2.response_id AND c1.value - c2.value > 1 

Answer 1

 from django.db.models import F qset = Coding.objects.filter(response__coding__value__gt=F('value') + 1, qid='risk', coder=4 ).extra(where=['T3.qid = %s', 'T3.coder_id = %s'], params=['risk', 3]) responses = [c.response for c in qset.select_related('response')] 

When you join a table already in the query, ORM will assign a second alias, in this case T3, which you can use in the parameters for extra() . To find out what an alias is, you can get into the shell and print qset.query .

See Django documentation on F and extra objects

Update: It doesn't seem like you need to use extra() or figure out which alias django is using, because every time you refer to response__coding in your searches, django will use the originally created alias. Here is one way to look for differences in any direction:

 from django.db.models import Q, F gt = Q(response__coding__value__gt=F('value') + 1) lt = Q(response__coding__value__lt=F('value') - 1) match = Q(response__coding__qid='risk', response__coding__coder=4) qset = Coding.objects.filter(match & (gt | lt), qid='risk', coder=3) responses = [c.response for c in qset.select_related('response')] 

See Django's Q Objects documentation

BTW. If you need both instances of the encoding, you have a problem with N + 1, because django select_related() will not get FK backward relationships. But since you already have the data in the request, you can get the necessary information using the T3 alias, as described above, and extra(select={'other_value':'T3.value'}) . The value data from the corresponding encoding record will be available as an attribute in the extracted encoding instance, that is, as c.other_value .

By the way, your question is quite general, but it looks like you have an entity-attribute-value attribute schema, which in an RDB script is usually considered an anti-pattern. You might be better off in the long run (and this query will be easier) with the risk field:

 class Coding(models.Model): response = models.ForeignKey(Response) coder = models.ForeignKey(User) risk = models.IntegerField() # other fields for other qid 'attribute' names...