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
