Filtering based on a foreign key in Django

Question

I'm trying to filter a table based on a value in another table, via a foreign key.

trench_id = models.ForeignKey(Trench, db_column='trench_id', on_delete = models.PROTECT)

As above, the Context model joins to the Trench model via trench_id__trench_id I want to access trench.name as you can see below I'm then using this value in a filter. I include the views.py code and for reference my models.py.

def allcontexts(request):
    allcontexts = Context.objects.filter(trench_id__trench_id__name=request.session.get("name"))
    return render(request, 'context_manager/context_manager.html',
    {
    'allcontexts':allcontexts,
    })

I'm getting the following error Unsupported lookup 'name' for AutoField or join on the field not permitted.

models.py

class Trench(models.Model):
    trench_id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=50)
    area_easting = models.IntegerField()
    area_northing = models.IntegerField()

    def __str__(self):
        return str(self.name)

class Context(models.Model):
    context_id = models.AutoField(primary_key=True)
    trench_id = models.ForeignKey(Trench, db_column='trench_id', on_delete = models.PROTECT)
    number = models.IntegerField()
    type = models.CharField(max_length = 50, blank=True, null=True)
    description = models.TextField(blank=True, null=True)
    excavation_method = models.CharField(max_length = 50, blank=True, null=True)
    open_date = models.DateField(blank=True, null=True)
    close_date = models.DateField(blank=True, null=True)
    excavated_by = models.CharField(max_length = 50, blank=True, null=True)
    area_easting = models.IntegerField()

    def __str__(self):
        return str(self.number)

Extra troubleshooting

Here's the sql that is being generated, it's not looking in the trench table.

queryset = Context.objects.filter(trench_id__trench_id=4)

print(queryset.query)

SELECT "kap"."context"."context_id", "kap"."context"."trench_id", "kap"."context"."number", "kap"."context"."type", "kap"."context"."description", "kap"."context"."excavation_method", "kap"."context"."open_date", "kap"."context"."close_date", "kap"."context"."excavated_by", "kap"."context"."area_easting" 
FROM "kap"."context" 
WHERE "kap"."context"."trench_id" = 4 ORDER BY "kap"."context"."number" ASC

score 1 · Accepted Answer · answered Jan 14 '20 at 18:31

1

Looks to me like it should just be Context.objects.filter(trench_id__name=request.session.get("name")). As written, you're asking for the name attribute of the trench_id AutoField on the Trench model - which of course doesn't exist.

answered Jan 14 '20 at 18:31

Peter DeGlopper

36,326
7
90
83

Currently the page displays, but no data. I can filter on any field in the Contexts model. I've reduced the problem to the manual basics `allcontexts = Context.objects.filter(trench_id__trench_id=4)` nothing displays. In the html I'm using `{% for context in allcontexts %} {{ context.number }} {{ endfor }}` I'm sure I've made a basic error somewhere. – Spatial Digger Jan 14 '20 at 19:10
Well...your FKs are set up in a nonstandard manner, that's not _helping_. The usual idiom would be to name the field `trench` rather than `trench_id` and not to declare the db column explicitly - behind the scenes Django will create the `_id` fields. Then you query as `Context.objects.filter(trench__name="foo")` or (if you actually know the ID of the related object) `Context.objects.filter(trench_id=4)` - the latter of which is equivalent to `.filter(trench__trench_id=4)` but doesn't do the join. – Peter DeGlopper Jan 14 '20 at 19:15
You also wouldn't usually declare your own `AutoField` primary key fields. I don't think either of these nonstandard declarations are breaking things but they push your code away from, say, being able to follow examples in the docs. – Peter DeGlopper Jan 14 '20 at 19:19
I understand that Django automatically creates id columns if they are omitted, but I'm building this on top of a pre-existing PostgreSQL database, so I'm managing them directly. I also know the id numbers, hence `trench_id=4`. – Spatial Digger Jan 14 '20 at 19:27
OK - I would rename the field to just `trench` then, but leave the PK field. You can keep the db_column declaration in the `ForeignKey` call or not, the default value would also be "trench_id". Django expects to be able to create a distinct accessor on the `Context` model class to get the raw FK number as well as using the field you declared as a field for getting the linked instances. – Peter DeGlopper Jan 14 '20 at 19:33
`allcontexts = Context.objects.filter(number=1)` works without issue, but that's filtering within the context table and results in all the contexts with id=1 rather than just those from the single trench. I've tried numerous versions of the `trench_id=4` - `trench_id__trench_id=4` etc. Conceptually there is a trench, within the trench are many contexts, so I'm navigating from the contexts table/object to the trench table/object and then filtering based on that relationship. The field will remain trench_id, otherwise it becomes ambiguous and might be mistaken for the name of the trench. – Spatial Digger Jan 14 '20 at 19:36
Could it be a direction issue? Rather than going from the Trench model to the Context model I'm doing the reverse, I have a vague recollection that this causes issues? – Spatial Digger Jan 14 '20 at 19:45
The direction of the relationship sounds right to me given "within the trench there are many contexts" - you typically model a many-to-one relationship in Django with a FK field on the many side of that relationship, as you have here. `__` notation on a FK field implies a join - `Context.objects.filter(trench_id__name="foo")` should be (basically) `select * from context join trench on context.trench_id = trench.trench_id where trench.name = "foo"` – Peter DeGlopper Jan 14 '20 at 19:48
Direct queries on the PK of `trench` are likely to look weird given that you're not following the Django convention for naming foreign key fields - `Context.objects.filter(trench_id_id=4)` probably equates to `select * from context where trench_id = 4` while `filter(trench_id__trench_id=4)` equates to `select * from context join trench on context.trench_id = trench.trench_id where trench.trench_id = 4` – Peter DeGlopper Jan 14 '20 at 19:50
The latter SQL is correct `select * from context join trench on context.trench_id = trench.trench_id where trench.trench_id = 4`, but Django seems to give me an empty queryset, I'm going to try to pass it in the shell and see if any errors arise. – Spatial Digger Jan 14 '20 at 19:55
See this answer for a way to see the raw SQL backing a queryset - you can use eg `django admin shell` to inspect it interactively. https://stackoverflow.com/questions/3748295/getting-the-sql-from-a-django-queryset – Peter DeGlopper Jan 14 '20 at 19:57
I've added the raw sql above, it's not showing anything from the trench table and returns an empty queryset. – Spatial Digger Jan 14 '20 at 20:10
Well, that is logically equivalent (no need to do the join when the only constraint is on the join field) - what do you get for a `trench_id__name=...` query? – Peter DeGlopper Jan 14 '20 at 20:19
An empty queryset, but it has a join! `SELECT "kap"."context"."context_id", "kap"."context"."trench_id", "kap"."context"."number", "kap"."context"."type", "kap"."context"."description", "kap"."context"."excavation_method", "kap"."context"."open_date", "kap"."context"."close_date", "kap"."context"."excavated_by", "kap"."context"."area_easting" FROM "kap"."context" INNER JOIN "kap"."trench" ON ("kap"."context"."trench_id" = "kap"."trench"."trench_id") WHERE "kap"."trench"."name" = 81.551 ORDER BY "kap"."context"."number" ASC` – Spatial Digger Jan 14 '20 at 20:22
`allcontexts = Context.objects.filter(trench_id__trench_id=request.session.get("trench_id"))` worked, for some reason the trench_id had not been populated during the automated migration, sorry for wasting your time and thanks. – Spatial Digger Jan 14 '20 at 20:52

Filtering based on a foreign key in Django

1 Answers1