dabo Commit
Revision 1508
Date: 2005-11-02 06:50:48 -0800 (Wed, 02 Nov 2005)
Author: ed
Changed:
U trunk/dabo/db/dCursorMixin.py
Log:
Implemented the Order By clause of DataSet querying. You can now do a query on
a Dabo DataSet very much as you do a single-table SQL Select:
ds = bizobj.getDataSet()
slct = ds.select(fields=("firstname", "lastname", "title"),
where="lastname.lower() = 'leafe' ",
orderBy="lastname desc, firstname")
After running the above 'slct' will contain the 3 specified fields from the
records in the original data set that match the 'where' condition, and they
will be ordered by lastname in reverse order, with any 'ties' sorted on
firstname.
Diff:
Modified: trunk/dabo/db/dCursorMixin.py
===================================================================
--- trunk/dabo/db/dCursorMixin.py 2005-11-02 05:21:01 UTC (rev 1507)
+++ trunk/dabo/db/dCursorMixin.py 2005-11-02 14:50:48 UTC (rev 1508)
@@ -1665,7 +1665,7 @@
_dictSubName = "_dataSet_rec"
- def _fldReplace(self, expr):
+ def _fldReplace(self, expr, dictName=None):
"""The list comprehensions require the field names be the keys
in a dictionary expression. Users, though, should not have to
know
about this. This takes a user-defined, SQL-like expressions,
and
@@ -1675,29 +1675,31 @@
keys = self[0].keys()
patTemplate = "(.*\\b)%s(\\b.*)"
ret = expr
+ if dictName is None:
+ dictName = self._dictSubName
for kk in keys:
pat = patTemplate % kk
mtch = re.match(pat, ret)
if mtch:
- ret = mtch.groups()[0] + "%s['%s']" %
(self._dictSubName, kk) + mtch.groups()[1]
+ ret = mtch.groups()[0] + "%s['%s']" %
(dictName, kk) + mtch.groups()[1]
return ret
- def select(self, flds=None, where=None, orderBy=None):
+ def select(self, fields=None, where=None, orderBy=None):
fldList = []
whereList = []
orderByList = []
keys = self[0].keys()
- if flds is None or flds == "*":
+ if fields is None or fields == "*":
# All fields
- flds = keys
- elif isinstance(flds, basestring):
+ fields = keys
+ elif isinstance(fields, basestring):
# Convert to list
- flds = [flds]
- for fld in flds:
+ fields = [fields]
+ for fld in fields:
fldList.append("'%s' : %s" % (fld,
self._fldReplace(fld)))
- fldsToReturn = ", ".join(fldList)
- fldsToReturn = "{%s}" % fldsToReturn
+ fieldsToReturn = ", ".join(fldList)
+ fieldsToReturn = "{%s}" % fieldsToReturn
# Where list elements
if where is None:
@@ -1710,7 +1712,51 @@
whereClause = " and ".join(whereList)
if whereClause:
whereClause = " if %s" % whereClause
+ stmnt = "[%s for %s in self %s]" % (fieldsToReturn,
self._dictSubName, whereClause)
+ resultSet = eval(stmnt)
- stmnt = "[%s for %s in self %s]" % (fldsToReturn,
self._dictSubName, whereClause)
- resultSet = eval(stmnt)
- return resultSet
+ if orderBy:
+ # This should be a comma separated string in the format:
+ # fld1, fld2 desc, fld3 asc
+ # After the field name is an optional direction, either
'asc'
+ # (ascending, default) or 'desc' (descending).
+ # IMPORTANT! Fields referenced in 'orderBy' MUST be in
+ # the result data set!
+ orderByList = orderBy.split(",")
+ sortList = []
+
+ def orderBySort(val1, val2):
+ ret = 0
+ compList = orderByList[:]
+ while not ret:
+ comp = compList[0]
+ compList = compList[1:]
+ if comp[-4:].lower() == "desc":
+ compVals = (-1, 1)
+ else:
+ compVals = (1, -1)
+ # Remove the direction, if any, from
the comparison.
+ compWords = comp.split(" ")
+ if compWords[-1].lower() in ("asc",
"desc"):
+ compWords = compWords[:-1]
+ comp = " ".join(compWords)
+ cmp1 = self._fldReplace(comp, "val1")
+ cmp2 = self._fldReplace(comp, "val2")
+ eval1 = eval(cmp1)
+ eval2 = eval(cmp2)
+ if eval1 > eval2:
+ ret = compVals[0]
+ elif eval1 < eval2:
+ ret = compVals[1]
+ else:
+ # They are equal. Continue
comparing using the
+ # remaining terms in compList,
if any.
+ if not compList:
+ break
+ return ret
+
+ resultSet.sort(orderBySort)
+
+ return DataSet(resultSet)
+
+
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
