pydal-20241204.1 with_alias + as_list broke backward compatibilty with old code.

[mbelletti]

The latest versions of pydal introduced a breaking change in the behavior of the with_alias method used with as_list. In the previous version (20241111.2), when using with_alias with as_list, the aliased fields were returned as keys of the dict representing the record, but in the latest version, they are only available in the _extra dict.

It seems the different behavior is introduced here: 16c9395

This change breaks backward compatibility with existing code that relied on the old behavior.

from pydal import DAL, Field
db = DAL('sqlite:memory')
db.define_table('test',
                Field('a'),
               )
for i in range(3):
    db.test.insert(a=str(i))
db.commit()  
print(db(db.test.id).select(db.test.a.with_alias('my_col_name')).as_list())

pydal-20241111.2

[{'my_col_name': '0', '_extra': {'"test"."a" AS my_col_name': '0'}},
 {'my_col_name': '1', '_extra': {'"test"."a" AS my_col_name': '1'}},
 {'my_col_name': '2', '_extra': {'"test"."a" AS my_col_name': '2'}},

pydal-20241204.1

[{'_extra': {'my_col_name': '0'}},
 {'_extra': {'my_col_name': '1'}},
 {'_extra': {'my_col_name': '2'}},

[laundmo]

I found out what changed:

pydal/pydal/adapters/base.py

Lines 295 to 296 in 16c9395

	if isinstance(field, Expression) and field.op == self.dialect._as:
	colname = field.second

This now changes colname from "test"."a" as my_col_name to my_col_name
That means, later, the following regex match evaluates to None:

pydal/pydal/adapters/base.py

Lines 307 to 310 in 16c9395

	new_column_match = self._regex_select_as_parser(colname)
	if new_column_match is not None:
	new_column_name = new_column_match.group(1)
	new_row[new_column_name] = value

And therefore the last line in that code block never runs.

I made a PR which improves the logic a lot, which also fixes this issue for single-table queries.

your query would be parsed as {"test": {"my_col_name": '0'}} and then flattened to {"my_col_name": '0'} since it now uses the original fields table as a "table name" for the alias.

this means a multi-table query with both column aliased would result in this, which is still a breaking change:
{"test": {"my_col_name": '0'}, "test2": {"my_other_col_name": '1'}} which won't be flattened as its 2 tables

[mbelletti]

Maybe to don't' break old code could be possibile to add a flag in .with_alias method?

flatten=True by default

or something like that that add the same fake table name just to flat multi-table query?

[laundmo]

The intended way to write queries like this would be to either write queries like this:

db(db.table1).select(db.table1.id.with_alias("data.tbl1id"), db.table2.id.with_alias("data.tbl2id"), join=db.table2.on(db.table1.id == db.table2.ref_tbl1))

which selects both to a single result table called "data" which will be flattened normally (single table: flat, multi-table: nested, instead of the old "nested but aliases are flat")

or just to access the data in its nested form.

i'm not sure if @mdipierro has any idea how to resolve this without, essentially, reverting back to the old behaviour entirely

[mbelletti]

This monkey patch could be an easy workaround for existing code that doens't support dot notation:

from pydal import Field

if not hasattr(Field, 'smart_alias'):
    Field.smart_alias = lambda self, fname=None, tname=None: self.with_alias(
        f"_.{fname if fname else self.name}" if not tname 
        else f"{tname}.{fname if fname else self.name}"
    )

using it:

q = (db.person.id == db.superhero.real_identity)

fields = [
    db.person.id.smart_alias(), 
    db.person.name.smart_alias(), 
    db.superhero.name.smart_alias('superhero'), 
]

db(q).select(*fields).as_list()

[{'id': 1, 'name': 'Clark Kent', 'superhero': 'Superman'},
 {'id': 2, 'name': 'Peter Park', 'superhero': 'Spiderman'},
 {'id': 3, 'name': 'Bruce Wayne', 'superhero': 'Batman'}]