Problem traversing generic relationship with different types of primary keys

Question

I have the following models (greatly simplified)

class Species(models.Model):
    # automatic primary key: id, bigint
    label = models.CharField(
        null=True, blank=True,
        max_length=LABEL_LENGTH
    ) 
    photos = GenericRelation(Photo, related_query_name='species')
     
class FieldNotes(models.Model)
    fulcrum_id = models.CharField(
        max_length = EXTERNAL_ID_LENGTH,
        primary_key=True
    )
    scan_name = models.CharField(
        max_length=LABEL_LENGTH,
    ) 
    photos = GenericRelation(Photo, related_query_name='species')

class Media(models.Model):
    # automatic primary key: id, bigint

    caption = models.TextField(
        blank=True, default='',
    )

    object_id = models.CharField(
        max_length = EXTERNAL_ID_LENGTH,
    )
    content_object = GenericForeignKey('content_type', 'object_id')

    class Meta:
        abstract = True

class Photo(Media):
    photo= models.ImageField(
        upload_to ='photo/%Y/%m/',    
    )
    content_type = models.ForeignKey(ContentType, on_delete=models.CASCADE,
        related_name="photos")

class Video(Media):
    video = models.FileField(
        null=True, blank=True,
        upload_to='videos/', )
    content_type = models.ForeignKey(ContentType, on_delete=models.CASCADE, 
          related_name="videos")

This works generally fine, but when I want to query for example Species that have a photo with a certain caption

qs = Species.objects.filter(photos__caption='abc')

I get the following error:

django.db.utils.ProgrammingError: operator does not exist: bigint = character varying

Obviously this comes from the mismatch between foreign key and primary key types, varchar vs bigint.

If I do the same query for model FieldNotes it works

qs = FieldNotes.objects.filter(photos__caption='abc')

Question: Django documentation (https://docs.djangoproject.com/en/4.2/ref/contrib/contenttypes/) states that:

Primary key type compatibility

The “object_id” field doesn’t have to be the same type as the primary key fields on the related models, but their primary key values must be coercible to the same type as the “object_id” field by its get_db_prep_value() method.

So this should work, why doesn't it? Is there a way I make it work without changing the primary key type in FieldNotes (and other similar models)?

Answer 1

You seem to have misread the Django documentation you cite:

Primary key type compatibility

The “object_id” field doesn’t have to be the same type as the primary key fields on the related models, but their primary key values must be coercible to the same type as the “object_id” field by its get_db_prep_value() method..

This thus means that for example an IntegerField and a BigIntegerField because the get_db_prep_value(), the value that Django uses to convert a value to a parameter in an SQL query, will produce for both an int. This will not work for a CharField and an IntegerField (which is essentially the problem here): a CharField produces a str for the get_db_prep_value() whereas an IntegerField produces an int, hence the two do not coerce.

Answer 2

It seems this is a limitation of Django's generic relationships.

Queries like

qs = FieldNotes.objects.filter(photos__caption__icontains='abc')

work, but only if the types of foreign keys and primary keys match

However:

one_species.photos.count() etc

*work even if the primary key in Species is an integer. This is because the type-mismatch, even without explicit coercion, is resolved by the SQL engine:

>>> from scans.models.fieldnotes_species import FieldNotes_Species 
    as Species
>>> one_species = Species.objects_for_count.get(id=6720)
>>> qs1 = one_species.photos.all()
>>> print(qs1.query)
SELECT "core_photo"."id", "core_photo"."external_id", 
...
"core_photo"."signature_timestamp", "core_photo"."content_type_id" 
FROM "core_photo" WHERE ("core_photo"."content_type_id" = 137 
AND "core_photo"."object_id" = 6720)
>>> qs1.count()
3
>>> 

However, in the context of doing a filter on a to-many relationship, this will result in a join, and for the join condition the type-mismatch is not tolerated by the SQL engine.

>>> qs = Species.objects.filter(photos__caption__icontains='a')
>>> print(qs.query)
SELECT "scans_fieldnotes_species"."id",
 ...
"scans_fieldnotes_species"."cover_class_braun_blanquet_id" 
 FROM "scans_fieldnotes_species" INNER JOIN "core_photo" 
ON ("scans_fieldnotes_species"."id" = "core_photo"."object_id" 
AND ("core_photo"."content_type_id" = 137)) 
WHERE UPPER("core_photo"."caption"::text) LIKE UPPER(%a%)

I think that is something that could be clearer in the documentation, if that is a known limitation.