Pydal: How to properly construct single-value subselect fields

[laundmo]

Hi,

In SQL, we can use a subselect like this instead of a join:

SELECT
    thing.name,
    (SELECT name FROM person WHERE person.id = thing.owner_id) AS owner_name
FROM 
    thing;

This example is a direct replacement for a join, but there are more complex usecases where this kind of subselect becomes very useful. Besides: If this worked, i could use it in a Column.required_fields for Table, where adding joins with aggregate isn't otherwise supported.

I've tried doing something similar with Pydal, but ._select() produces invalid syntax (lacking parantheses around subselect) and .nested_select() doesn't work as a Field (Select object has no attribute 'type').

I was work around the invalid syntax from trying to use ._select() since it returns a string, but it still produces wrong results, as it puts both tables in the FROM clause.

By running my test script, based on the thing.owner_id -> person example from the documentation, i would expect the output to be this:
Boat,Alex
Chair,Alex
Shoes,Bob

But every working subselect version results in this:
Boat,Alex
Chair,Alex
Shoes,Alex

Heres my testing script:

from pydal import DAL, Field

db = DAL("sqlite:memory")

db.define_table("person", Field("name"))

db.person.insert(name="Alex")

db.person.insert(name="Bob")

db.person.insert(name="Carl")

db.define_table("thing", Field("name"), Field("owner_id", "reference person"))

db.thing.insert(name="Boat", owner_id=1)

db.thing.insert(name="Chair", owner_id=1)

db.thing.insert(name="Shoes", owner_id=2)

db.commit()

print("partial workaround 1: limited, sends many queries:")

for row in db(db.thing).select():

    print(db._lastsql[0])

    print(row.name, row.owner_id.name)

print(db._lastsql[0])

print("partial workaround 2: limited")

direct_join = db(db.thing).select(

    db.thing.name, db.person.name, left=db.person.on(db.thing.owner_id == db.person.id)

)

print(db._lastsql[0])

print(direct_join)

# Exception: Select object has no attribute 'type'

# str() around subselect fixes syntax, but has 'thing' in FROM

# which leads to wrong results.

# also: alias doesn't work as expected

print("str(nested_select): wrong results due to 'FROM person, thing ...'")

nested_where = db(db.thing).select(

    db.thing.name,

    str(

        db(db.person.id == db.thing.owner_id)

        .nested_select(db.person.name)

        .with_alias("owner_name")

    ),

)

print(db._lastsql[0])

print(nested_where)

# generally, JOIN doesn't work for this kind of subselect

print("str(nested_join): wrong results due to join")

nested_join = db(db.thing).select(

    db.thing.name,

    str(

        db(db.thing.owner_id == db.person.id)

        .nested_select(

            db.person.name, left=db.person.on(db.thing.owner_id == db.person.id)

        )

        .with_alias("owner_name")

    ),

)

print(db._lastsql[0])

print(nested_join)

# query syntax wrong and has "thing" in FROM

print("._select(): syntax error due to missing parantheses/alias")

bare_select = db(db.thing)._select(

    db.thing.name, db(db.thing.owner_id == db.person.id)._select(db.person.name)

)

print(bare_select + "\n")

# fixed syntax, but has "thing" in FROM

# produces the wrong results

print("._select() with parantheses added: wrong results")

modified_select = db(db.thing).select(

    db.thing.name,

    "("

    + db(db.thing.owner_id == db.person.id)._select(db.person.name)[:-1]

    + ") AS owner_name",

)

print(db._lastsql[0])

print(modified_select)

[laundmo]

I forgot to mention: while the queries above "work" with sqlite, as sqlite is really permissive, they would all error with PostgreSQL with: "SQL Error [21000]: ERROR: more than one row returned by a subquery used as an expression".

Sqlite on the other hand seems to just use the first row of the subselect and not throw an error.

[Massimo DiPierro]

I agree that not all possible SQL queries can be build with the DAL. But why is the direct_join not sufficient for your use case?

[laundmo]

Initially, i was trying to figure out how to have aggregate columns in Grid, where being able to pass a subquery as a Column.required_fields was the closest i was able to get to making it work.

More generally: any time you can decide at runtime which fields to include, allowing subquery-fields makes more complex cases far easier, since theres no need to modify the joins and groupby of a .select() call.

I would propose that the Select class, which is already meant for subselects, should be able to generate the correct SQL based on the context in which it is used. For subselects, all it would need to do is generate the FROM clause without the outer table. "FROM person" instead of "FROM person, thing".

[Massimo DiPierro]

I agree with that change

--
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/8e351de0-1564-4ac5-83f2-0d59efcf4d33n%40googlegroups.com.