Django+python: Use Order by to Django RawQuery objects?

Question

I have almost 100 million product names present in DB.I am displaying 100 products in UI each time & after scrolling showing next 100 & so on. For this I have used Django RawQuery as my database(mysql) doesn't support distinct functionality.

Here 'fetch' is callback function used in otherfile:

    def fetch(query_string, *query_args):        

                conn = connections['databaseName']
                with conn.cursor() as cursor:
                cursor.execute(query_string, query_args)
                record = dictfetchall(cursor)
                return record

Here is the main call in views.py So sample raw query code snippet:

record= fetch("select productname from abc")

Here if I am going to apply sorting criteria to the records

record= fetch("select productname from abc orderby name ASC")

Same doing for descending as well. As a result it takes so much time to display the sorted products.

What I want is like I will query 1 time & will store in a python object then will start applying ascending or descending.

So that for the first time when it loads, it will take some time but then applying sorting criteria it won't go to database to apply sorting for each time sort is hitted.

Overally want to say increase performance in case of sorting the records.

http://stackoverflow.com/questions/4183506/python-list-sort-in-descending-order — Usman Maqbool, Aug 19 '16 at 10:28

score 1 · Answer 1 · answered Aug 19 '16 at 10:37

I think what you are looking for is pagination. This is an essential technique when you want to display data in batches(pages).

from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger

def listing(request):
    query_string = 'your query'
    query_args = []
    conn = connections['databaseName']
    with conn.cursor() as cursor:
    cursor.execute(query_string, *query_args)
    all_records = dictfetchall(cursor)
    paginator = Paginator(all_records, 100) # Show 100 records per page

    page = request.GET.get('page')
    try:
        records = paginator.page(page)
    except PageNotAnInteger:
        # If page is not an integer, deliver first page.
        records = paginator.page(1)
    except EmptyPage:
        # If page is out of range (e.g. 9999), deliver last page of results.
        records = paginator.page(paginator.num_pages)

    return records

Whenever you make a request you should include the page you want to display (e.g. 1,2,3...) in the url parameters.

Example GET http://localhost/products/?page=1

In terms of logic, your javascript should display the first page and have a counter that hold the next page you should request, after the user scrolls make an AJAX request to get the second page and increase the page counter, etc...

EDIT: As for the sorting matter you can also use javascript to sort your data

score 0 · Accepted Answer · answered Aug 19 '16 at 11:51

Below is my try & I got the desired answer.

I fetch the data from database & is stored in the form of array of dictionary in a list.

Assuming the data stored format in a list:

l = [{'productName'='soap',id=1},{'productName'='Laptop',id=2}]

Below code snippet is the solution to sort dependening upon key:

from operator import itemgetter

for Ascending

res= sorted(l, key=itemgetter('name'))

For Descending

res= sorted(l, key=itemgetter('name'),reverse=True)

Django+python: Use Order by to Django RawQuery objects?

2 Answers2