CODEWITHSUNDEEP
pythonweb2py
Sebastian
Sebastian

Reputation: 1263

web2py DAL complex query

I have a raw SQL query that i would like to translate to a Web2py DAL query. Is it possible? translating can make more inefficient?

tables are:

t_proyecto ( f_nombre, ...)
t_informe  ( f_proyecto [reference to t_proyecto], ....)
t_gasto    ( f_nombre, f_monto, f_informe [reference to t_informe])

The idea is that one project (t_proyecto) has many reports (t_informe) and each report can declare/have many expenses (t_gasto). I want to have the total expenses for each project.

This SQL works fine, but i would like to know the corresponding DAL expression:

result=db.executesql('SELECT f_nombre, SUM(f_monto)
                      FROM t_proyecto,
                           (SELECT f_proyecto, f_monto 
                            FROM t_informe, t_gasto 
                            WHERE t_gasto.f_informe==t_informe.id) as AuxTable

                      WHERE  t_proyecto.id==AuxTable.f_proyecto 
                      GROUP BY t_proyecto.f_nombre;')

I've tried several things, but none seemed to work, and i ended up writing this raw SQL. Any help to solve it and understand the logic would be appreciated

Thanks!

Upvotes: 0

Views: 3016

Answers (1)

mpranjic
mpranjic

Reputation: 61

I don't think you can join table and subquery, but this should give the same result

SELECT f_nombre, SUM(f_monto)
FROM t_proyecto, t_informe, t_gasto 
WHERE t_gasto.f_informe==t_informe.id
AND t_proyecto.id==t_informe.f_proyecto 
GROUP BY t_proyecto.f_nombre;

DAL query:

db((db.t_proyecto.id==db.t_informe.f_proyecto) &
   (db.t_informe.id==db.t_gasto.f_informe)).select(db.t_gasto.f_monto.sum(), db.t_proyecto.f_nombre, groupby=db.t_proyecto.f_nombre)

Upvotes: 1

Related Questions