using with_alias() for one field in .select() causes other fields to be nested

[laundmo]

When querying with the db().select(db.table.field1, db.table.field2) I can access row.field1 and row.field2 as I would expect.

But if I instead alias one of the fields, like this: db().select(db.table.field1, db.table.field2.with_alias("alias")) I suddenly cannot do this any more:
KeyError: 'field1'
Comparing the representations of the rows, for some reason the DAL decided to nest my non-aliased fields inside the table name. This is highly unintuitive behaviour, and I wasn't able to find documentation for it.

without alias: <Row {'field1': 1, 'field2': 1}>

with alias: <Row {'table': {'field1': 1}, 'alias': 1, '_extra': {'"table"."field2" AS alias': 1}}>
Additionally, it seems foreign key links are also lost entirely when using an alias.
I'm using Postgres with psycopg2.

Greetings

[Massimo]

Looks like a bug. Please create a pydal ticket for this

[laundmo]

I finally got around to writing the issue for pydal

https://github.com/web2py/pydal/issues/720

PS:
- theres a lot of open issues on pydal without answers.
- the pydal docs on readthedocs are very out of date: "latest Last built 5 years, 10 months ago"

[Massimo]

Turns out this is a big can of worms.

I made a branch that addresses it 

https://github.com/web2py/pydal/pull/new/with_alias

Unfortunately it breaks pydal tests and I am not going to merge until I give it some more thought.

Let me explain the problem.

The rows in db(...).select(...) do not have a fixed structure as they depend on what is being selected.

- The result of a select involving one table if a list of dict of columns of the table { "field1": ..., "field2":...}

- The result of a select involving more than one one table is a list of dict of dicts {"table1: { "field1": ..., "field2":...}, "table2": ...}

- Expressions columns go in _extra {"table1: { "field1": ..., "field2":...}, "_extra": ...} which also make the output a dict of dicts

(this is because the expression is technically not a table column)

- Aliased expressions also go in _extra for the same reason above. It is not obvious to pydal that an aliased expression belongs to a single table and in general it does not.

**Aliasing was an afterthought introduced to handle multiple joins of the same table, not to rename fields.**

One can alias an expression that is just a field (although that is pointless). In this case things get confusing because one expects the aliased field to go in the table, but it goes in _extra and changes the dict structure. By adding any expression to the select (even if it is a single aliased field from one table) converts the rows from a dict to a dict of dicts.

Internally a rows is always a dict of dicts but it collapses to a single dict when (at top level) there is a single key (a single column and no expressions). In the branch above I change this behavior so that is collapses to a single dict when there is a single column even if there are expressions, by joining the table dict with the _extra dict.

This makes the case in laundmo's example behave like he expects, but it breaks existing tests and therefore it is not backward compatible. Also I am not convinced this is more intuitive.

Need to sleep on this...

Massimo

[Massimo DiPierro]

Ignore the previous email. I found a way to make it work that is backward compatible.

Using laundmo example:

  rows = db().select(db.mytable.field1, db.mytable.field2.with_alias("alias"))

will put the aliased field in rows[k]["_extra"]["alias"] = value

BUT

rows = db().select(db.mytable.field1, db.mytable.field2.with_alias("mytable.alias"))

will put the aliased field in rows[k]["mytable"]["alias"] = value

and since there is only one item in rows[k] will collapse it into

rows[k] = {"mytable": {"field1": ..., "alias": ...}}

In this way, by prepending "tablename." to the alias name, you can move any aliased expression out of _extra and into the table header.

I merged this as I think this is the best we can do while remaining backward compatible.

If no strong objection I will document it tomorrow.

Massimo

--
You received this message because you are subscribed to the Google Groups "py4web" group.
To unsubscribe from this group and stop receiving emails from it, send an email to py4web+un...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/py4web/e8afd1ab-9e65-49af-9608-17d0f970c18en%40googlegroups.com.

[laundmo]

note: sorry for the double-send, i accidentally deleted the mail after sending.

Hello Massimo,

i noticed this only now, but it seems this change is entirely incompatible with at least postgresql. See the following error i get when trying to do this:
psycopg2.errors.SyntaxError: syntax error at or near "."
LINE 1: SELECT "tbl"."col1", "tbl"."col2" AS test.test FROM "s...
                                                       ^ 
I've also noticed some other quite annoying issues with with_alias():
- even using it at top level still keeps the entire expression name in _extra, causing bad .as_dict, .as_json, .as_csv etc. output
- its ignored entirely when used below the top level: db().select((db.tbl.col1.with_alias("test") | db.tbl.col2))

I've started using db.executesql(db()._select(myquery)) since its the only way i found being able to recieve the datastructure i queried for.

Greetings,
- laund

[Massimiliano]

Hi Massimo, 

this solution is very flexible, but all the old code is broken (ref: issue #727). 

Is there a way to get back the old behaviour or a way to prepend automatically a (fake?) tablename if there are no cols but only _extras? 

Thank you very much for your help.

To view this discussion visit https://groups.google.com/d/msgid/py4web/CAJYrZr%3DNEfB_%3Dx3ZJCuz6S4W3n2n9xbQGpyW9GZfu%3D%2BEhzampw%40mail.gmail.com.

--

Massimiliano