Thanks Richard,
If you don't mind and have time, and more specific example may help. For
example when looking on postgresql views along with your example it has
hard coded values, I need dynamic values.
Currently this works for getting the data, I will also add a date range
query parameter(s) which is an easy addition if I didn't need to use
SQLFORM.grid():
query = """
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
"""
calculations = db.executesql(query, placeholders=dict(distributor_id=
distributor.id))
It would be nice if SQLFORM.grid() would except more advanced query's I am
going to read through the code for it to see if their is any way to extend
it. Or even if we can just feed it the data and it just displays what you
send it.
--
Regards,
Bruce
On Tue, Feb 14, 2012 at 5:16 PM, Richard Vézina <[email protected]
> wrote:
> 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
>>
>
>
--
--
Regards,
Bruce Wade
http://ca.linkedin.com/in/brucelwade
http://www.wadecybertech.com
http://www.warplydesigned.com
http://www.fitnessfriendsfinder.com
