Fielderror: Relation Fields do not support nested lookups

Question

I’m trying to make a query in Django with several joins, and I came across this mistake that I don’t know what it means:

Tag.objects.filter(dset__descendant__entities__entity=e)

Fielderror: Relation Fields do not support nested lookups

My models on this query sane Tag, Entity, TaggedEntity and EntityClosure (is an EAV model, full code here). They represent the following:

1. Entity is an entity. All it has are "name" and "details":

   class Entity(models.Model):
       name = models.TextField(null=True, blank=True, default=None)
       details = models.TextField(null=True, blank=True, default=None)
   

2. EntityClosure is a table of relationships (entities form a tree):

   class EntityClosure(Model):
       ancestor = models.ForeignKey(Entity, related_name="dset")
       descendant = models.ForeignKey(Entity, related_name="aset")
       depth = models.PositiveSmallIntegerField()
   

3. Tag is a "tag" associated with an entity. Each entity can have multiple tags, and the tags themselves are 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 desired is: "select all tags associated with an entity"...

Tag.objects.filter(entities__entity=e) # Funciona

..."but taking into account the hierarchy" (i.e. also search for tags that are ancestors of tags associated with the entity):

Tag.objects.filter(dset__descendant__entities__entity=e) # Erro

I don’t see anything wrong with this query... but some problem is happening between the descendant and the entities which does not allow it to be done. The strange thing is that if I do two queries he accepts in a good:

>>> 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 a MCVE, if you want to test (the actual code is much more extensive).

Note also that - as this example in Sqlfiddle shows - the query I want to do is nothing big, I just don’t know why Django is having trouble putting it together:

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 mean FieldError presented, and how can I bypass it to do what I want with a single query?

---

Updating: after upgrading to Django 1.11 the error message has changed:

Fielderror: Related Field got invalid lookup: entities

Which is a bit more descriptive than the previous message. Possibly the error is in using __entities - a relationship of Tag - shortly after __descendant - a relationship of EntityClosure - since EntityClosure does not relate to Tag, but rather with Entity. This would also explain why the two-way solution works: because the result of the first would be a set of entities, not of tags...

Answer 1

It seems to me that your problem arises from inheritance among models Tagand Entity.

I think, given this model structure, Django derives the following table structure:

That is, the legacy among models Tag and Entity would generate two different but structurally equal tables.

However, in the model EntityClosure, you just define FK’s to Entity, and not to Tag. Then you could only use the related_name dset in the model Entity. Use it in the Tag, as you are trying to do, generates the error presented.

Answer 2

Try not to use alias in your joins because depending on your database engine or database version it does not apply to correlates. If you use alias always put the syntax as in front of the table name / field you want to put the alias.

Always specify your type of Join, don’t just give Join, always try using Inner, left, or any relational predecessor to specify in detail the type of relationship you search for in your query.

Example:

   select t.*
from tagged_entity
  inner join entity_closure on tagged_entity.tag = entity_closure.descendant
  join entity as t on t.id = tagged_entity.ancestor
where tagged_entity.entity = 1;

Another thing I noticed that the names of the data in your table and the way you make the relationship between them is quite inconsistent leaving even the confused understanding to third parties (for the created obviously of course, because it was he who created).

I suggest you always carry some Foreign key to another table always put the table name + field you are loading, example:

You have the user table, with categories other table

Then you would have the user table with the field Foreign key category_id / id_category, never never use generic names at its base, Even though your query looks very normal and structured correctly, but in terms of reading, it’s awful, and Jango at the moment of assembly is not able to properly assemble the relational correlates, I suggest you do other generic tests with structured tables to find out if the problem in the code really or if the database is poorly structured.