django adding a column from a reverse one-to-many relationship on a queryset

Question

Hi i got a question lets say i got this models:

class Site():
   site_name = charfield

class Sample():
   site = foregeinkey
   state = decimalfield
   sample_date = datefield

and i need to assembly a table like this:

| site_name | latest_sample |
| site1     | 150           |

searching in the documentation an the internet link1 link2 i found annotate for when i need to adding some other characteristics to a query set and my new query is something like:

class ApiGetListOfSites(View):

    def get(self, request, format=None):

    objList = Site.objects.annotate(
        date_sample=Max('sample__sample_date'),
        valor_estado=F('sample__state')
    )
    json = serializers.serialize('json', objList)
    return HttpResponse(json, content_type='application/json')

i got the problem that i retrieve repeated objects and no new columns in the json. some one con help me with some suggestions on how i can do this.

Ps i think if i write the query in raw SQL i can used with the raw property of django models.

score 4 · Answer 1 · answered Oct 14 '18 at 09:49

4

This is a typical case to use Django Subqueries:

from django.db.models import OuterRef, Subquery

latest_samples = Sample.objects.filter(site=OuterRef('pk')).order_by('-sample_date')
obj_list = Site.objects.annotate(state=Subquery(latest_samples.values('state')[:1]))

answered Oct 14 '18 at 09:49

dirkgroten

20,112
2
29
42

thanks very much i know now what type of query's are those. but i don't understand why the serializer only serialize the data on the Site table and don't bring the new column. i m going to see if who to doit with django-rest-framwork – wecbxxx Oct 14 '18 at 16:33

robotHamster · Answer 2 · 2018-10-14T20:17:24.467

0

Making the table like this should work:

table=[]
for site in Site.objects.select_related().all():
    table.append({'site_name':site.site_name, 'latest_sample': site.sample_set.all().order_by('-pk')[0].state}

As pointed out by @dirkgrotten , using select_related() pre-populates the cache with the related objects so it's just one query to the db

Using select_related without an argument should preload all related objects, or you can use the related_name to fetch only what you need

edited Oct 14 '18 at 20:17

answered Oct 14 '18 at 09:05

robotHamster

609
1
7
24

You could also use the date field you have in `sample_date` – robotHamster Oct 14 '18 at 09:08
1

That works, except you get one db query for each `site`. In this case it would help if instead of `for site in Site.objects.all()` you'd do `for site in Site.objects.all().select_related('sample_set')` – dirkgroten Oct 14 '18 at 09:43

django adding a column from a reverse one-to-many relationship on a queryset

2 Answers2