Hello,
I've got a problem and don't know if it's (easily) solvable in web2py.
There're two tables, one for properties and one for their valuations (one
property can have zero to multiple valuations):
db.define_table('property',
Field('property_id', 'id'),
Field('name', 'string'),
Field('purchase_price', 'float'))
db.define_table('property_valuation',
Field('property_id', 'reference property'),
Field('valuation_date', 'date'),
Field('amount', 'float'))
Now I want as a result a table with one record for each property with the
purchase price and the last valuation amount (and date). In SQL (e.g.
SQLite) it would be
with t1 as (
-- get last valuation for each property (id and date)
select property_id, max(valuation_date) as last_valuation_date
from property_valuation
group by property_id
), t2 as (
-- get last valuation record for each property
select pv.*
from property_valuation pv
inner join t1
on t1.property_id = pv.property_id
and t1.last_valuation_date = pv.valuation_date
)
select
p.name
,p.purchase_price
,t2.valuation_date
,t2.amount
from property p
left join t2
on t2.property_id = p.property_id
How is this best done in web2py? I'm struggling with many similar problems
where the analysis are a little bit more difficult than just a simple count
etc. I have no problems formulating the solution with e.g. SQL but want to
use the SQLFORM.grid function on the other side for representing the
results to have the same "appearance" for end user. So I'm thinking about
to create views in the database and than create table definitions with
"migrate=False". What do you think about that approach?
Or should I leave the web2py DAL as soon as I've more complex analytics, do
them in SQL directly or e.g. pandas and use something like Javascript
DataTable for representing results? (a feature for downloading results as
csv would also be nice)
stex
--
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.
