def query(self, like, sidx, rows=25, sord="desc", page=1, _search=False, **kw): Knowledge = get_knowledge_session("sqlite:///knowledge.db") query = Knowledge.query(Entity).filter(Entity.name.like(like)) count = query.count() page = int(page) rows = int(rows) total_pages = count / rows if page > total_pages: page = total_pages model, columns = get_colmodel_and_colnames_from_entity(Knowledge, like) # ~ .order_by(getattr(getattr(model, sidx), sord)()) \ query = query.offset(max(page * rows - rows, 0)).limit(rows).all() entries = [] for row in query: cell = [] for col in columns: if col == "id": continue cell.append(row[col]) # Note: this requires each model to have an 'id' column... entries.append({"id": row.id, "cell": cell}) return dict(page=page, total=total_pages, records=count, rows=entries)
def prepare(self): super(PolyGrid, self).prepare() if not getattr(self, 'id', None): self.id = str(uuid4()) Knowledge = get_knowledge_session("sqlite:///knowledge.db") #entity = Entity.by_name(self.model) self.entity = Knowledge.query(Entity).filter(Entity.name.like('user_%')).first() #colNames = get_colnames_from_entity(entity) #colModel = get_colmodel_from_entity(entity) colModel, colNames = get_colmodel_and_colnames_from_entity(Knowledge, 'user_%') params = { 'url': '/grid/query?like=user_%', 'datatype': 'json', 'colNames': colNames, 'colModel': colModel, 'height': 550, 'rowNum': 25, #'rowList': [10,20,30], 'pager': '#%s_pager' % self.id, 'sortname': 'id', 'viewrecords': True, 'sortorder': "desc", 'caption': self.entity.name, 'autowidth': True, 'loadui': 'block', #'shrinkToFit': True, 'forcefit': True, } self.add_call(jQuery('#' + self.id) .jqGrid(params) .navGrid('#%s_pager' % self.id, { 'add': False, 'del': False, 'edit': False, 'search': False, }, {}, {}, {}, {'multipleSearch': True})) self.add_call(jQuery('#' + self.id) .jqGrid('filterToolbar',{ 'stringResult': True, 'searchOnEnter': False })); self.add_call(jQuery('#' + self.id) .jqGrid('navButtonAdd', '#%s_pager' % self.id, { 'caption': 'Columns', 'title': 'Reorder Columns', 'onClickButton': js_callback(""" function() { jQuery('#%s').jqGrid('columnChooser'); } """ % self.id), }))
def query(self, like, sidx, rows=25, sord='desc', page=1, _search=False, **kw): Knowledge = get_knowledge_session("sqlite:///knowledge.db") query = Knowledge.query(Entity).filter(Entity.name.like(like)) model, columns = get_colmodel_and_colnames_from_entity(Knowledge, like) if _search and _search.lower() == 'true': if 'filters' in kw: filters = json.loads(kw['filters']) if filters['groupOp'] == 'AND': group_func = and_ elif filters['groupOp'] == 'OR': group_func = or_ multi_query = [] for rule in filters['rules']: col = getattr(Entity, rule['field']) multi_query.append(self.query_filter(rule['op'], col, rule['data'])) query = query.filter(group_func(*multi_query)) count = query.count() page = int(page) rows = int(rows) total_pages = math.ceil(float(count) / rows) if page > total_pages: page = total_pages # This goes before offset, if it gets fixed #~ .order_by(getattr(getattr(model, sidx), sord)()) \ query = query \ .offset(max(page*rows - rows, 0)) \ .limit(rows) \ .all() entries = [] for row in query: cell = [] for col in columns: if col in ['id', 'name']: cell.append(row.name) else: try: cell.append(row[col]) except KeyError: cell.append(None) # Note: this requires each model to have an 'id' column... entries.append({'id': row.id, 'cell': cell}) return dict(page=page, total=total_pages, records=count, rows=entries)