Getting duplicated results when filtering on nested ForeignKeyField

[lelecanfora]

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

This is an example of the data model:

TABLE model_a

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

TABLE model_b

id
1b
2b

TABLE model_c

id
1c
2c

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"]

when I filter ModelC on the nested key:

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

I get 2 identical results, both with model_c.id = '1b', both with the same list of 2 activities

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

[Prof1-web]

Try:

print(ModelC.filter(models_a__model_b__id='1b')).prefetch_related('activities').sql())

What sql query does it gives? What sql query are you expecting?