Raw sql query as per URL parameters in django

Question

My models.py is as follows:

class Prescription(models.Model):
    date_prescribed = models.DateField()
    doctor = models.ForeignKey(Doctor)  
    pharmacy = models.ForeignKey(Pharmacy)

class Doctor(models.Model):
    name = models.CharField(max_length=100)
    age = models.IntegerField()

class Pharmacy(models.Model):
    name = models.CharField(max_length=100)
    status = models.Charfield(max_length=100)

What I want in my views is a queryset which finds the count of prescriptions grouped by month for the last six months.I am using raw_sql and my views.py is as follows:

from django.db import connection
from rest_framework import status
from rest_framework.decorators import api_view
from rest_framework.response import Response

@api_view(['GET'])
def prescription_trend_overview(request):

    query = '''
    select concat(year(rx.date_prescribed),'-',month(rx.date_prescribed)) as timeline,
    COUNT(rx.id) as total_prescriptions
    from myapp_prescription rx, myapp_doctor doctor, myapp_pharmacy pharmacy
    where pharmacy.id = rx.pharmacy_id and
    doctor.id = rx.doctor_id and
    rx.date_prescribed >= '2014-06-04' and
    rx.date_prescribed <= '2015-08-15'
    group by timeline
    order by year(rx.date_prescribed),
    month(rx.date_prescribed)
    '''

    try:
        cursor = connection.cursor()
        cursor.execute(query)
        descr = cursor.description
        rows = cursor.fetchall()
        result = [dict(zip([column[0] for column in descr], row)) for row in rows]

    finally:
        cursor.close()

    return Response(result, status=status.HTTP_200_OK)

Works really well and the output I get when I visit the url is as follows:

Currently the start and end date are hard coded and the prescription count is for all doctors and pharmacies.However,I need to now filter the results on the basis of 3 parameters:

1. Doctor
2. Pharmacy
3. Start_dat and End_date

I am trying to add these parameters in the URL like:

myapp/services/overview?doctor=John&pharmacy=Phizer&start_date=2015-7-28&end_date=2015-2-12

How do I capture these parameters and alter the SQL query dynamically on the basis of the URL parameters ?

Answer 1

The Rest framework provides get_queryset method. You can overide it and implement the new functionality you want to.

class PrescriptionTrendOverview(viewsets.ModelViewSet):
    queryset = Prescription.objects.all()
    serializer_class =  PrescriptionSerializer

    def get_queryset(self):
        queryset = super(PrescriptionTrendOverview, self).get_queryset()
        doctor_name = self.request.query_params.get('doctor', None)
        pharmacy_name = self.request.query_params.get('pharmacy', None)
        start_date = self.request.query_params.get('start_date', None)
        end_date = self.request.query_params.get('end_date', None)

        if doctor_name and pharmacy_name and start_date and end_date:
            # You can write the query set this way:
            queryset = queryset.filter(Q(doctor__name=doctor_name)
                                       & Q(pharmacy__name=pharmacy_name)
                                       & Q(date_prescribed__gte=start_date)
                                       & Q(date_prescribed__lte=end_date))

            # or this way
            queryset = queryset.filter(doctor__name = doctor_name,
                                       pharmacy__name = pharmacy_name,
                                       ate_prescribed__gte=start_date,
                                       date_prescribed__lte=end_date)
        return queryset