Re: [web2py] Joined query help
What about table3 in your query? How is linked to table2? To see the generated sql you can use db(query)._select(...) On Thu, Jul 30, 2015 at 12:50 PM, Ian Ryder i.ry...@appichar.com.au wrote: 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+unsubscr...@googlegroups.com. 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [web2py] Joined query help
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 On Thu, Jul 30, 2015 at 6:50 AM, Ian Ryder i.ry...@appichar.com.au wrote: 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+unsubscr...@googlegroups.com. 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Joined query help
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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
