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)?

"should be coercible", a bigint is not coeercible with a varchar. This merely is that a `BigInteger` will work with an `Integer` and vice versa. — Willem Van Onsem, Jul 05 '23 at 07:29
@WillemVOsupportsmodstrike: Thank you,but not according to the Django documention, which states: 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. For example, if you want to allow generic relations to models with either IntegerField or CharField primary key fields, you can use CharField for the “object_id” field on your model since integers can be coerced to strings by get_db_prep_value(). — Angelika Sajani, Jul 06 '23 at 14:11
Your own documentation part: "*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 it works if both fields have a `get_db_prep_value()` that for example produce a `str`, or a `datetime`, or an `int` or something equivalent. — Willem Van Onsem, Jul 06 '23 at 14:13
The object_id is a string, the primary keys are either strings or ints. ints can be coerced to strings. According to my reading of the docs this should work. Maybe the docs are just wrong? — Angelika Sajani, Jul 06 '23 at 14:16
you seem to think that this is about how the database coerces, but the "coerce" relation is defined in the next sentence: when `get_db_prep_value()` produces the same type, `str` and `int` are not the same type. — Willem Van Onsem, Jul 06 '23 at 14:18

score 0 · Answer 1 · answered Jul 06 '23 at 14:16

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.

score 0 · Answer 2 · answered Jul 07 '23 at 07:08

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.

Problem traversing generic relationship with different types of primary keys

2 Answers2