Json, Ajax, Inner Join , Group By and '_extra' revisited - code example included

[BrendanC]

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>.

[James Burke]

Thank you Brendan. 5 years later this is still an issue.

I am using the pyDAL library with the bottle framework. It is very nasty to try and access the count value in JavaScript buried under, e.g. _extra.count(tagref.tag)

If you just want to get rid of the _extra part, you can simple use

for item in tagsummary:
    item['count'] = item[count]

And then if you wanted to return json you can still use.

return tagsummary.json()