CODEWITHSUNDEEP
pythondjangopython-2.7django-modelsdjango-views
dahyunism
dahyunism

Reputation: 55

Django reverse foreign key

I'm using python/django for 1 month now and I need help because I can't find any similar questions that has the answer I was looking for. I have Worker model and Job model. A Worker can have many jobs. I need to query the latest job that the Worker has.

class Worker(models.Model):
   name = models.CharField(max_length=128)

class Job(models.Model):
   worker = models.ForeignKey(Worker)
   type = models.CharField(max_length=64)
   position = models.CharField(max_length=64)
   location = models.CharField(max_length=128)
   start_date = models.DateField()

I am currently using this sample code. The problem is that this returns similar Worker objects including its previous jobs. I just want to query the Worker with the latest job location and start date.

for employee in Job.objects.all():
   print(employee.worker, employee.location, employee.start_date)

Sample Output

(A, north, 2018-01-21)
(A, south, 2018-09-13)
(A, east, 2019-05-11)
(B, west, 2019-01-01)

Is there a way to use a for loop to a query like Worker.job_set.all() to have this expected output

(A, east, 2019-05-11)
(B, west, 2019-01-01)

Hope you can help a newbie like me. Thank you in advance! :)

Upvotes: 1

Views: 1988

Answers (3)

Javier Buzzi
Javier Buzzi

Reputation: 6838

I think the other two answers are very simplistic. Both of them are tackling the problem you are trying to accomplish now, but these solutions will fail once you have more and more workers/jobs since all their solutions are O(N*N). This solution is O(N).

    subqry = models.Subquery(Job.objects.filter(worker_id=models.OuterRef('worker_id'))
                                        .order_by('-start_date').values('id')[:1])
    workers = Worker.objects.prefetch_related(models.Prefetch('job_set',
                                                              queryset=Job.objects.filter(id__in=subqry)))

    for worker in workers:
        # no matter what this will always have 1 // or nothing, depending on your logic; if nothing, fix this.
        latest_job = list(worker.job_set.all())[0]  
        print(worker.name, latest_job.location, latest_job.start_date)

This will do a single query for Worker like the rest, BUT will only do a single query for the latest jobs, the other solutions will make a query PER worker, this is inefficient and slow.

See this example for more background as how i tested all this. https://gist.github.com/kingbuzzman/ac2ada9c27196fc90c1b75f2d01a6271#file-django_prefetch_limit-py-L163

Upvotes: 2

Håken Lid
Håken Lid

Reputation: 23084

You can perform ordering on the job_set queryset, for example. Is something like this what you are looking for?

for worker in Worker.objects.all():
     latest_job = worker.job_set.latest('start_date')
     print(worker.name, latest_job.location, latest_job.start_date)

Upvotes: 1

heemayl
heemayl

Reputation: 42137

You can use the last method on the filtered and ordered (by start_date, in ascending order) queryset of a worker.

For example, if the name of the worker is foobar, you can do:

Job.objects.filter(worker__name='foobar').order_by('start_date').last()

this will give you the last Job (based on start_date) of the worker named foobar.

FWIW you can also get the first element if you're sorting in the descending order of start_date:

Job.objects.filter(worker__name='foobar').order_by('-start_date').first()

Upvotes: 0

Related Questions