Reusing frequently used queries and joins

In DAL chapter on Web2py book, there's a section called  "Query, Sets, Rows".

It explains the use of the Query object in a very short way and doesn't explore its power.

Let's see some examples to optimize your application, shorten your code and don't repeat yourself.

Frequently used queries (i.e, common filtering by date):

db.define_table('show',
    Field('name', 'string'),
    Field('seen_on', 'datetime'))

rows1 = db((db.show.seen_on.month()==my_month) & \
            (db.show.seen_on.year()==my_year)) \
            .select(orderby='name')
print rows1

Now, if you want do the same query, plus filtering by name, too:

rows2 = db((db.show.seen_on.month()==my_month) & \
            (db.show.seen_on.year()==my_year) &\
            (db.show.name.like('Mary %')) \
            .select(orderby='name')
print rows2

See, date filtering part is duplicated. You can organize it in a better way, making your code like this:

db.define_table('show',
    Field('name', 'string'),
    Field('seen_on', 'datetime'))

db.show.my_filter = lambda m, y: db.show.seen_on.month()==m & \
                                  db.show.seen_on.year()==y

rows1 = db((db.show.my_filter(my_month, my_year)) \
            .select(orderby='name')
print rows1
 
rows2 = db((db.show.my_filter(my_month, my_year) & \
            (db.show.name.like('Mary %')) \
            .select(orderby='name')
print rows2

"Appending" personal filters to your table objects, you put them in a context and they goes everywhere with all other table properties and methods, throughout  your application.

You should make the same with joins. ;-)

Naturally this example doesn't show all benefits, but imagine some common cases:
a) Frequently used queries among your application: now they are standardized;
b) Complex and long queries: now they don't mess your code anymore. And are better readable;
c) Joins: now they are optimized and standardized.

As bigger your application and your team gets, better are the benefits.

What additional suggestion do you have to us? Tell us below.

Reusar queries usadas com frequência

No capítulo sobre DAL do livro de Web2py, existe uma seção chamada "Query, Sets, Rows".

Ela explica como usar o objeto Query de uma forma bem resumida e não explora todo o poder que ele tem.

Vamos ver alguns exemplos de como otimizar sua aplicação, encurtar seu código e praticar o princípio DRY (don't repeat yourself).

Consultas, ou queries, usadas com frequência (p.ex, filtragem comum por datas):

db.define_table('show',
    Field('name', 'string'),
    Field('seen_on', 'datetime'))

rows1 = db((db.show.seen_on.month()==my_month) & \
            (db.show.seen_on.year()==my_year)) \
            .select(orderby='name')
print rows1

Agora, se você quiser fazer a mesma query, mas filtrar também por nome:

rows2 = db((db.show.seen_on.month()==my_month) & \
            (db.show.seen_on.year()==my_year) &\
            (db.show.name.like('Mary %')) \
            .select(orderby='name')
print rows2

Viu? A parte que filtra por data está duplicada. Organize isso de um jeito melhor fazendo assim:

db.define_table('show',
    Field('name', 'string'),
    Field('seen_on', 'datetime'))

db.show.my_filter = lambda m, y: db.show.seen_on.month()==m & \
                                  db.show.seen_on.year()==y

rows1 = db((db.show.my_filter(my_month, my_year)) \
            .select(orderby='name')
print rows1
 
rows2 = db((db.show.my_filter(my_month, my_year) & \
            (db.show.name.like('Mary %')) \
            .select(orderby='name')
print rows2

"Adicionando" os filtros aos seus objetos table, você os coloca num contexto e eles estarão disponíveis em qualquer lugar da sua aplicação, assim como os outros métodos e propriedades.

Aconselho que você faça o mesmo com seus joins.

É natural que esse pequeno exemplo não mostre todos os benefícios dessa técnica, mas pense em alguns casos comuns:
a) Queries usadas com frequência: ficam padronizadas;
b) Queries longas e complexas: não deixam mais seu código bagunçado, e tornam-se mais fáceis de ler;
c) Joins: ficam otimizados e padronizados.

À medida que sua aplicação e sua equipe aumenta, você pode perceber melhor esses benefícios.

E você, tem alguma sugestão para melhorar? Colabore nos comentários.

Total control over your models

Every single web2py application that uses database, has its own models dir.

This directory is particularly important due to some characteristics:

1. models directory files ares executed in alphabetical order;
2. Your application's database tables definitions stay in it;
3. Objects defined by these files become available on your app's global scope. They can be accessed by controllers and views.

I warn you to pay attention to the first one: files are executed in alphabetical order.

Some newbies have dificulties with that, and tend to think this is strange. I did it, until I concluded this is the most intuitive way to things work. It gives me freedom to organize my app.

However, to avoid some mistakes this way can bring to your app, we need to adopt a clear and fool proof convetion to name models files.

I use this simple and efficient structure:

1. models/0_db.py
2. models/1_menu.py
3. models/5_models.py
4. models/5_validators.py

I rename the automatically created db.py file to 0_db.py. So, I guarantee all basic definitions are executed before anything else. The DAL object, who provides all data access funcionalities, is created in this script.

I also rename menu.py to 1_menu.py forcing all menu definitions to be executed after DAL object instantiation.

With these modified names, I'm sure that all the code I'll develop will be executed after the scaffolding app web2py generated to me.

From that point I write models/5_models.py with my table definitions. Nothing but db.define_table() commands.

Validators are written in models/5_validators.py. But, why separated from model definitions? First, because web2py recommends you don't mix validators with  db.define_table(). Second, because at this point I'm certain all tables are already created and all cross references between them will work with no need to adjust definition sequences. Sometimes it messes my scripts.

When I have many tables, I can separate them in various scripts, i.e, 5_models_accounts.py, 5_models_finances.py, etc. And, the same way, their validators.

Here, your creativity can fly, but remeber some principles:

Explicit is better than implicit.
Simple is better than complex.
Flat is better than nested.
Readabilty counts.
In the face of ambiguity, refuse the temptation to guess.