[sqlalchemy] Generated slow JOIN sql statement/specifying a left JOIN

[Andreas Jung]

Hi,




   # generate a mapper of the self-referential table 'hierarchy'
   HierarchyTable = Table('hierarchy', metadata,

                          autoload=True)

   AMH_View = Table('arbeitsmittelhierarchy_arbeitsmittel_view', metadata,
                     autoload=True)

arbeitsmittelhierarchy_arbeitsmittel_view.idhierarchy references
hierarchy.id

The mapper code is:

   class HierarchyNode(MappedClassBase):
        pass


   mapper(HierarchyNode, HierarchyTable, properties={
       'children' : relation(
                       HierarchyNode,


                       cascade="all",

                    ),
       'parent' : relation(
                       HierarchyNode,

primaryjoin=HierarchyTable.c.parentid==HierarchyTable.c.id,
                       remote_side=[HierarchyTable.c.id],
                       uselist=False,
                    ),
       'tools' : relation(
                       HierarchyNode,

primaryjoin=AMH_View.c.idhierarchy==HierarchyTable.c.id,
                       foreign_keys=[AMH_View.c.idhierarchy],
                       uselist=True,
                    ),
       }
   )

The 'children' and 'parent' properties work like a charm however
when accessing 'tools' the generated query is slow because
it generates something like:

select .... from hierarchy, arbeitsmittelhierarchy_arbeitsmittel_view
where arbeitsmittelhierarchy_arbeitsmittel_view  = some_id

Because both tables are big the query takes forever. Using a LEFT JOIN
would definitely be faster. Is there a way to configure the 'tools'
property in a smarter way?

Andreas

I have a self-referential table 'hierarchy' which has a 1:N relationship with a view 'arbeitsmittelhierarchy_arbeitsmittel_view'. They are defined as follows: Column('parentid', Integer, ForeignKey('hierarchy.id')), primaryjoin=and_(HierarchyTable.c.parentid==HierarchyTable.c.id, HierarchyTable.c.deleted==False), backref=backref("parent", remote_side=[HierarchyTable.c.id])

pgpj2HJErEYRa.pgp
Description: PGP signature