I'm trying to do a query in Django with several joins , and I came across this error that I do not know what it means:
Tag.objects.filter(dset__descendant__entities__entity=e)
FieldError: Relation fields do not support nested lookups
My templates in this query are Tag
, Entity
, TaggedEntity
and EntityClosure
(it's an EAV type template, full code here ). They represent the following:
Entity
is an entity. All it has are "name" and "details" fields:
class Entity(models.Model):
name = models.TextField(null=True, blank=True, default=None)
details = models.TextField(null=True, blank=True, default=None)
EntityClosure
is a relationship table (entities form a tree):
class EntityClosure(Model):
ancestor = models.ForeignKey(Entity, related_name="dset")
descendant = models.ForeignKey(Entity, related_name="aset")
depth = models.PositiveSmallIntegerField()
Tag
is a "tag" associated with an entity. Each entity can have multiple tags, and the tags themselves are also entities (i.e. they also form a tree):
class Tag(Entity):
pass
class TaggedEntity(Model):
entity = models.ForeignKey(Entity,related_name="tags")
tag = models.ForeignKey(Tag,related_name="entities")
My query is: "select all tags associated with an entity" ...
Tag.objects.filter(entities__entity=e) # Funciona
... but "taking into account the hierarchy" (i.e. also look for tags that are ancestors of tags associated with the entity):
Tag.objects.filter(dset__descendant__entities__entity=e) # Erro
I do not see anything wrong with this query ... but some problem is happening between descendant
and entities
that does not allow it to be done. The strange thing is that if I make two queries he accepts in a good way:
>>> conj = [x.ancestors for x in Tag.objects.filter(entities__entity=d).annotate(ancestors=F('aset__ancestor'))]
>>> conj
[3, 1, 3, 1]
>>> Tag.objects.filter(pk__in=conj)
[<Tag: TipoDocumento>, <Tag: Recibo Fiscal>]
Here is an MCVE , if you want to test (the actual code is much more extensive).
Also note that - as this example in SQLFiddle shows - the query I want to do is not nothing much, I just do not know why Django is having difficulties to mount it:
select t.*
from tagged_entity te
join entity_closure ec on te.tag = ec.descendant
join entity t on t.id = ec.ancestor
where te.entity = 1;
So ... what does this FieldError
mean, and how can I get around it to do what I want with a single query?
Update: After upgrading to Django 1.11 the error message has changed:
FieldError: Related Field got invalid lookup: entities
That's a bit more descriptive than the previous message. Possibly the error is in using __entities
- a relation of Tag
- soon after __descendant
- a relation of EntityClosure
- since EntityClosure
is not related to Tag
, but to Entity
. This would also explain why the two-query solution works: because the result of the first would be a set of entity ids, not tags ...