django - Django ORM - Cast and filter a queryset

Question

I would like to cast a related column to integer and then filter a queryset, but I can not get it to work. These are my models

class Author(models.Model):
    name = models.CharField(max_length=255)
    ...


class Book(models.Model):
    title = models.CharField(max_length=255)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    ...


class MetricType(models.Model):
    label = models.CharField(max_length=255)


class Metrics(models.Model):
    book = models.ForeignKey(Book, on_delete=models.CASCADE, related_name="metrics")
    metric_type = models.ForeignKey(MetricType, on_delete=models.CASCADE)
    value = models.CharField(max_length=255)

For simplicity, let's suppose I have the following data. (This example is an oversimplification, but the problem is the same).

Author:
 - Name: Miguel de Cervantes
 - Name: William Shakespeare

Book:
 - Title: Don Quixote, Author: Miguel de Cervantes
 - Title: Hamlet, Author: William Shakespeare

MetricType:
 - Label: Pages

Metrics:
  - Book: Don Quixote, MetricType: Pages, value: 100
  - Book: Hamlet, MetricType: Pages, value: 40

Now, the only thing I want to do is get all the books that have more than N pages. This would be really easy to do in normal circumstances but in this case I need to cast the value of the metrics and then do the filter. I have tried the following:

from django.db.models import IntegerField
from django.db.models.functions import Cast
from . import models

value = 50

pages_metric = models.MetricType.objects.get(label='Pages')

models.Book.objects.annotate(
    pages=Cast('metrics__value', IntegerField()
).filter(
    metrics__metric_type=pages_metric, pages__gte=value
)

I'd like to get one book, the one that have 100 pages, but I get this nasty error instead:

(1055, "Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'XXXXX' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")

I have no access to modify my MySQL server settings and I can not modify the database schema for now since this is a legacy database. Is there anything I can do in Django to make this work?

标签： djangodjango-orm