Misleading error "Filtering by relation is not possible. Filter by nested field of related model"

[iburadempa]

I had a ForeignKey m2 in a model M and wanted to M.filter(m2__id__isnull=None), but got the above error message. It took me some time to understand that I had to use .order_by(...), where ... does not contain m2, to get around the error. IMHO, the error should talk about fixing order_by().

Source line: https://github.com/tortoise/tortoise-orm/blob/8e3c294ef0cfcc577356b7864c79433cac1d2744/tortoise/queryset.py#L173

My version: tortoise-orm==0.16.13 (and using PostgreSQL)

[long2ice]

You can call M.filter(m2__isnull=True)

[iburadempa]

No, same error with M.filter(m2__isnull=True), if I do not add order_by(...). And if I do, I get Unknown filter param 'isnull'. Allowed base values are [...].

Both M.filter(m2_id__isnull=True) and M.filter(m2__id__isnull=True) work, if I add order_by(...).

The issue is really in method resolve_ordering.

[EternalSoul]

Got the same misleading error. The problem is when you try to order by FK field, then the error occures.

as an example

  class A(BaseModel):
      id = fields.BigIntField(pk=True)
      f = fields.Charfield()

  class B(BaseModel):
      id = fields.BigIntField(pk=True)
      a = fields.ForeignKeyField("A")

      class Meta:
          ordering=["a"]

In this case even calling await B.all() will lead to said error. Actually correct way of ordering in case of FK field would be ordering=["a_id"]

I don't think there's an error with the ordering itself, rather wording of an error is somewhat bad. Especially it forces you to think what you did wrong when you are coming from Django ORM where you can write both ways ['a'] and ['a_id']