Hello,
The book is not clear about counting in context of join :
Grouping and counting
When doing joins, sometimes you want to group rows according to certain
criteria and count them. For example, count the number of things owned by
every person. web2py allows this as well. First, you need a count operator.
Second, you want to join the person table with the thing table by owner.
Third, you want to select all rows (person + thing), group them by person,
and count them while grouping:
grouping
>>> count = db.person.id.count()
>>> for row in db(db.person.id==db.thing.owner).select(
db.person.name, count, groupby=db.person.name):
print row.person.name, row[count]
Alex 2
Bob 1
Notice the count operator (which is built-in) is used as a field. The only
issue here is in how to retrieve the information. Each row clearly contains
a person and the count, but the count is not a field of a person nor is it
a table. So where does it go? It goes into the storage object representing
the record with a key equal to the query expression itself. The count
method of the Field object has an optional distinct argument. When set to
Trueit specifies that only distinct values of the field in question are to
be counted.
I try this, but it not seems to work properly, at least it not giving me
the count I suppose to have :
count = db.table.id.count()
db((db.table.date>='2012-01-01') &
(db.table.date<='2012-12-31')).select(count,
join=[db.table.on(db.table.othertable_id==db.othertable.id), ...])
I know, that I could just add a where clause like this one
"(db.table.othertable_id==db.othertable.id)", but I have many joins...
Thanks
Richard
--
