python - jquery datatables with mongodb(mongoengine)

[Peter Otten]

renier de bruyn wrote:

> UPDATE:
> 
> So now I got this code:
> 
> import pymongo
> from pyramid_mongo import get_db
> 
> # translation for sorting between datatables api and mongodb
> order_dict = {'asc': 1, 'desc': -1}
> 
> 
> class DataTables_Handler(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.params
> 
>         # results from the db
>         self.result_data = None
> 
>         # total in the table after filtering
>         self.cardinality_filtered = 0
> 
>         # total in the table unfiltered
>         self.cadinality = 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 = []
> 
>         for row in self.result_data:
>             aaData_row = []
>             for i in range(len(self.columns)):
> 
>                 aaData_row.append(row[self.columns[i]].replace('"',
>                 '\\"'))
> 
>             # 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
> 
>         return output
> 
>     def run_queries(self):
> 
>         # 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 = self.db.self.collection.find(spec=filters,
>                                                       skip=pages.start,
>                                                       limit=pages.length,
>                                                       sort=sorting)
> 
>         total_count =
>         len(list(self.db.self.collection.find(spec=filters)))
> 
>         self.result_data = list(self.result_data)
> 
>         self.cardinality_filtered = total_count
> 
>         self.cardinality = len(list(self.db.self.collection.find()))
> 
>     def filtering(self):
> 
>         # build your filter spec
>         filters = {}
>         if (self.request_values.has_key('sSearch')) 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
>         (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
> 
> with this code in the View:
> 
> @view_config(
> route_name='candidates.list.json',
> renderer='json',
> permission='admin'
> )
> def candidate_list_json(context, request):
>     columns = [ 'id_number', 'first_name', 'email', 'mobile_number']
>     index_column = "id_number"
>     collection = "candidates"
> 
>     results = DataTables_Handler(request, columns, index_column,
>     collection).output_result()
> 
>     # return the results as a string for the datatable
>     return {"results": results}
> 
> and this in the template:
> 
> <a id="btn-addteam" class="btn btn-success"
> href="{{'add_candidate'|route_url}}"><i class="icon-plus"></i> Add
> Candidate</a>
> 
> ID Number Candidate Name Candidate email Mobile Number Health -->
> 
> <script
> src="{{'kivu:static/datatables/jquery.dataTables.js'|
static_url}}"></script>
>     <script
>     src="{{'kivu:static/datatables/dataTables.scroller.js'|
static_url}}"></script>
> <script>
> $(document).ready(function() {
>     url = "{{'candidates.list.json'|route_url}}";
>     var oTable = $('#candidate_search').dataTable( {
>         "bProcessing": true,
>         "bServerSide": true,
>         "sPaginationType": "full_numbers",
>         "bjQueryUI": true,
>         "sAjaxSource": url
> });
> } );</script>
> 
> But its giving me a js error:
> 
> TypeError: aData is undefined
>     for ( var i=0, iLen=aData.length ; i<iLen ; i++ )
> 
> my GET response returns:
> 
> {"results": {"aaData": [], "iTotalRecords": "0", "sEcho": "1",
> {"iTotalDisplayRecords": "0"}}
> 
> there is data in the database. What am I missing?

Could it have to do with the mismatch of 'aData' versus 'aaData'?