Getting non unique results from Tortoise-ORM when filtering on nested ForeignKeyField

66 views Asked by At

I am confused by the behavior of tortoise orm when filtering on ForeignKeyField.

This is an example of the data model:

class ModelA(Model):
    id = UUIDField(pk=True)
    model_b: ForeignKeyRelation[ModelB] = ForeignKeyField(
        model_name="models.ModelB",
        related_name="models_a"
    )
    model_c: ForeignKeyRelation[ModelC] = ForeignKeyField(
        model_name="models.ModelC",
        related_name="models_a"
    )

class ModelB(Model):
    id = UUIDField(pk=True)
    models_a: ReverseRelation["ModelA"]


class ModelC(Model):
    id = UUIDField(pk=True)
    models_a: ReverseRelation["ModelA"]

and this is the table "models_a" in the database

id model_b_id model_c_id
1a 1b 1c
2a 1b 1c
3a 2b 2c

the combination of b and c might be duplicated (there are many more columns)

when I filter ModelC on the nested key:

model_c = await ModelC.filter(models_a__model_b__id='1b'))

print(len(model_c))

I get 2 ModelC with the same ID!!! one with ModelA = 1a and the other 2a

why is tortoise not returning the single ModelC with "models_a" as a list of ModelA as it should be?

0

There are 0 answers