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.