FieldError: Relation fields do not support nested lookups

24

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 ...     

    asked by anonymous 22.01.2016 / 19:10

    2 answers

    1

    It seems to me that your problem arises from the inheritance between models Tag and Entity .

    I think, given this structure of models, Django derives the following structure from tables:

    That is, inheritance between models Tag and Entity would generate two different but structurally equal tables.

    However, in the EntityClosure model, you only set FK's to Entity , not Tag . Then you could only use the dset related_name in the Entity model. Using it in Tag , as you are trying to do, generates the error that is displayed.

        
    14.09.2017 / 06:12
    0

    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 in front of the table name / field you want by alias .

    Always specify your join type, do not just join, always try to use inner, left, or any relational predecessor to specify in detail the type of relationship you are looking 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 is that the names of the data in your table and the way you do the relationship between them is pretty inconsistent leaving even the confused the understanding to third parties (for the servant obviously of course because he was the one who created ).

    I suggest that whenever you load a foreign key to another table, always put the table name + field you're loading , example:

    You have the user table, with categories that another table

    Then you would have the user table with the foreign key field category_id / id_category, never ever use generic names in its base, even though your query looks pretty normal and structured correctly, but in terms of reading , it is horrible, and django at the time of assembly is not able to correctly mount the relational correlates, I suggest you do other generic tests with structured tables to find out if the problem really is in the code or if the database is badly structured. p>     

    22.02.2017 / 14:02