Re: [web2py] Re: How to do a count distinct with DAL?
Postgres too! On Fri, Jan 28, 2011 at 1:41 PM, villas villa...@gmail.com wrote: It seems to work in SQLite and MS-SQL though. Firebird seems OK too.
[web2py] Re: How to do a count distinct with DAL?
OK. Now in trunk:
db=DAL()
db.define_table('a',Field('b'),Field('c'))
db(db.a.b0)._count()
SELECT count(*) FROM a WHERE (a.b '0')
db(db.a.b0)._count(distinct=db.a.b)
SELECT count(DISTINCT a.b) FROM a WHERE (a.b '0')
db(db.a.b0)._count(distinct=db.a.b|db.a.c)
SELECT count(DISTINCT a.b, a.c) FROM a WHERE (a.b '0')
db(db.a.b0)._count(distinct=(db.a.b,db.a.c))
SELECT count(DISTINCT a.b, a.c) FROM a WHERE (a.b '0')
Give it a try and report any bug.
Massimo
On Jan 29, 7:18 am, beto (R3) bet...@gmail.com wrote:
Postgres too!
On Fri, Jan 28, 2011 at 1:41 PM, villas villa...@gmail.com wrote:
It seems to work in SQLite and MS-SQL though.
Firebird seems OK too.
[web2py] Re: How to do a count distinct with DAL?
The SQL command would be
SELECT COUNT(DISTINCT item.of_variant) FROM item;
but I believe that is not something you can build in the DAL
currently.
In addition that SQL command might not be supported by all the backend
DBs.
It seems to work in SQLite and MS-SQL though.
So you have two options:
1) len( db().select(db.item.of_variant, distinct=True) )
2) db.executesql('SELECT COUNT(DISTINCT item.of_variant) FROM item;')
if it is supported by the DB you are using.
On Jan 28, 7:32 am, vortex billyara...@gmail.com wrote:
How to do a count distinct with DAL?
like:
db().count(db.item.of_variant, distinct=True)
[web2py] Re: How to do a count distinct with DAL?
This is not supported because I am not sure all supported RDBS support count(distinct ...). As a way around it you can do len(db().select(db.item.of_variant,distinct=True)) Massimo On Jan 28, 6:32 am, vortex billyara...@gmail.com wrote: How to do a count distinct with DAL? like: db().count(db.item.of_variant, distinct=True)
[web2py] Re: How to do a count distinct with DAL?
Thank you very much! On Jan 28, 2:43 pm, Massimo Di Pierro massimo.dipie...@gmail.com wrote: This is not supported because I am not sure all supported RDBS support count(distinct ...). As a way around it you can do len(db().select(db.item.of_variant,distinct=True)) Massimo On Jan 28, 6:32 am, vortex billyara...@gmail.com wrote: How to do a count distinct with DAL? like: db().count(db.item.of_variant, distinct=True)
Re: [web2py] Re: How to do a count distinct with DAL?
Can we not add per-dialect identifiers to DAL... I can see adding this just for postgres ms sql. -- Thadeus On Fri, Jan 28, 2011 at 8:43 AM, Massimo Di Pierro massimo.dipie...@gmail.com wrote: This is not supported because I am not sure all supported RDBS support count(distinct ...). As a way around it you can do len(db().select(db.item.of_variant,distinct=True)) Massimo On Jan 28, 6:32 am, vortex billyara...@gmail.com wrote: How to do a count distinct with DAL? like: db().count(db.item.of_variant, distinct=True)
[web2py] Re: How to do a count distinct with DAL?
It seems to work in SQLite and MS-SQL though. Firebird seems OK too.
[web2py] Re: How to do a count distinct with DAL?
OK. 3 is enough. Please add a google code issue and I will add it asap. Meanwhile if you want to try build a patch, it would be a new exercise in understainding the new dal. search for def count in the file. On Jan 28, 10:41 am, villas villa...@gmail.com wrote: It seems to work in SQLite and MS-SQL though. Firebird seems OK too.
