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 :)

[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

--
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+un...@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(...)

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

--

Massimiliano