Fielderror: Relation Fields do not support nested lookups

Asked

Viewed 517 times

24

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

  • You’re trying to access dset directly from Tag no relationship, or I’m wrong?

  • 2

    @Orion I don’t understand. dset is the related_name country EntityClosure.ancestor. I can access it normally (ex.: Tag.objects.filter(dset__descendant=4) returns all id tag ancestors 4). The problem is between the descendant and the entities according to my tests, but I could be wrong.

  • 1

    I didn’t know that inheriting created this relationship, so I wondered.

  • 1

    @Orion Yes. From what I could see, if a model Tag inherits from a model Entity, then the table representing the tag will have instead of a field id, a field entity_ptr or something like that, which is both a primary key and a foreign key to the mother table. Thus, all relations of other tables with the mother table are also applicable to the child model, since it is represented by a row in the mother table and a row in the daughter table (having all columns of both), with the same primary key.

  • @mgibsonbr, reading the last comment on the creation of the mother table, if you did not want this table to be created, you should put a Meta class: Abstract = True in Entity. In this case Django does not create the mother table and all fields of it would be in the tag table in your case. This would decrease the number of joins to be made. But I would have to review the modeling in Entityclosure that has the foreignkey for Entity

  • @Puamdias Unfortunately it is important to me that the Entity table exists (because it is elements of it that will receive the tag). And anyway my goal is not to reduce the number of joins (my real query has even more joins rsrs), but to solve this problem of not being able to mount the query. If I have no solution, beauty, I can live with two queries instead of one, but the ideal would be to make the whole query in one query (and as you can see in the example SQL, it is nothing of the other world...).

Show 1 more comment

2 answers

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:

inserir a descrição da imagem aqui

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.

  • Unfortunately, that’s not the problem. If one model is abstract and another model inherits from it, then yes, Django copies the base model structure for the derived model. But how Entity is a concrete model, the table relating to the model Tag does not contain fields name and details, only one field entity_ptr what reference Entity.id. And according to Django’s ORM, dset on the model Tag, for example doing Tag.objects.filter(dset__descendant=e) (that works as expected).

  • Anyway, I appreciate the suggestion, because you’re right about one thing - being the EntityClosure connected to Entity and not the Tag, the result of dset__descendant is a Entity, and maybe that’s why he can’t bear to call __entities soon after (since it is a relationship of Tag). Maybe the answer to the problem is right there...

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

  • But it was Django himself who created the structure of the tables, how can he be having trouble putting together what he himself created?! As for SQL, I think you don’t understand, this was just an example showing that the query I want to do is nothing much. The problem is that Django’s ORM is failing to make a similar query. My question refers to the specific error mentioned in the ORM, not in SQL. I appreciate your suggestions regarding code portability and nomenclature, but unfortunately the problem here is another.

  • 1

    I understood, in that case you have to do the queries manually to avoid these problems of the ORM, the other day was with the same problem in flask that he rode the queries "incorrectly" (due to many relationships), soon I had to do them manually.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.