On Nov 8, 2007, at 10:14 PM, Heling Yao wrote:
>>> Question/Enhancement:
>>> 2. I generated an app using the webtest.sqlite db, now how do I
>>> query
>>> multiple tables? say I'd like to query the total amount of each
>>> order,
>>> which means I need to join order table and orderitems table. If this
>>> can easily be done, it would be good enough for me to drop MS
>>> Access.
>>
>> Do you have bizobjs defined for those tables? If so, it is
>> straightforward to just add those bizobjs as children of the customer
>> bizobj, so that they are automatically requeried with the current
>> customer PK. You can then add a method in your orders bizobj to
>> return the sum of all the orditems related to it, and then call the
>> bizobj.scan() method to total all the orders for that customer. I can
>> certainly help you with setting this up if my explanation sounds too
>> confusing.
>
> I do need more guidance with the bizobj part. Never touched that
> before. Could you give me a simple example, and maybe some pointers.
> I'll go and listen to your bizobj screencast now.
OK, this is rough, since I don't have the app handy to test it, but
you should get the general idea.
First option is to change the SQL for the bizobj, but since you are
using the datanav framework, that gets much dicier. I've never been
able to modify datanav queries with anything but the most trivial
changes and have them work; Paul is much more experienced here, and
claims you can do just about anything with datanav. Perhaps he can
help you here.
The simplest way I can think of is to define a method in the
Orditems bizobj that will sum up the orders for a give order_fk
value. So add this method to the biz/Orditems.py:
def sumForOrder(self, orderPK):
crs = self.getTempCursor()
crs.execute("""select sum(unit_price * quantity) as total from
orditems
where orditems.order_fk = %s """,
params=(orderPK, ))
return crs.Record.total
Note the use of a temporary cursor. This is important, because we
don't want to erase the data in the current dataset, which would
happen if we executed this directly. There is an alternative that
accomplishes the same thing, but until a few minutes ago it didn't
support parameters. I've just posted an update to the bizobj and
cursor classes that corrects that, so you could also write:
def sumForOrder(self, orderPK):
crs = self.executeSafe("""select sum(unit_price * quantity) as
total from orditems
where orditems.order_fk = %s """,
params=(orderPK, ))
return crs.Record.total
Now we have to let the Orders bizobj "know" about the Orditems
bizobj. So in Orders.py, modify afterInit() to read:
def afterInit(self):
self.super()
# Get a reference to the biz module
biz = self.Application.biz
import biz.Orditems as BizOI
# Create the orditems bizobj
bizItems = BizOI(self._connection)
bizItems.LinkField = "order_fk"
self.addChild(bizItems)
Now we're going to define two methods in the Order bizobj: one to
get the total for the related Orditems, and one to iterate through
all the current Orders and add them up. Here's the first:
def sumItems(self):
bizItems = self.getChildByDataSource("orditems")
total = bizItems.sumForOrder(self.getPK())
self._itemTotal += total
Now in order to call that for all Orders in the current selection,
we use the bizobj's scan() method. What this does is iterate through
all the records in the bizobj, calling the passed function for each
one. It then restores the current record position. So this method
starts by zeroing out the _itemTotal attribute, calling the scan()
method (which calls self.sumItems() for each Order record), and then
returning the value in _itemTotal.
def sumAllOrders(self):
self._itemTotal = 0
self.scan(self.sumItems)
return self._itemTotal
I hope this illustrates the general approach I had in mind for my
initial comment. There are lots of places where there could be
optimizations; it certainly would be faster if the sum of the related
orditems could be in the original query; then we would just have to
sum those.
-- Ed Leafe
-- http://leafe.com
-- http://dabodev.com
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/dabo-users/[EMAIL PROTECTED]
