sqlobject-discuss

[SQLObject] orderBy table.q.attribute

[Oleg B. <ph...@ph...>]

Hello.

   How can I orderBy using table.q.attribute magic?

patients = Patient.select(None, orderBy=-Patient.q.registrationTime)

Traceback (most recent call last):
[snip]
  File "/usr/local/lib/python2.3/site-packages/SQLObject/SQLObject.py", line 1068, in select
    connection=connection)
  File "/usr/local/lib/python2.3/site-packages/SQLObject/SQLObject.py", line 1235, in __init__
    orderBy = self._mungeOrderBy(orderBy)
  File "/usr/local/lib/python2.3/site-packages/SQLObject/SQLObject.py", line 1246, in _mungeOrderBy
    if self.sourceClass._SO_columnDict.has_key(orderBy):
TypeError: unhashable instance

   SQLObject 0.5.2 with inheritance patch.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] orderBy table.q.attribute

[Philippe N. <sw...@fr...>]

On Fri, Apr 23, 2004 at 06:48:43PM +0400, Oleg Broytmann wrote:
> Hello.
>=20
>    How can I orderBy using table.q.attribute magic?
>=20
> patients =3D Patient.select(None, orderBy=3D-Patient.q.registrationTime)
>=20

Well, what's the dash out there ? If you want to order by registrationTime
DESC, used reversed param like:

patients =3D Patient.select(orderBy=3DPatient.q.registrationTime,
                          reversed=3DTrue)
			 =20


--=20
Philippe Normand

Re: [SQLObject] orderBy table.q.attribute

[Oleg B. <ph...@ma...>]

On Fri, Apr 23, 2004 at 04:50:23PM +0200, Philippe Normand wrote:
> On Fri, Apr 23, 2004 at 06:48:43PM +0400, Oleg Broytmann wrote:
> > Hello.
> > 
> >    How can I orderBy using table.q.attribute magic?
> > 
> > patients = Patient.select(None, orderBy=-Patient.q.registrationTime)
> > 
> 
> Well, what's the dash out there ?

   No difference without it:

patients = Patient.select(None, orderBy=Patient.q.registrationTime)

Traceback:
[snip]
    if self.sourceClass._SO_columnDict.has_key(orderBy):
TypeError: unhashable instance

> If you want to order by registrationTime
> DESC, used reversed param like:
> 
> patients = Patient.select(orderBy=Patient.q.registrationTime,
>                           reversed=True)

   What if I want to orderBy a complex condition?

...orderBy=[-registrationTime, name, surname]

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] orderBy table.q.attribute

[Philippe N. <sw...@fr...>]

On Fri, Apr 23, 2004 at 07:16:29PM +0400, Oleg Broytmann wrote:
> On Fri, Apr 23, 2004 at 04:50:23PM +0200, Philippe Normand wrote:
> > On Fri, Apr 23, 2004 at 06:48:43PM +0400, Oleg Broytmann wrote:
> > > Hello.
> > >=20
> > >    How can I orderBy using table.q.attribute magic?
> > >=20
> > > patients =3D Patient.select(None, orderBy=3D-Patient.q.registrationTi=
me)
> > >=20
> >=20
> > Well, what's the dash out there ?
>=20
>    No difference without it:

I don't understand the 'None' ... Do you want to select ... nothing ? hmm :)
Try replacing None with 'all'. And, still the dash doesn't make sense to me
because orderBy has to be a string, not a number. Anyway ...

>=20
>    What if I want to orderBy a complex condition?
>=20
> ...orderBy=3D[-registrationTime, name, surname]

Never tried such a thing.

--=20
Philippe Normand

Re: [SQLObject] orderBy table.q.attribute

[Oleg B. <ph...@ma...>]

On Fri, Apr 23, 2004 at 05:22:00PM +0200, Philippe Normand wrote:
> > > > patients = Patient.select(None, orderBy=-Patient.q.registrationTime)
> 
> I don't understand the 'None' ... Do you want to select ... nothing ? hmm :)

   I want to select all. None and "all" do the same - select all.

> Try replacing None with 'all'.

   In the real program there is a complex query. It does not matter -
the problem is in orderBy, not in the query.

> And, still the dash doesn't make sense to me
> because orderBy has to be a string, not a number. Anyway ...

   It has not to be a string. At least according to the documentation.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] orderBy table.q.attribute

[Oleg B. <ph...@ph...>]

On Fri, Apr 23, 2004 at 07:48:06PM +0400, Oleg Broytmann wrote:
> On Fri, Apr 23, 2004 at 05:22:00PM +0200, Philippe Normand wrote:
> > And, still the dash doesn't make sense to me
> > because orderBy has to be a string, not a number. Anyway ...

   Let do it with strings:

patients = Patient.select(None, orderBy="-registration_time")

Traceback (most recent call last):
[snip]
  File "/usr/local/lib/python2.3/site-packages/SQLObject/DBConnection.py", line 168, in _iterSelect
    cursor.execute(query)
ProgrammingError: ERROR:  Non-integer constant in ORDER BY

SELECT ... ORDER BY 'registration_time' DESC

   See? SQLObject have added excessive apostrophes to the column name :(

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] orderBy table.q.attribute

[Philippe N. <sw...@fr...>]

On Fri, Apr 23, 2004 at 07:53:29PM +0400, Oleg Broytmann wrote:
> On Fri, Apr 23, 2004 at 07:48:06PM +0400, Oleg Broytmann wrote:
> > On Fri, Apr 23, 2004 at 05:22:00PM +0200, Philippe Normand wrote:
> > > And, still the dash doesn't make sense to me
> > > because orderBy has to be a string, not a number. Anyway ...
>=20
>    Let do it with strings:
>=20
> patients =3D Patient.select(None, orderBy=3D"-registration_time")
>=20
> Traceback (most recent call last):
> [snip]
>   File "/usr/local/lib/python2.3/site-packages/SQLObject/DBConnection.py"=
, line 168, in _iterSelect
>     cursor.execute(query)
> ProgrammingError: ERROR:  Non-integer constant in ORDER BY
>=20
> SELECT ... ORDER BY 'registration_time' DESC
>=20
>    See? SQLObject have added excessive apostrophes to the column name :(
>=20
> Oleg.


Quoting the online doco (http://sqlobject.org/docs/SQLObject.html):

"You can use the keyword arguments orderBy to create ORDER BY in the
select statements: orderBy takes a string, which should be the
database name of the column, or a column in the form
Person.q.firstName."

Cheers

--=20
Philippe Normand

Re: [SQLObject] orderBy table.q.attribute

[Oleg B. <ph...@ma...>]

On Fri, Apr 23, 2004 at 06:05:40PM +0200, Philippe Normand wrote:
> Quoting the online doco (http://sqlobject.org/docs/SQLObject.html):
> 
> "You can use the keyword arguments orderBy to create ORDER BY in the
> select statements: orderBy takes a string, which should be the
> database name of the column, or a column in the form
> Person.q.firstName."

   As I have shown both variants don't work. By different reasons.
Probably bugs... this is exactly what I want to understand - are they
bugs? are they fixable in 0.5.2?

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] orderBy table.q.attribute

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

Oleg Broytmann wrote:
> On Fri, Apr 23, 2004 at 07:48:06PM +0400, Oleg Broytmann wrote:
> 
>>On Fri, Apr 23, 2004 at 05:22:00PM +0200, Philippe Normand wrote:
>>
>>>And, still the dash doesn't make sense to me
>>>because orderBy has to be a string, not a number. Anyway ...
> 
> 
>    Let do it with strings:
> 
> patients = Patient.select(None, orderBy="-registration_time")
> 
> Traceback (most recent call last):
> [snip]
>   File "/usr/local/lib/python2.3/site-packages/SQLObject/DBConnection.py", line 168, in _iterSelect
>     cursor.execute(query)
> ProgrammingError: ERROR:  Non-integer constant in ORDER BY
> 
> SELECT ... ORDER BY 'registration_time' DESC
> 
>    See? SQLObject have added excessive apostrophes to the column name :(

Huh... this is exactly what happens in SliceTest and NamesTest:

self.counterEqual(Counter.select('all', orderBy='number'), range(100))

self.assertEqual([(n.fname, n.lname) for n in 
Names.select().orderBy(['fname', 'lname'])],
                          [('aj', 'baker'), ('joe', 'baker'),
                           ('joe', 'robbins'), ('tim', 'jackson'),
                           ('zoe', 'robbins')])

And those work and don't quote their arguments.  Should it be 
"-registrationTime"?  Both should work, but maybe they don't.

   Ian

Re: [SQLObject] orderBy table.q.attribute

[Oleg B. <ph...@ph...>]

On Fri, Apr 23, 2004 at 11:18:15AM -0500, Ian Bicking wrote:
> "-registrationTime"?  Both should work, but maybe they don't.

Traceback (most recent call last):
[snip]
  File "/usr/local/lib/python2.3/site-packages/SQLObject/DBConnection.py", line 168, in _iterSelect
    cursor.execute(query)
ProgrammingError: ERROR:  Non-integer constant in ORDER BY

SELECT ... ORDER BY 'registrationTime' DESC

   Both does not. DB is Postgres.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] orderBy table.q.attribute

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

Oleg Broytmann wrote:
> On Fri, Apr 23, 2004 at 11:18:15AM -0500, Ian Bicking wrote:
> 
>>"-registrationTime"?  Both should work, but maybe they don't.
> 
> 
> Traceback (most recent call last):
> [snip]
>   File "/usr/local/lib/python2.3/site-packages/SQLObject/DBConnection.py", line 168, in _iterSelect
>     cursor.execute(query)
> ProgrammingError: ERROR:  Non-integer constant in ORDER BY
> 
> SELECT ... ORDER BY 'registrationTime' DESC
> 
>    Both does not. DB is Postgres.

Ah... I found the bug, in _mungeOrderBy; which should look like:

     def _mungeOrderBy(self, orderBy):
         if isinstance(orderBy, str) and orderBy.startswith('-'):
             orderBy = orderBy[1:]
             desc = True
         else:
             desc = False
         if isinstance(orderBy, (str, unicode)):
             if self.sourceClass._SO_columnDict.has_key(orderBy):
                 val = self.sourceClass._SO_columnDict[orderBy].dbName
                 if desc:
                     return '-' + val
                 else:
                     return val
             else:
                 if desc:
                     return '-' + orderBy
                 else:
                     return orderBy
         else:
             return orderBy

Re: [SQLObject] orderBy table.q.attribute

[Oleg B. <ph...@ph...>]

On Fri, Apr 23, 2004 at 12:04:07PM -0500, Ian Bicking wrote:
> Ah... I found the bug, in _mungeOrderBy; which should look like:

   Aha, now it is much better. orderBy="-registration_time" now works!
Thank you.

   orderBy=-Patient.q.registrationTime still does not work, though with
a different error:

Traceback (most recent call last):
[snip]
  File "/usr/local/lib/python2.3/site-packages/SQLObject/SQLObject.py", line 1381, in __iter__
    return conn.iterSelect(self)
  File "/usr/local/lib/python2.3/site-packages/SQLObject/DBConnection.py", line 167, in iterSelect
    select, keepConnection=False)
  File "/usr/local/lib/python2.3/site-packages/SQLObject/DBConnection.py", line 361, in __init__
    self.cursor.execute(self.query)
ProgrammingError: ERROR:  Unable to identify a prefix operator '-' for
type 'character varying'
        You may need to add parentheses or an explicit cast

SELECT ... ORDER BY - patient.registration_time

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] orderBy table.q.attribute

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

Oleg Broytmann wrote:
> On Fri, Apr 23, 2004 at 12:04:07PM -0500, Ian Bicking wrote:
> 
>>Ah... I found the bug, in _mungeOrderBy; which should look like:
> 
> 
>    Aha, now it is much better. orderBy="-registration_time" now works!
> Thank you.
> 
>    orderBy=-Patient.q.registrationTime still does not work, though with
> a different error:

If you are using that, you should use 
sqlbuilder.DESC(Patient.q.registrationTime)

Re: [SQLObject] orderBy table.q.attribute

[Oleg B. <ph...@ph...>]

On Fri, Apr 23, 2004 at 12:41:22PM -0500, Ian Bicking wrote:
> sqlbuilder.DESC(Patient.q.registrationTime)

   Yes, that works. Thank yoy very much! Sorry for bothering you with
it.

   Now I am eagerly waiting 0.5.3...

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.