django sort the query elements in a weekly monthly daily fashion

Question

I am getting a list of people and the tests they have taken from an api in the same project. I would like the user to have an option to see the number of tests that have taken place in a city with three options - daily/weekly/monthly.

models.py

class City(models.Model):
    city_name=models.CharField(max_length=100,default='',blank=False)

class Person(models.Model):
    title = models.CharField(max_length=3,default="mr",blank=False)
    name = models.CharField(max_length=50,default='',blank=False)
    address = models.CharField(max_length=200,default='',blank=False)
    city = models.ForeignKey(City)
class Test(models.Model):
    person = models.ForeignKey(Person)
    date = models.DateTimeField(auto_now_add=True)
    test_name = models.CharField(max_length=200,default='',blank=False)
    subject = models.CharField(max_length=100,default='')

The json file for people in each city is generated like this http://pastebin.com/pYLBjrjh

related views.py

def personlist(request, id):
data = requests.get('http://127.0.0.1:8000/app/cities/' + id + '/persons/').json()
context = RequestContext(request, {
    'persons': data['results'],'count': data['count'],
})

return render_to_response('template.html', context)

This generates the list of names in that city that have taken the test. I want to know if there is a way to let the user select the time period.(daily/weekly/monthly) and only view the names that have taken the test in that time period.

Dhia · Accepted Answer · 2015-12-16T13:00:28.657

In order to have access to test through Person Model you can define your own related_name such as tests or just use the default provide by django test_set:

class Test(models.Model):
    person = models.ForeignKey(Person, related_name='tests')
    date = models.DateTimeField(auto_now_add=True)
    test_name = models.CharField(max_length=200,default='',blank=False)
    subject = models.CharField(max_length=100,default='')

related_name define a reverse relation so you can do the following query:

  person_1.tests.all()
  person_1.tests.filter(date=date)
  person_1.tests.filter(date__day=day) # day is an integer
  person_1.tests.filter(date__month=month) # month is an integer

but there is no default support for week, but you still can do it using range feature:

import datetime
start_date = datetime.date(2015, 12, 16)
end_date = start_date + datetime.timedelta(days=7)
person_1.tests.filter(date__range=(start_date, end_date))

Example :

in views.py:

def get_current_month():
    now = datetime.now()
    return now.strftime("%m")

def get_current_year():
    now = datetime.now()
    return now.strftime("%y")

def tests_by_month(request, id):
    person = Person.objects.get(id=id)
    month = request.GET.get('month', get_current_month()) # if no month take the current as default
    year = request.GET.get('month', get_current_year()) # if no year take the current as default
    test_list = person.tests.filter(date__month=int(month), date__year=int(year))
    context = {'test_list': test_list}
    return render_to_response(request, 'template.html', context)

in urls.py :

url(r'^app/persons/(?P<id>\d+)/tests/', tests_by_month),

and you use the following url

app/persons/(?P<id>\d+)/tests/?month=12&year=2014

if you send without month: app/persons/(?P<id>\d+)/tests/ you will get the tests of the current month and current year

You don't *need* a related name, you could always just use `person.test_set` — Sayse, Dec 16 '15 at 09:17
You do not have to specify the related_name as long as there is only one direct relationship between the models. The default related name is simply ```test_set```in this case. — Tim, Dec 16 '15 at 09:18
yeah it's not obligatory, but jut for more readabilty it's always better. I update the post. — Dhia, Dec 16 '15 at 09:21
@DhiaTN Could you add an example of a view function that uses the above method to return a list of people tested this month? — WutWut, Dec 16 '15 at 09:34
Thanks. That helps a lot. Although, I just wanted to display the names of the people based on the date added and you've used tests. But, I get the concept. — WutWut, Dec 16 '15 at 10:03

User Rik · Answer 2 · 2015-12-16T12:15:38.573

2

This is an addition to the answer above as it does not support years which means the tests data would not be accurate if the data accumulates over the years. For example if there is 2014 and 2015 January data, then getting the data by month would return both 2015 and 2014 january data.

Solution

Supply the year also in the request and filter using year in the model also

def tests_by_month(request, id):
    person = Person.objects.get(id=id)
    month = request.GET.get('month', get_current_month()) 
    year = request.GET.get('year', datetime.now().year)
    test_list = person.tests.filter(date__month=int(month),   date__year=year)
    context = {'test_list': test_list}
    return render_to_response(request, 'template.html', context)

edited Dec 16 '15 at 12:15

answered Dec 16 '15 at 11:09

User Rik

106
5

Can you give an example of how this would be used to return the list of names depending on date added and not tests. Thanks. – WutWut Dec 16 '15 at 11:21
Since the person object is already in the test object, we could use it to return only the person names using [django's values](https://docs.djangoproject.com/en/1.9/ref/models/querysets/#django.db.models.query.QuerySet.values) like so test_list = person.tests.filter(date__month=int(month), date__year=year).values('person__name') – User Rik Dec 16 '15 at 12:11

django sort the query elements in a weekly monthly daily fashion

2 Answers2

Linked