Creating a HAVING COUNT(column) > 2 clause in pyDAL

Question

I have the following pyDAL table:

market = db.define_table(
    'market',
    Field('name'),
    Field('ask', type='double'),
    Field('timestamp', type='datetime', default=datetime.now)
    )

I would like to use the expression language to execute the following SQL:

SELECT * FROM market 
GROUP BY name
ORDER BY timestamp DESCENDING
HAVING COUNT(name) > 1

I know how to do the ORDER BY and the GROUP BY:

db().select(
        db.market.ALL,
        orderby=~db.market.timestamp,
        groupby=db.market.name
    )

but I do not know how to do a count within a having clause even after reading the section in the web2py book on the HAVING clause.

Answer 1

The count() function returns an expression which can be used both as a field in the select query, and to build an argument to the query's having parameter. The Grouping and counting section from the web2py manual has a few hints on this topic.

The following code will give the desired result. The row objects will hold both the market objects and their respective row counts.

count = db.market.name.count()
rows = db().select(
    db.market.ALL,
    count,
    groupby=db.market.name,
    orderby=~db.market.timestamp,
    having=(count > 2)
    )