Problem:

I have a complex query in which, at a certain point, in case a flag is set, I eventually need to group_by() and e.g. sum() and group_concat(). My problem is that after doing that I fail to map the result over an ORM object.

Note that the query itself seems to produce the right results; it’s just the mapping that fails.

The fact that the query is “complex” may poses some limitations about how the grouping can be implemented, and I suspect causes my failure:

• I need to implement something that can work from a dynamically defined (sub)query object and produces something at which joins, filters etc. can be dynamically appended later on; so I need to interpose the grouping in between “explode step” and “join step” (see below).

Context:

I have a table which contains information about “what items are required to build a (compound) item”. e.g.

• a tablet is composed by a screen, a plastic chassis and an electronic board.
• the electronic board is composed by a PCB, resistors, etc.

So that the table represents a tree indeed, with root (the tablet), intermediate nodes (the electronic board) and leafs (screen or PCB).

(Actually the table contains recipes to build several root items, so it is not just ONE tree, but I think this is not the point here).

I need to support several options to present the results i.e. the query is assembled dynamically as follow:

• Explode step: it might be selected just what directly compose an object (i.e. the 1st level of items related to the given object), or alternatively the leaf nodes of the tree. For example a tablet can be seen as composed by the screen, chassis, and the electronic board or, alternatively, we may not be interested it knowing that there is an intermediate object called “electronic board”, so the tablet is composed by screen, chassis, PCB, resistors etc.. This scenario is implemented by performing a recursive CTE.

• Implode step: it might be grouped for components. For example if the electronic board contains two identical resistors, they can be grouped on the same row. This follows previous point, (i.e. can be done in both cases, either when exploding or not). This is where I am stuck.

• Join step: the result is then joined with several tables (items have one or more manufacturers, and for each manufacturers there are one or more suppliers and so on).

• Filter/order step: filters/order may be applied to almost any of the previous fields, including those coming from sum() on in joined tables for example.

Apart for the “implode step” everything is implemented and seems working fine.

Code / attempts:

I tried to use .label() to make the query results fully compatible wrt the original table definition (which has a working map) i.e. same column names, and then I used aliased(OrmEntity, my_subquery) or select_entity_from() but the resulting object are not filled up with the correct values; I got the right number of rows, but the content of the items is simply wrong and doesn’t seem to come from the sum() and group_concat().

I also tried to setup a new map for my query (actually a CTE) to a new ORM entity, rather than to make work the map which I already have to the original table, but I didn’t find the proper way.

Code:

The table schema is:

        bom = Table('bom_details', Base.metadata,
                    Column('id', Integer(), primary_key=True, autoincrement=True),
                    Column('bd_bh_mag_id', Integer()),
                    Column('bd_bh_revisione', String()),
                    Column('bd_mag_id', Integer()),
                    Column('bd_mag_id_revisione', String()),
                    Column('bd_topografia', String()),
                    Column('bd_flag_non_montare', Integer()),
                    Column('bd_progressivo', Integer()),
                    Column('bd_descrizione', String()),
                    Column('bd_note', String()),
                    Column('bd_qta', String()),
                    ForeignKeyConstraint(['bd_bh_mag_id', 'bd_bh_revisione', 'bd_mag_id', 'bd_mag_id_revisione'],
                                         ['anamag.mag_id', 'anamag.mag_code_revision', 'anamag.mag_id', 'anamag.mag_code_revision'])

        )

The mapping is done with this code:

        mapper_registry.map_imperatively(
            BomItem,
            bom,
            properties={
                "descrizione" : column_property(bom.c.bd_descrizione),
                "nm": column_property(bom.c.bd_flag_non_montare),
                "index": column_property(bom.c.bd_progressivo),
                "refdes": column_property(bom.c.bd_topografia),
                "note": column_property(bom.c.bd_note),
                "qty": column_property(bom.c.bd_qta),
                "parent_anamag": relationship("AnamagItem", lazy='joined',
                                       primaryjoin=(bom.c.bd_bh_mag_id == AnamagItem.iit_code) &
                                       (bom.c.bd_bh_revisione == AnamagItem.revision)),
                "anamag": relationship("AnamagItem", lazy='joined',
                                       primaryjoin=(bom.c.bd_mag_id == AnamagItem.iit_code) &
                                       (bom.c.bd_mag_id_revisione == AnamagItem.revision)),
            },
        )

And the “implode step” I’m trying to implement is currently looking like this (some field just fixed with literal right now, but they may change).

    def _query_imploded(self, q):
        q = q.subquery()

        subq = self.db.session.query(
            literal('0').label("bd_bh_mag_id"),
            literal('0').label("bd_bh_revisione"),
            q.c.bd_mag_id,
            q.c.bd_mag_id_revisione,
            func.group_concat(q.c.bd_topografia, separator=', ').label("bd_topografia"),
            func.group_concat(q.c.bd_flag_non_montare, separator=', ').label("bd_flag_non_montare"),
            func.group_concat(q.c.bd_progressivo, separator=', ').label("bd_progressivo"),
            literal('').label("bd_descrizione"),
            func.group_concat(q.c.bd_note, separator=', ').label("bd_note"),
            func.sum(q.c.bd_qta).label("bd_qta")
        ).group_by(q.c.bd_mag_id, q.c.bd_mag_id_revisione).cte("implode_cte")

        #res = self.db.session.query(aliased(self.db.BomItem, subq))
        #res = self.db.session.query(self.db.BomItem).select_entity_from(subq)

where q is either session.query(BomItem) or the recursive CTE depending by “explode step”, and res will be feed to the “join step” which does basically several .outerjoin (and related contains_eager) on q, then ordering, filtering etc..