Alias column names (akin to AS clause) in db select

[Brendan Barnwell]

When running queries that do joins, I often find the format of the result somewhat cumbersome, with its nested structures representing the different tables in the join.  If I were writing the query in raw SQL, I would probably use "AS" clauses to select just the columns that I want, with simple "top-level" names.  Something like

Select Table1.foo as stuff, Table2.bar as blah from Table1, Table2 ...

Is there a way to do this with Web2py?  It would seem natural to specify this inside the select call, something like

db(db.Table1.foo=db.Table2.foo).select(db.Table1.foo.alias('foo'), db.Table2.bar.alias('blah'))

[Brendan Barnwell]

Any ideas on this?  I continue to be especially frustrated at having to unpack the nested structure that occurs when I select using a join.  I want to be able to easily "flatten" the structure into a single list of column names, rather than the existing structure where column names are nested inside table names.

[Paolo Caruccio]

Maybe the web2py book could help you:

http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=with_alias#Self-Reference-and-aliases

[Brendan Barnwell]

On Monday, July 31, 2017 at 1:25:31 PM UTC-7, Paolo Caruccio wrote:

Maybe the web2py book could help you:

http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=with_alias#Self-Reference-and-aliases

That appears to only be talking about using aliases for tables, and specifically to be able to create and query tables that reference one another.  What I'm describing is conceptually much simpler than that: I just want to take a query that already works and give my own names to the columns of the result.

[Paolo Caruccio]

Maybe 

def test():
    rows = db(db.Table1.foo==db.Table2.foo).select(db.Table1.foo.with_alias('foo'), db.Table2.bar.with_alias('blah'))
    for row in rows:
        print row.foo, row.blah
    return locals()

should work.

Another way - if you can rewrite tables definitions - is to use the "rname" field value. From the web2py book http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?#Field-constructor 

rname provides the field was a "real name", a name for the field known to the database adapter; when the field is used, it is the rname value which is sent to the database. The web2py name for the field is then effectively an alias.

So with 

db.define_table('Table2', Field('blah', 'string', rname="bar")

the query becomes

db(db.Table1.foo==db.Table2.foo).select(db.Table1.foo, db.Table2.blah)

If you want alias fields in a query without joins you could write

rows = db(db.Table2.id>0).select('bar AS blah')

[Val K]

try this trick:

db.Table1.blah = db.Table1.bar.clone(name = 'blah', _rname='bar')
db(...).select(db.Table1.blah)