Thread: [SQLObject] orderBy table.q.attribute
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Oleg B. <ph...@ph...> - 2004-04-23 14:48:51
|
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. |
From: Philippe N. <sw...@fr...> - 2004-04-23 15:02:38
|
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 |
From: Oleg B. <ph...@ma...> - 2004-04-23 15:16:33
|
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. |
From: Philippe N. <sw...@fr...> - 2004-04-23 15:33:19
|
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 |
From: Oleg B. <ph...@ma...> - 2004-04-23 15:48:13
|
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. |
From: Oleg B. <ph...@ph...> - 2004-04-23 15:53:51
|
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. |
From: Philippe N. <sw...@fr...> - 2004-04-23 16:17:48
|
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 |
From: Oleg B. <ph...@ma...> - 2004-04-23 16:50:34
|
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. |
From: Ian B. <ia...@co...> - 2004-04-23 16:18:59
|
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 |
From: Oleg B. <ph...@ph...> - 2004-04-23 16:46:33
|
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. |
From: Ian B. <ia...@co...> - 2004-04-23 17:04:41
|
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 |
From: Oleg B. <ph...@ph...> - 2004-04-23 17:38:06
|
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. |
From: Ian B. <ia...@co...> - 2004-04-23 17:41:59
|
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) |
From: Oleg B. <ph...@ph...> - 2004-04-23 18:02:37
|
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. |