Count record in SQLFORM (web2py)

Question

I'm trying to create a SQLFORM.grid in web2py that group by 2 fields and count them up, but I couldn't figure it out how to do it.

Model

db.define_table('invocados',
                Field('modulo_servico', 'string', label='Módulo (Serviço)', default=IS_LENGTH(2)),
                Field('servico', 'string', default=IS_LENGTH(8)),
           )

Controller

fields=(db.invocados.modulo_servico, db.invocados.servico)
Invocados=SQLFORM.grid(db.invocados.modulo_servico != db.invocados.modulo_consumidor, details=False, fields=fields, groupby=(db.invocados.modulo_servico | db.invocados.servico) , paginate=15)

I'm trying to count all records grouped by modulo_servico and servico. I've tried:

fields=(db.invocados.modulo_servico, db.invocados.servico, db.invocados.count())

but it doesn't work.

Can you help?

Sadly, there is only a few people that use web2py...

bitfhacker
– bitfhacker

2015-10-09 00:13:02 +00:00
Commented Oct 9, 2015 at 0:13 — bitfhacker
– bitfhacker, Commented Oct 9, 2015 at 0:13

James Burke · Accepted Answer · 2015-10-15 12:56:23Z

1

You could use a link in your grid to display the result:

SQLFORM.grid(db.invocados.modulo_servico != db.invocados.modulo_consumidor, 
    details=False, 
    fields=fields, 
    groupby=(db.invocados.modulo_servico | db.invocados.servico) , 
    paginate=15,
    links=[dict(header='Count', body=lambda row: row.modulo_servico + row.servico)])

answered Oct 15, 2015 at 12:56

James Burke

2,4171 gold badge28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bitfhacker Over a year ago

your answer was not 100% correct but I marked your answer as usefull because you pointed me to the right direction. I edit my previous answer and included your solution with the correction. thx

bitfhacker · Accepted Answer · 2015-10-17 11:05:03Z

Until now I've been using web2py and I concluded that the best solution is to return the data in the controller and build a dedicated view, so that you can sort the results by Count field, like this:

Controller:

def servexpostos():
    q = db.invocados.modulo_servico != db.invocados.modulo_consumidor
    rows = db(q).select(db.invocados.modulo_servico, db.invocados.servico, db.invocados.servico.count(), groupby=db.invocados.modulo_servico | db.invocados.servico)
    return locals()

View:

<div class="col-md-12">
<table class="table table-striped table-hover">
    <thead>
        <tr>
            <th>Módulo</th>
            <th>Nome Implementação</th>
            <th># invocações de outros módulos</th>
        </tr>
    </thead>
    <tbody>
{{for i,row in enumerate(rows):}}
{{if i==items_per_page: break}}
        <tr>
            <td>{{=row.invocados.modulo_servico}}</td>
            <td>{{=row.invocados.servico}}</td>
            <td align="middle"><span class="badge">{{=row._extra['COUNT(invocados.servico)']}}</span></td>
        </tr>        
{{pass}}
    </tbody>
</table>

This solution have more more effort because I don't use SQLFORM.grid and I must control pagination, sorting, etc (common tasks of a web developer)...

A simpler aproach, but with no possibility of ordering the results by the "Count" field, is to use the links argument, of a SQLFORM.grid, and add a subquery in each row to count the records.

Example:

SQLFORM.grid(db.invocados.modulo_servico != db.invocados.modulo_consumidor, 
    details=False, 
    fields=fields, 
    groupby=(db.invocados.modulo_servico | db.invocados.servico) , 
    paginate=15,
    links=[dict(header='Count', body=lambda row: db(db.invocados.servico == row.servico).count())])

Collectives™ on Stack Overflow

Count record in SQLFORM (web2py)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related