OK - I posted earlier on this thread
https://groups.google.com/forum/#!topic/web2py/8yypDdtmG9g and vented some
frustration re the handling of computed columns. Rather than clutter up the
old threat I'm creating a new thread that contains the code that solves my
problem.
Feel free to offer alternative and/or more general alternatives - my code
is very case specific.
I reworked my controller method ('gettagcounts' to avoid any references to
backwardly incompatible references such as '_extra'. The following code is
what I came up with. I have included some copious comments here to help
anyone who runs into this in the future.
(FWIW - So far I have found the Web2py examples too cryptic with no
guidelines for usage and constraints - they seem to assume that you already
know Web2py and it's hard to find specific examples - this can be
frustrating and time consuming for those just testing the web2py waters).
On with the code!
def gettagcounts():
'''
DAL Example Combining Inner Join Query and a computed column.
The use case here is:
- find the tag values and related counts for a set of blog posts
- return custom result set containing id, name and count
This example shows how to construct a user friendly response that
avoids any references to the DAL's '_extra' artifacts
The expected Json result has this format:
{"tagsummary": [
{"count": 4, "name": "Places", "id": 1},
{"count": 2, "name": "Cars", "id": 2},
{"count": 2, "name": "Boats", "id": 3}
]}
'''
# Construct the required DAL objects
# Note that the count will be 'lazy loaded' when the query is executed
count = db.tagref.tag.count()
# the following DAL statement ressolves to an inner join sql query
# Note that I am *NOT* using the 'as_select()' method here
#
tagsummary = db(db.tagref.tag==db.tag.id).select(db.tagref.tag,
db.tag.name, count, \
groupby=db.tagref.tag)
#
# BTW - Here's the generated SQL string - you could just exec this as
# a SQL statement w/out using the DAL
# 'SELECT tagref.tag, tag.name, COUNT(tagref.tag) FROM tagref, tag
# WHERE (tagref.tag = tag.id) GROUP BY tagref.tag;'
#
# create an array for a custom list of dicts
# containing the required columns
# note the syntax for the computed 'count' field
# In the DAL computed fields are handled separately from table fields
tlist = []
for item in tagsummary:
d = {}
d['count'] = item[count] # <-- note the syntax
d['name'] = item.tag.name
d['id'] = item.tagref.tag
tlist.append(d)
# return custom results for consumptionj by the view
return dict(tagsummary=tlist)
# Finally - in case anyone is interested here is the json response
populated
# with some test data - nice and easy to handle on the client side.
# No hidden '_extras' included <g>.
