[web2py] Joined query help

[Ian Ryder]

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 web2py+***@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Richard Vézina]

You may consider db.executesql(""" YOUR SQL QUERY """)

You may also have a look to the generated query with ._select(...). If you
add an underscore before the .select web2py return a string of the
generated query instead of the results of the query... So you can see what
is the issue and fix your web2py query...

But basically I think your aggregate are wrong... I am pretty sure that
source_count and source_sum are made against the table 2 without
considering the join with table 1...

Also... I really not understand you plain SQL query :

table2.table1.batch_id

????

Richard

Post by Ian Ryder
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
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?
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 :)
--
- 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
For more options, visit https://groups.google.com/d/optout.

--
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 web2py+***@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Massimiliano]

What about table3 in your query?

How is linked to table2?

To see the generated sql you can use

db(query)._select(...)

Post by Ian Ryder
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
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?
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 :)
--
- 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
For more options, visit https://groups.google.com/d/optout.

--
Massimiliano
--
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 web2py+***@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.