sqlobject-discuss

[SQLObject] Specifying defaultOrder

[Andy T. <an...@ha...>]

I've got a table in my database which contains a date column. I'd like 
to be able to specify that rows are returned in date order, which can be 
done with the _defaultOrder special class variable.

But - I'd like the records returned in reverse date order, which can be 
achieved in SQL with an order by clause like 'dateCol DESC'. I've tried 
this in my class definition but I get the following error;

AssertionError: Strings in clauses are expected to be column 
identifiers.  I got: 'price_date DESC'

Is there a way to do this with SQLObject?

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/

Re: [SQLObject] Specifying defaultOrder

[<el...@in...>]

Andy wrote:

> But - I'd like the records returned in reverse date order, which can be
> achieved in SQL with an order by clause like 'dateCol DESC'. I've tried
> this in my class definition but I get the following error;

You must be the 6th person to ask, I was the 5th, according to Luke. :-)
Sort order cannot currently be specified in SO.

...Edmund.

Re: [SQLObject] Specifying defaultOrder

[Nick <ni...@dd...>]

el...@in... wrote:
> Andy wrote:
>>But - I'd like the records returned in reverse date order, which can be
>>achieved in SQL with an order by clause like 'dateCol DESC'. I've tried
>>this in my class definition but I get the following error;
> You must be the 6th person to ask, I was the 5th, according to Luke. :-)
> Sort order cannot currently be specified in SO.

Should say something about the direction that development should be taking.

Nick

Re: [SQLObject] Specifying defaultOrder

[Ian B. <ia...@co...>]

On Thu, 2003-06-19 at 09:15, Nick wrote:
> el...@in... wrote:
> > Andy wrote:
> >>But - I'd like the records returned in reverse date order, which can be
> >>achieved in SQL with an order by clause like 'dateCol DESC'. I've tried
> >>this in my class definition but I get the following error;
> > You must be the 6th person to ask, I was the 5th, according to Luke. :-)
> > Sort order cannot currently be specified in SO.
> 
> Should say something about the direction that development should be taking.

Yeah, I'm planning on it Real Soon, I just have to look at the code. 
Probably options like:

defaultOrder='-dateCol'
defaultOrder=SQLBuilder.DESC(Something.dateCol)
sel = Something.select().reversed()
sel = Something.select(reversed=True)
# or descending=True?

Re: [SQLObject] Specifying defaultOrder

[Luke O. <lu...@me...>]

> > Sort order cannot currently be specified in SO.
>
> Should say something about the direction that development should be taking.
>
> Nick

Alright, here's what it'll take (from current CVS) to get order direction:

Questions:

Can you think of better names than "_defaultOrderDirection" and
"orderDirection"?

Should the value be the strings "DESC"/"ASC", be case-insensitive, be some
python representation of these strings? Accept strings but turn them into some
python rep?

1. Modify SQLObject to specify _defaultOrderDirection = None in class vars.

2. Modify SQLObject.select() to take keyword "orderDirection", and pass to
SelectResults.

    def select(cls, clause=None, clauseTables=None,
               orderBy=NoDefault, orderDirection=NoDefault,
               groupBy=None, limit=None, lazyColumns=False):
        return SelectResults(cls, clause, clauseTables=clauseTables,
                     orderBy=orderBy, orderDirection=orderDirection,
                     groupBy=groupBy, limit=limit, lazyColumns=lazyColumns)

3. Modify SelectResults (SQLObject.py) class to set ops['orderDirection'] =
sourceClass._defaultOrderDirection if not specified.

        if self.ops.get('orderDirection', NoDefault) is NoDefault:
            self.ops['orderDirection'] = sourceClass._defaultOrderDirection

4. Modify DBConnection.whereClauseForSelect() to actually insert the DESC/ASC
word if ops['orderDirection'] is set.

     if order and ops.get('dbOrderBy'):
         q = "%s ORDER BY %s" % (q, ", ".join(clauseList(ops['dbOrderBy'])))
+        if ops.get('orderDirection',None):
+            q = "%s %s" % (q, ops['orderDirection'])

(Also, DBMConnection implements it's own iterSelect/DBMSelectResults,
presumably because it doesn't support ORDER BY natively. This will need to be
modified to swap comparisons much like Joins below.)

4. Modify SOJoin.__init__() to accept an orderDirection keyword, modify
SOJoin._applyOrder() to use defaultSortClass._defaultOrderDirection if None
given, and to use a reverseSorter that swaps the comparison if
orderDirection.upper() == 'DESC'

    def _applyOrderBy(self, results, defaultSortClass):
        if self.orderBy is NoDefault:
            self.orderBy = defaultSortClass._defaultOrder
+       if self.orderDirection is NoDefault:
+           self.orderDirection = defaultSortClass._defaultOrderDirection
        if self.orderBy is not None:
            def sorter(a, b, attr=self.orderBy):
                return cmp(getattr(a, attr),
                           getattr(b, attr))
+           def revSorter(a, b, attr=self.orderBy):
+               return cmp(getattr(a, attr), getattr(b, attr))
+           if self.orderDirection.upper() == 'DESC'
                results.sort(revSorter)
+           else:
+               results.sort(sorter)
        return results


And I think that should be everything. I haven't tested all of this yet, and it
can't be committed till Ian returns, but comments are welcome.

- Luke

Re: [SQLObject] Specifying defaultOrder

[<el...@in...>]

Luke,

> Can you think of better names than "_defaultOrderDirection" and
> "orderDirection"?

I feel that "sortOrder" is a little bit of an improvement over the other
alternatives.

Is there any possibility of sorting on more than one column? It's pretty
common to have to sort on more than one column...

...Edmund.

Re: [SQLObject] Specifying defaultOrder

[Luke O. <lu...@me...>]

> Is there any possibility of sorting on more than one column? It's pretty
> common to have to sort on more than one column...

It appears that CVS currently supports a list for SelectResults orderBy (and
_defaultOrder), and properly generates SQL for them. I haven't actually used
this in SQLObject yet however. Also, the Join sort implementation will break
if you do this, so that'll need to be fixed.

- Luke