view at backend level :
CREATE VIEW v_comedies AS
SELECT ID, f1, f2, f3 --, etc.
FROM films
WHERE kind = 'Comedy';
#Modified from :
http://www.postgresql.org/docs/8.1/static/sql-createview.html
# Example
Then in web2py :
#Model
db.define_table('v_comedies',
Field('ID','id'), # you need to specify your ID field name only if
different then 'id' default web2py id fieldname
Field('f1',TYPE),
Field('f2',TYPE),
Field('f3',TYPE),
migrate=False,
format=%(f1)s)
db.define_table('your_real_table',
Field(...),
...)
Then if you have a FK in your_real_table or even you id field you can get
out your represent from your v_comedies SQL view...
v_comedies is a pretty bad example...
But you can imagine a aggregate select that you define as a SQL view where
there is a FK or ID field on which you can base your reprensent lambda
function to retreive the aggregate you want.
Hope it's help...
I could show a better example tomorrow base on your particular case...
:)
Richard
On Tue, Feb 14, 2012 at 7:42 PM, Bruce Wade <[email protected]> wrote:
> Hi Richard,
>
> Do you have an example of this using postgresql?
>
> Thanks,
> Bruce
>
>
> On Tue, Feb 14, 2012 at 3:59 PM, Richard Vézina <
> [email protected]> wrote:
>
>> Hi Bruce,
>>
>> When I have stuff to hard to me and I feel I will loose time to write in
>> web2py what I usually do is to write a backend view (SQL) and define the
>> SQL view has a web2py db that I use in my reprensent. Off course, you can't
>> add record or update, but you can, read and select. By setting it to
>> migrate false there is no ticket from web2py.
>>
>> Richard
>>
>>
>> On Tue, Feb 14, 2012 at 6:37 PM, Bruce Wade <[email protected]> wrote:
>>
>>> Hi,
>>>
>>> We have a pretty complicated query that uses multiple summed fields and
>>> I would like to use SQLFORM.grid to display them. Two situations first I
>>> need to figure out how to translate this query into DAL (unless grid can
>>> handle executesql?), then need to know how to use the sum() like you would
>>> with a normal query.select().
>>>
>>> Normal SQL
>>> """
>>> select to_char(report_date,'yyyy-mm') report_date,distributor_id,
>>> sum(sponsorbonus) sponsorbonus, sum(leadbonus) leadbonus, sum(binarybonus)
>>> binarybonus,
>>> sum(matchingbonus) matchingbonus, sum(growingbonus) growingbonus,
>>> sum(adviewpayback) adviewpayback, sum(adpackcomm) adpackcomm,
>>> sum(kvalue_deduction) kvalue_deduction, sum(group_management_fee)
>>> group_management_fee
>>> from bonus_daysummary
>>> where distributor_id = %(distributor_id)s
>>> group by to_char(report_date,'yyyy-mm'), distributor_id
>>> """
>>>
>>> DAL so far:
>>> sum_sponsor_bonus = db.bonus_summary.sponsorbonus.sum()
>>> sum_lead_bonus = db.bonus_summary.leadbonus.sum()
>>> sum_binary_bonus = db.bonus_summary.binarybonus.sum()
>>> sum_matching_bonus = db.bonus_summary.matchingbonus.sum()
>>> sum_growing_bonus = db.bonus_summary.growingbonus.sum()
>>> sum_adview_payback = db.bonus_summary.adviewpayback.sum()
>>> sum_ad_pack_comm = db.bonus_summary.adpackcomm.sum()
>>> sum_kvalue_deduction = db.bonus_summary.kvalue_deduction.sum()
>>> sum_group_manage_fee = db.bonus_summary.group_management_fee.sum()
>>>
>>> query = db(db.bonus_daysummary.distributor_id == distributor.id)
>>>
>>> monthlyBonusReport= SQLFORM.grid(
>>> query, paginate=10, deletable=False, editable=False,
>>> details=False, csv=True, formname='monthlyBonus',
>>> )
>>> --
>>> --
>>> Regards,
>>> Bruce Wade
>>> http://ca.linkedin.com/in/brucelwade
>>> http://www.wadecybertech.com
>>> http://www.warplydesigned.com
>>> http://www.fitnessfriendsfinder.com
>>>
>>
>>
>
>
> --
> --
> Regards,
> Bruce Wade
> http://ca.linkedin.com/in/brucelwade
> http://www.wadecybertech.com
> http://www.warplydesigned.com
> http://www.fitnessfriendsfinder.com
>
