python - jquery datatables with mongodb(mongoengine)

[renier de bruyn]

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?