Hi, I’m trying to construct a query in web2py which I’m struggling with -
help appreciated :)
Table 1
table1.batch_id
Table 2
table2.table1
table2.table3
table2.amount
Table 3
table3.name
Query is roughly:
select table3.name, sum(table2.amount), count(table2.table3) where
table2.table1.batch_id = batch_x groupby table2.table3
Achievable without a subquery of table1s that have a batch_id of batch_x?
I have this but not getting the results I’d like:
query = db.table1.batch_id == batch_x
query &= db.table2.table1 == db.table1.id
source_count = db.table2.id.count().with_alias(*'source_count'*)
source_sum = db.tabel2.amount.sum().with_alias(*'source_sum'*)
sources = db(query).select(
db.table2.table3,
db.table3.name,
source_count,
source_sum,
groupby=db.table2.table3
)
Essentially the records are in a batch, but the batch is stamped on the
parent record only. I need to count the children of the parent and get the
details of the reference records on those children.
Thanks
Ian
PS - the tables are more imaginatively named in reality, just simplified it
for here :)
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
