From: <py...@to...> - 2006-06-14 16:30:01
|
Hello, I want to have results of a query, ordered by a count() field. I've tried : >>>Catalogue._connection.sqlrepr(sqlbuilder.Select([Fournisseurs.q.id,=20 func.Count(Fournisseurs.q.id)] =20 ,AND(Catalogue.q.marqueID=3D=3Dself.id =20 ,CatalogueFournisseurs.q.fournisseurID=3D=3DFournisseurs.q.id =20 ,CatalogueFournisseurs.q.catalogueID=3D=3DCatalogue.q.id ) =20 ,groupBy=3DFournisseurs.q.id ,orderBy=3D=20 func.Count(Fournisseurs.q.id) ) ) SELECT nt_fournisseurs.no_fournisseur,=20 Count(nt_fournisseurs.no_fournisseur) FROM nt_fournisseurs,=20 nt_catalogue_fournisseurs, nt_catalogue WHERE ((nt_catalogue.no_marque =3D= =20 376) AND ((nt_catalogue_fournisseurs.no_fournisseur =3D=20 nt_fournisseurs.no_fournisseur) AND=20 (nt_catalogue_fournisseurs.no_catalogue =3D nt_catalogue.no_catalogue)))=20 GROUP BY nt_fournisseurs.no_fournisseur ORDER BY=20 Count(nt_fournisseurs.no_fournisseur) =20 But (almost) in MySQL I have an error : ERROR 1111: Invalid use of group=20 function The right SQL is SELECT nt_fournisseurs.no_fournisseur,=20 Count(nt_fournisseurs.no_fournisseur) AS nn FROM... ORDER BY nn How can I make a field alias ? Fran=E7ois |
From: Oleg B. <ph...@ma...> - 2006-06-14 16:46:45
|
On Wed, Jun 14, 2006 at 06:29:40PM +0200, Fran?ois wrote: > How can I make a field alias ? There is sqlbuilder.Alias, and it's documented. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: <py...@to...> - 2006-06-15 08:31:00
|
[sorry... I make a mistake and send this mail before to oleg directly.=20 so this is a copy] Oleg Broytmann a =E9crit : > On Wed, Jun 14, 2006 at 06:29:40PM +0200, Fran?ois wrote: > =20 >> How can I make a field alias ? >> =20 > > There is sqlbuilder.Alias, But Alias is for Tables, no ? I want to make an alias of a *calculated=20 field* > and it's documented. > =20 It's probable, but before to post here, I haved a look on sqlbuilder.py,=20 on website http://www.sqlobject.org/SQLBuilder.html and make some search=20 on google, but I don't found the right documentation. Sorry for this... I want to do : SELECT COUNT(table.field1) as MyAlias FROM table GROUP BY table.field2 ORDER BY MyAlias DESC > Oleg. > =20 Fran=E7ois |
From: Oleg B. <ph...@ma...> - 2006-06-15 09:03:20
|
On Thu, Jun 15, 2006 at 10:30:41AM +0200, Fran?ois wrote: > > There is sqlbuilder.Alias, > But Alias is for Tables, no? Oops, sorry, it is. There are no field aliases and adding ones would be a bigger job - one needs to patch all places where SQLObject generates SQL query strings in dbconnection.py, main.py and sqlbuilder.py. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: <py...@to...> - 2006-06-15 10:16:37
|
Oleg Broytmann a =E9crit : > On Thu, Jun 15, 2006 at 10:30:41AM +0200, Fran?ois wrote: > =20 >>> There is sqlbuilder.Alias, >>> =20 >> But Alias is for Tables, no? >> =20 > > Oops, sorry, it is. There are no field aliases and adding ones would= be > a bigger job - one needs to patch all places where SQLObject generates = SQL > query strings in dbconnection.py, main.py and sqlbuilder.py. > > Oleg. > =20 OK, so for SELECT COUNT(table.field1) as MyAlias FROM table GROUP BY table.field2 ORDER BY MyAlias DESC I make : count =3D func.COUNT(Table.q.field1) sql =3D conn.sqlrepr(sqlbuilder.Select([count] =20 ,groupBy=3DTable.q.field2 ) ) alias =3D 'MyAlias' count_original =3D "%s" % count count_replace =3D "%s AS %s" % (count, alias) sql.replace(count_original, count_replace) return conn.queryAll(sql + " ORDER BY %s DESC" % alias) Not really nice, but working :-) Fran=E7ois |