django sort the query elements in a weekly monthly

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.

Answer 1

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

Answer 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)