Class for server side processing of datatables.net for the Masonite Framework

Controller.py

from masonite.request import Request
from config.database import DB
from app.util import TableSchema
from app.util.ServerSideTable import ServerSideTable

class ClientController:
    """ClientController
    """

    def listClients(self, request: Request):
        columns = TableSchema.SERVERSIDE_TABLE_COLUMNS
        extraWhere = []
        extraWhere.append({
            "col": 'userid',
            "operator": "=",
            "val": request.user().userid,
            "bool": "AND"
        })
        ssp = ServerSideTable()
        return ssp.simple(request, DB, 'clients', 'clientid', columns, extraWhere)

ServerSideTable.py

import re


class ServerSideTable():


    def simple(self, request, database, table, primaryKey, columns, extraWhere):

        request = self.mr_parse(request.all())

        order = self.order(request, columns)
        bindings = self.filter(request, columns, extraWhere)
        
        data = self.sql_exec(database, bindings, table, order, request, columns, None)

        resFilterLength = self.sql_exec(database, bindings, table, None, None, None, primaryKey)

        recordsFiltered = resFilterLength

        resTotalLength = self.sql_exec(database, None, table, None, None, None, primaryKey)
        recordsTotal = resTotalLength

        output = {}
        output['draw'] = request['draw']
        output['recordsTotal'] = recordsTotal
        output['recordsFiltered'] = recordsFiltered
        output['data'] = self.data_output(columns, data)

        return output

    def sql_exec(self, database, bindings, table, order, request, columns, countColumn):

        if countColumn is not None:
            statement = database.table(table).select(countColumn)
        else:
            statement = database.table(table).select(*self._pluck(columns, 'db'))
		
        if bindings is not None:
            for i in bindings:
                binding = i
                if binding['bool'] == 'AND':
                    statement = statement.where(binding['col'], binding['operator'], binding['val'])
                else:
                    statement = statement.or_where(binding['col'], binding['operator'], binding['val'])

            print(statement.to_sql())


        if request is not None:
            start = request['start']
            length = request['length']
            statement = statement.skip(int(start)).limit(int(length))

        if order is not None:
            statement = statement.order_by_raw(order)

        
        if countColumn is not None:
            return statement.count(countColumn)
        else:
            return statement.get()

    def filter(self, request, columns, extraWhere):
        bindings = []
        dtColumns = self._pluck( columns, 'dt' )

        if request['search'] is not None and 'value' in request['search'] and request['search']['value'] != '':
            str = request['search']['value']

            for i in request['columns']:
                requestColumn = request['columns'][i]
                columnIdx = dtColumns.index(requestColumn['data'])
                column = columns[ columnIdx ]

                if requestColumn['searchable'] == 'true':
                    bindings.append({
                        "col": column['db'],
                        "operator": "like",
                        "val":'%'+str+'%',
                        "bool": "OR"
                    })
				
        # dd(request['columns'])
        if request['columns'] is not None:
            for i in request['columns']:
                requestColumn = request['columns'][i]
                columnIdx = dtColumns.index(requestColumn['data'])
                column = columns[ columnIdx ]

                if 'value' in requestColumn['search']:
                    str = requestColumn['search']['value']
                else:
                    str = ''

                if requestColumn['searchable'] == 'true' and str != '':
                    bindings.append({
                        "col": column['db'],
                        "operator": "like",
                        "val": '%'+str+'%',
                        "bool": "AND"
                    })

        return bindings + extraWhere


    def order(self, request, columns):
        order = ''
        if request['order'] is not None and len(request['order']) > 0:
            orderBy = []
            dtColumns = self._pluck( columns, 'dt' )

            for i in request['order']:
                columnIdx = request['order'][i]['column']
                requestColumn = request['columns'][columnIdx]
                print(dtColumns)
                columnIdx = dtColumns.index(requestColumn['data'])
                # dd(requestColumn)
                column = columns[ columnIdx ]
                
                if requestColumn['orderable'] == 'true':
                    dir = ""
                    if request['order'][i]['dir'] == 'asc':
                        dir = 'ASC' 
                    else: 
                        dir = 'DESC'
                    orderBy.append('{} {}'.format(column['db'], dir))

            if len(orderBy) > 0:
                order = '{}'.format(', '.join(orderBy))

        return order

    def limit(self, request, columns):
        limit = ''

        if request['start'] and request['length'] != -1:
            limit = 'LIMIT {}, {}'.format(request['start'], request['length'])
		
        return limit

    def _pluck(self, columns, prop):
        out = []
        print(columns)
        for a in columns:
            out.append(str(a[prop]))

        return out


    def data_output(self, columns, data):
        out = []
        for i in data:
            row = {}
            for j in columns:
                column = j
                # Is there a formatter?
                if 'formatter' in column:
                    row[ str(column['dt']) ] = column['formatter']( i[ column['db'] ], i)
                else:
                    row[ str(column['dt']) ] = i[ column['db'] ]
				
            out.append(row)
		
        return out

    def split(self, string, brackets_on_first_result = False):
        matches = re.split("[\[\]]+", string)
        matches.remove('')
        return matches

    def mr_parse(self, params):
        results = {}
        for key in params:
            if '[' in key:
                key_list = self.split(key)
                d = results
                for partial_key in key_list[:-1]:
                    if partial_key not in d:
                        d[partial_key] = dict()
                    d = d[partial_key]
                d[key_list[-1]] = params[key]
            else:
                results[key] = params[key]
        return results

TableSchema.py

SERVERSIDE_TABLE_COLUMNS = [
    {
        "db": "name",
        "dt": 0,
    },
    {
        "db": "email",
        "dt": 1,
    },
    {
        "db": "phone",
        "dt": 2,
    },
    {
        "db": "address",
        "dt": 3,
    },
    {
        "db": "city",
        "dt": 4,

    },
    {
        "db": "clientid",
        "dt": 5,
    }
]