How to write dynamic part of a select?

[Sébastien Loix]

Hi to all

I am wondering how to ask dynamically the fields inside the select()

I know we can do dynamic query but how do we do dynamic select?

I want to avoid to do this if else (just an example):

if ask_for_lastname:

    user = db(db.auth_user.id == user_id).select(db.auth_user.first_name, db.auth_user.last_name).first()

else:

    user = db(db.auth_user.id == user_id).select(db.auth_user.first_name).first()

Thank you for the help

[Limedrop]

You can do this by building a list of fields and then using the python unpack operator.  For example:

field_list = [db.auth_user.first_name]
if ask_for_lastname:
    field_list.append(db.auth_user.last_name)
rows = db(query).select(*field_list)

See https://docs.python.org/2/tutorial/controlflow.html#unpacking-argument-lists

[Sébastien Loix]

Great thank you!

Quiet new to Python so this one will help me a lot...

[Sébastien Loix]

One last thing, any idea if it is also possible to dynamically add "left=[.....], orderby=...."

For now I can't as it (logically) tells me that 'append() takes no keyword arguments'

thank you! 

On Wednesday, 15 April 2015 00:52:53 UTC+2, Limedrop wrote:

[Limedrop]

That's a bit more involved.  For groupby you need to build a query using | and for left you need a list of db.table.on().  Have a look at the sample code below for a rough outline of the process.

    grouping_fields = [db.table.field1, db.table.field2]
    groupby = grouping_fields[0]
    if len(grouping_fields) > 1:
        for g in grouping_fields[1:]:
            groupby = groupby|g
    fields = fields + grouping_fields # the grouping fields must be selected
    left = [db.table1.on((db.table1.id == db.table2.table1_id)), db.table1.on((db.table1.id == db.table3.table1_id))]
    rows = db(query).select(*fields, groupby=groupby, left=left)