Django - filtering on foreign key properties

Question

I'm trying to filter a table in Django based on the value of a particular field of a ForeignKey.

For example, I have two models:

class Asset(models.Model):
    name = models.TextField(max_length=150)
    project = models.ForeignKey('Project')

class Project(models.Model):
    name = models.TextField(max_length=150)

I'd like to filter my asset list based on the name of the associated project.

Currently, I am performing two queries:

project_list = Project.objects.filter(name__contains="Foo")
asset_list = Asset.objects.filter(desc__contains=filter,
                                  project__in=project_list).order_by('desc')

I'm wondering if there is a way to specify this kind of filtering in the main query?

I am confused, is it true that `project__in=project_list` should be `Project__in=project_list` (should it case sensitive) since you assign in model `Project` — Chau Loi, Feb 18 '21 at 09:00
@ChauLoi, no think of it like just another field of the Model. — Michael Higgins, Jul 26 '22 at 05:42

score 263 · Accepted Answer · edited Aug 13 '21 at 18:38

263

Asset.objects.filter( project__name__contains="Foo" )

edited Aug 13 '21 at 18:38

Jeremy Caney

7,102
69
48
77

answered Dec 30 '09 at 18:07

Fragsworth

33,919
27
84
97

1

Thanks, I had tried that but apparently I had forgotten to use double underscore. – Fraser Graham Dec 30 '09 at 18:13
4

is contains necessary?? – DeadDjangoDjoker Jul 11 '15 at 07:08
2

@DeadDjangoDjoker `contains` describes the type of comparison used in the query that the django ORM produces, the sql will probably look like `LIKE '%Foo%'`. – orangecaterpillar Apr 07 '20 at 00:43

score 24 · Answer 2 · edited Sep 10 '21 at 18:26

24

This has been possible since the queryset-refactor branch landed pre-1.0. Ticket 4088 exposed the problem. This should work:

Asset.objects.filter(
    desc__contains=filter,
    project__name__contains="Foo").order_by("desc")

The Django Many-to-one documentation has this and other examples of following Foreign Keys using the Model API.

edited Sep 10 '21 at 18:26

Phistrom

539
5
20

answered Dec 30 '09 at 18:07

Michael Greene

10,343
1
41
43

1

Is this going to hit the DB twice, should I be using select_related() to make this more optimal? – Fraser Graham Dec 30 '09 at 22:52
6

You can add a .query.as_sql() to see what sql will actually be executed. – fastmultiplication Jul 17 '10 at 05:19
the link to the django docs is completely out of date and lands on a '410 Page removed' :-/ – szeitlin Oct 09 '15 at 17:14

score 0 · Answer 3 · edited Nov 27 '18 at 20:12

student_user = User.objects.get(id=user_id)
available_subjects = Subject.objects.exclude(subject_grade__student__user=student_user) # My ans
enrolled_subjects = SubjectGrade.objects.filter(student__user=student_user)
context.update({'available_subjects': available_subjects, 'student_user': student_user, 
                'request':request, 'enrolled_subjects': enrolled_subjects})

In my application above, i assume that once a student is enrolled, a subject SubjectGrade instance will be created that contains the subject enrolled and the student himself/herself.

Subject and Student User model is a Foreign Key to the SubjectGrade Model.

In "available_subjects", i excluded all the subjects that are already enrolled by the current student_user by checking all subjectgrade instance that has "student" attribute as the current student_user

PS. Apologies in Advance if you can't still understand because of my explanation. This is the best explanation i Can Provide. Thank you so much

Django - filtering on foreign key properties

3 Answers3

Linked