def setfilter(self, rule="", group="", maxrows=0, page=1, orderby=None, orderbyasc=True):
'''
make a new query by the rule provided, get maxrows results, skip (page - 1)*maxrows results
default values get an unsorted list of all the tracks (tracks are always sorted by id)
returns lst list for convenience and a feedback list
feedback is a list of tuples with name and value
if rule is None previous value are used except for orderby and orderbyasc
'''
self._checksession()
#print('setfilter(rule="{}", group="{}", maxrows={}, page={}, orderby={}, orderbyasc={})'.format
# (rule, group, maxrows, page, orderby, orderbyasc))
fb = []
if self.stats.empty():
self.stats.total = self.session.query(Track).count()
if rule is None and group is None:
rule = self.prevrule
group = self.prevgroup
maxrows = self.prevmax
page = self.prevpage
if not type(maxrows) is int:
maxrows = 0
fb.append(("max", 0))
# when group changes, reset page; when mode changes, keep and convert sort order
newsortcol = None
if group != self.prevgroup:
if group == "" or self.prevgroup == "": # mode changed
newsortcol = self.orderingconvert()
if newsortcol:
fb.append(("sortbycol", newsortcol))
fb.append(("page", 1))
page = 1
# if max changes reset page
if maxrows != self.prevmax:
fb.append(("page", 1))
page = 1
# "register" new orderby
if orderby != None:
assert(newsortcol == None)
# new orderby resets page to 1
fb.append(("page", 1))
page = 1
# shift orderby value to clearly distinguish between asc and desc even for 0
orderno = orderby + 1
ordernosign = orderno if orderbyasc else -orderno
if not self.ordering or self.ordering[-1] != ordernosign:
self.ordbackup = None
if orderno in self.ordering:
self.ordering.remove(orderno)
elif -orderno in self.ordering:
self.ordering.remove(-orderno)
self.ordering.append(ordernosign)
#print("ordering", self.ordering)
# creating basic query
q = None
if group == "":
self.gtmode = False
q = self.session.query(Track)
else:
self.gtmode = True
q = self.session.query(GroupTrack).join(Track).join(Group)
q = q.filter(GroupTrack.groupid==Group.idno).filter(Group.name==group)
#rule filter
if rule != "":
q = self.buildquery(q, rule)
#sorting - remeber the shift between orderby and orderno
if self.ordering != None and len(self.ordering) > 0:
for on in reversed(self.ordering):
if on < 0:
oby = -1 - on
ofun = sqlalchemy.sql.expression.desc
else:
oby = on - 1
ofun = sqlalchemy.sql.expression.asc
if self.gtmode:
q = q.order_by(ofun(GroupTrack.colbycol(oby)))
else:
q = q.order_by(ofun(Track.colbycol(oby)))
#by default or as a last rule, sort tracks by trackid, grouptracks by number
if self.gtmode:
q = q.order_by(GroupTrack.no)
else:
q = q.order_by(Track.idno)
# query is prepared, execute it
if maxrows == 0:
self.lst = list(q.all())
elif maxrows == 1:
self.lst = q[(page-1*maxrows)]
else:
self.lst = q[(page-1)*maxrows:page*maxrows]
self.stats.newfilter(self.lst, q.count(), q.filter(Track.new == True).count())
# remember filter for sorting
self.prevrule = rule
self.prevgroup = group
self.prevmax = maxrows
self.prevpage = page
# return lst (for convenience) and feedback
return self.lst, fb
