django annotate queryset with field comparison result

Question

I have a queryset like this:

predicts = Prediction.objects.select_related('match').filter(match_id=pk)

I need to annotate this with a new field is_correct. I need to compare two string fields and the result should be annotated in this new field. the fields that I want to compare are:

• predict from Prediction table
• result from Match table (that has been joined through select_related)

I need to know what expression should I put inside my annotate function; below I have my current code which throughs a TypeError exception:

predicts = predicts.annotate(is_correct=(F('predict') == F('result')))

all help will be greatly appreciated.

UPDATE:

I found an alternative solution that does the job for me (filtering the Prediction based on Match result using filter and exclude), but I still like to know how to address this specific case where the new annotated field is the result of the comparison between two other fields of the queryset. For those who may need it, in Django 2.2 and later the Nullif database function does a comparison between two fields.

Answer 1

You can use the extra function, a hook for injecting specific clauses into the SQL.

First of all, we must know the names of the apps and the models, or the name of the tables in the database.

Assuming that in your case, the two tables are called "app_prediction" and "app_match".

The sentence would be as follows:

Prediction.objects.select_related('match').extra(
    select={'is_correct': "app_prediction.predict = app_match.result"}
)

This will add a field called is_correct in your result, in the database, the fields and tables must be called in the same way.

It would be best to see the models.