Bundle labels are wrong for new style select only (query works ?!) about sqlalchemy

[zzzeek]

this is sort of not the place to keep discussing this. if you can take this program and alter it to show an example of this "one row when there should be three" thing, start a new discussion

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the main branch:

disable col deduping inside of Bundle https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/5276

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the rel_2_0 branch:

disable col deduping inside of Bundle https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/5277

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the main branch:

revise approach for bundle deduping https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/5280

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the rel_2_0 branch:

revise approach for bundle deduping https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/5301

[Neah-Ko]

Hello @zzzeek ,

I'm bringing more activity to this matter as I may have found another bug.
I've updated my sqlalchemy version to latest today and worked on the feature described in the discussion.

The feature worked nicely with my initial version of the function, only handling x-to-one (nested entities) relationships.
I went on to tackle x-to-many (nested lists) relationships
My statement building currently looks like this:

       rels = table.relationships()
        nested, fields = partition(fields, lambda x: x in rels)
        stmt = select(
            DictBundle(
                table,  
                *[getattr(table, f) for f in fields],
                *[
                    DictBundle(
                        nes,
                        *[
                            getattr(get_declarative_class(rels[nes].target), f) 
                            for f in [
                                x.name for x in rels[nes].target.columns
                            ]
                        ]
                    )
                    for nes in nested
                ],
            )
        )
        joins = []
        for rel, nes in zip(rels, nested):
            # Handle x-to-many (nested lists) relationships
            if rel.secondary is not None:
                stmt = stmt.join_from(self.table, rel.secondary)
            stmt = stmt.join_from(self.table, rel.target)
     stmt = stmt.where(...)

Example

On a similar example as in the discussion, where myresource has now tags attached:

class Tag(Base):
    name = Column(String, primary_key=True)

class MyResource(Base):
  id = ...
  ...
  tags:    Mapped[Set["Tag"]]  = relationship(secondary=asso_res_tag, uselist=True, lazy="joined")

asso_res_tag = Table(
    "ASSO_RES_TAG",
    Base.metadata,
    Column("id_myresource",   ForeignKey("MYRESOURCE.id"),       primary_key=True),
    Column("name_tag",          ForeignKey("TAG.name"),                    primary_key=True),
)

and trying out with some test case:

curl -d '{ "name": "test", "contact": {"username": "test"},  "tags": [{"name":"bip"}, {"name":"bap"}, {"name":"bop"}],}' http://127.0.0.1:8000/myresources/

and querying my object

curl  http://127.0.0.1:8000/myresources/1?name,contact,tags

query result before serialization:

{'name': 'test', 'contact': {'username': 'test', 'email': None, 'firstName': None, 'lastName': None}, 'tags': {'name': 'bap'}}

and after serialization I completely loose the tags fields because marshmallow is expecting a list for it.
So my best guess is that somewhere in the pipeline, uselist isn't consumed.
I've tried adding an id to the tags table and changing the relationship/associative table accordingly hoping to help statement inferences and it didn't.

Let me know if you have a clue.

Best regards,

[zzzeek]

im not sure what you're doing there. uselist only has to do with loading ORM objects, and a Bundle does not load ORM objects. the join_from calls are fine but uselist has no interaction with those.

[Neah-Ko]

Good evening,
I'm sorry about bringing uselist into this it was a bad wording inspiration on my part. I have little idea on how most of the core works, and frankly the source code is quite impressive.

For my issue,
I meant that it is only fetching a single result when querying a table object relationship column that is supposed to be a list.
It is returning only the top indexed as a single item which is then shaved at serialization step.

The snippets I've provided were describing my attempt at querying a table object with the possibility to restrict on a list of fields (or defaults to a regular select(table))

• Some of those fields may be relationship fields
  -> join those tables as well

The goal is to implement a an abstract REST GET function i.e.: /resource?field
If you want to run the code you may find it here: https://github.com/bag-cnag/biodm/tree/dev
It is still work in progress, but neatly packaged. There's a dedicated install and setup section in the Readme.

The source that I've provided are somewhat simplified examples of me tinkering with this function

I'm testing with the following command to create a dataset (replace generic myresource in my example):

curl -d '{"id": 1, "version": 1, "name": "ds_test", "contact": {"username": "test"}, "owner_group": {"name": "g"}, "tags": [{"name":"bip"}, {"name":"bap"}, {"name":"bop"}], }' http://127.0.0.1:8000/datasets/

and query it that way:

curl http://127.0.0.1:8000/datasets/1_1?fields=name,contact,tags

which yields the following sql code when I print the statement before executing it.

SELECT "DATASET".name, "USER".id, "USER".username, "USER".email, "USER"."firstName", "USER"."lastName", "TAG".id AS id_1, "TAG".name AS name_1 
FROM "DATASET" JOIN "USER" ON "USER".username = "DATASET".username_user_contact JOIN "ASSO_DATASET_TAG" ON "DATASET".id = "ASSO_DATASET_TAG".id_dataset AND "DATASET".version = "ASSO_DATASET_TAG".version_dataset JOIN "TAG" ON "TAG".id = "ASSO_DATASET_TAG".id_tag 
WHERE "DATASET".id = :id_2 AND "DATASET".version = :version_1

which when I run directly in my postgres DB plugging in my values for id and version (1, 1) gets me:

   name   | id | username | email | firstName | lastName | id_1 | name_1 
----------+----+----------+-------+-----------+----------+------+--------
 ds_test |    | test     |       |           |          |    1 | bip
 ds_test |    | test     |       |           |          |    2 | bap
 ds_test |    | test     |       |           |          |    3 | bop

So in principle the core gets back all my tags but doesn't make a list out of it when building my Dataset item
c.f. result before serialization in my comment above.

Best,

[zzzeek]

I dont see any code here that I can run so I still dont really know what it is you're doing. is this still involving Bundle? Bundle only returns columns. you will get every row back individually. sub-bundles aren't going to be merged into lists or anything like that.