Alex Macmillan wrote:
> I started this post while the problem was unsolved but I'd like to
> post it anyway for people suffering similar woes and also to see if
> anybody has any improvements on my own solution.
>
> Solving the (trivial) problem of counting the number of rows matching
> a criterion in a relational database:
>
> result = select('foo', what="count(*)" where="foo.bar = %d" % bar)
>
> How do you retrieve the actual count from the returned object?
> Intuitively one would hope to access the result as the first column of
> the first row but result[0][0] is not valid, and I can't discover an
> attribute name with which to access the field.
>
> A poor solution is to return all matching rows and acquire the count
> from len(result), but this is klutzy and won't meet scalability
> requirements over a large DB:
>
> count = len(select('foo', where="foo.barid = %d" % barid))
>
> I struck a better solution remembering the AS syntax:
>
> count = select('foo', what='count(*) AS count', where="foo.barid = %d"
> % barid)[0].count
>
> However, explicitly accessing the first row with [0] makes this
> solution still a little short of perfect. Is there yet a better way?
>
> --
> Alex Macmillan
What you did there was the right thing; you have to use "SELECT
COUNT(bla) AS bla_count" to give the count column a name. Not doing this
is poor practice regardless of your implementation; you should always
explicitly name the results of these functions (MAX, COUNT, MIN, etc) in
SQL statements.
Anyway, what's wrong with explicitly addressing the first row? That's
how retrieving a one-row-result works.. perhaps you are looking for a
function similar to PEAR's getOne() [1]; as far as I know, web.py does
not have this. I have to say though, I do not believe the lack of this
function (and thus the need to put "[0].count" there) makes your code
much less elegant. But that is just my opinion.
If you would feel so inclined, you could of course always add this
functionality yourself and submit a patch :) if it is nice it will
probably added to web.py.
Greetings,
b^4
[1] http://pear.php.net/manual/en/package.database.db.db-common.getone.php
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web.py" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/webpy?hl=en
-~----------~----~----~----~------~----~------~--~---
