Last active
January 20, 2021 18:32
-
-
Save roozbehsam/05a696bc08567e48dc57510cc782afba to your computer and use it in GitHub Desktop.
Django + MongoDB DataTables (Jquery datatables v1.9 used)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
from collections import namedtuple | |
import pymongo | |
""" | |
(Jquery datatables v1.9&below - pymongo v3.11.0 - python v3.8 - mongodb v4.0.6 - django v2.2) | |
$('#table').dataTable({ | |
"sAjaxSource": "/route_name" // USE AJAX_SOURCE TO SWITCH TO V1.9 AUTOMATICALLY, even if you are using latest version! | |
}); | |
""" | |
# translation for sorting between datatables api and mongodb | |
order_dict = {'asc': 1, 'desc': -1} | |
client = pymongo.MongoClient('localhost', 27017) | |
db = client[' DATABASE NAME '] | |
class DataTables(object): | |
def __init__(self, request, columns, index, collection): | |
self.columns = columns | |
self.index = index | |
# self.db = get_db(request) | |
self.collection = collection | |
# values specified by the datatable for filtering, sorting, paging | |
self.request_values = request.GET | |
self.db = db | |
# results from the db | |
self.result_data = None | |
# total in the table after filtering | |
self.cardinality_filtered = 0 | |
# total in the table unfiltered | |
self.cardinality = 0 | |
self.run_queries() | |
def output_result(self): | |
output = {} | |
output['sEcho'] = str(int(self.request_values['sEcho'])) | |
output['iTotalRecords'] = str(self.cardinality) | |
output['iTotalDisplayRecords'] = str(self.cardinality_filtered) | |
aaData_rows = [] | |
#print "sEcho", output['sEcho'] | |
for row in self.result_data: | |
# print('row : ', row) | |
aaData_row = [] | |
for i in range(len(self.columns)): | |
aaData_row.append(row[self.columns[i]]) | |
#print aData_row | |
#add additional rows here that are not represented in the database | |
# aaData_row.append(('''<input id='%s' type='checkbox'></input>''' % (str(row[ self.index ]))).replace('\\', '')) | |
aaData_rows.append(aaData_row) | |
output['aaData'] = aaData_rows | |
# print("output: ", output) | |
return output | |
def run_queries(self): | |
#call db | |
db = self.db | |
# pages has 'start' and 'length' attributes | |
pages = self.paging() | |
# the term you entered into the datatable search | |
filters = self.filtering() | |
# the document field you chose to sort | |
sorting = self.sorting() | |
# get result from db | |
self.result_data = db[self.collection].find(filters, | |
skip=pages.start, | |
limit=pages.length, | |
sort=sorting) | |
self.result_data = list(self.result_data) | |
# print("result_data ", len(list(self.result_data))) | |
total_count = len(list(db[self.collection].find(filters))) | |
self.cardinality_filtered = total_count | |
self.cardinality = len(list(db[self.collection].find())) | |
# print("cardinality", self.cardinality) | |
def filtering(self): | |
# build your filter spec | |
filters = {} | |
if ('sSearch' in self.request_values.keys()) and (self.request_values['sSearch'] != ""): | |
# the term put into search is logically concatenated with 'or' between all columns | |
or_filter_on_all_columns = [] | |
for i in range(len(self.columns)): | |
column_filter = {} | |
column_filter[self.columns[i]] = {'$regex': self.request_values['sSearch'], '$options': 'i'} | |
or_filter_on_all_columns.append(column_filter) | |
filters['$or'] = or_filter_on_all_columns | |
return filters | |
def sorting(self): | |
order = [] | |
# mongo translation for sorting order | |
if (self.request_values['iSortCol_0'] != "") and (int(self.request_values['iSortingCols']) > 0): | |
order = [] | |
for i in range(int(self.request_values['iSortingCols'])): | |
order.append( | |
(self.columns[int(self.request_values['iSortCol_' + str(i)])], order_dict[self.request_values['sSortDir_' + str(i)]]) | |
) | |
return order | |
def paging(self): | |
pages = namedtuple('pages', ['start', 'length']) | |
if (self.request_values['iDisplayStart'] != "") and (self.request_values['iDisplayLength'] != -1): | |
pages.start = int(self.request_values['iDisplayStart']) | |
pages.length = int(self.request_values['iDisplayLength']) | |
return pages |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$('#table').dataTable({ | |
"bProcessing": true, | |
"bServerSide": true, | |
"sPaginationType": "full_numbers", | |
"bjQueryUI": true, | |
"sAjaxSource": "/route_name" // USE AJAXSOURCE TO SWITCH TO V1.9 AUTOMATICALLY, even if you are using latest version! | |
}); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
from .datatables import DataTables | |
def func_name(request): | |
# request method is GET | |
columns = ['name', 'user_id'] | |
index_column = "_id" | |
collection = "collection_name" | |
results = DataTables(request, columns, index_column, collection).output_result() | |
return HttpResponse(json.dumps(results), content_type="application/json") |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment