Search code examples
sqliteweb2py

How to use concat in web2py dal select query?

I've a table like below,

| id | Name |

| 1 | foo |

| 2 | bar |

I want to write a select query which should return some prep-ended text before the id. So my output should be something like, val_1 & val_2. I couldn't see any concat method in web2py select query. To achieve my requirement I need to manipulate the result separately. Is there a way to form the select query in web2py to use SQL concat?

Solution

  • The .select() method can take SQL expressions as arguments in addition to fields, so you can do:

    val = "'val_' || mytable.id"
rows = db(db.mytable).select(val)
print rows[0][val]

    Note, when using an expression in the select, the resulting value is stored in the row object with a key equivalent to the SQL expression itself, hence the use of [val] to extract the value from the row object.

    As an alternative to the above approach, you might instead consider using a computed field or a virtual field.