On Fri, Sep 21, 2018 at 10:52 AM Алексанр Платонов <al.al.plato...@gmail.com> wrote: > > Hi, with all due respect, I copy here the link to the question so as not to > duplicate information. > > https://stackoverflow.com/questions/52414329/sqlalchemy-query-filter-by-field-value-in-related-table
the query I see: result = Device.query.filter(DeviceGroup.type != ModelType.TYPE_BLADE_SERVER).all() does not generate any LEFT OUTER JOIN that you can filter on, it looks like you have eager loading in there. You can't filter on those: http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html?highlight=zen#the-zen-of-joined-eager-loading you would need to say: session.query(Device).outerjoin(Device.model).outerjoin(DeviceModel.group).filter(DeviceGroup.type != ModelType.TYPE_BLADE_SERVER) However, you still have LEFT OUTER JOIN for your eager loads in there so this will create a huge query, unless you add a lazyload('*') option to it, or allow them to be included with contains_eager(): session.query(Device).options(contains_eager("model").contains_eager("group")).outerjoin(Device.model).outerjoin(DeviceModel.group).filter(DeviceGroup.type != ModelType.TYPE_BLADE_SERVER).all() The LEFT OUTER JOINS are still inefficient though. When you want to look for rows where related rows *dont* exist, use EXISTS: session.query(Device).filter(~Device.model.has(DeviceModel.group.has(DeviceGroup.type == ModelType.TYPE_BLADE_SERVER))).all() this gives you, with your eager loads still turned on: SELECT device.id AS device_id, device.hostname AS device_hostname, device.model_id AS device_model_id, device_model_1.id AS device_model_1_id, device_model_1.group_id AS device_model_1_group_id, device_group_1.id AS device_group_1_id, device_group_1.name AS device_group_1_name, device_group_1.height AS device_group_1_height, device_group_1.type AS device_group_1_type FROM device LEFT OUTER JOIN device_model AS device_model_1 ON device_model_1.id = device.model_id LEFT OUTER JOIN device_group AS device_group_1 ON device_group_1.id = device_model_1.group_id WHERE NOT (EXISTS (SELECT 1 FROM device_model WHERE device_model.id = device.model_id AND (EXISTS (SELECT 1 FROM device_group WHERE device_group.id = device_model.group_id AND device_group.type = ?)))) you can turn off the eager loads with lazyload('*'): session.query(Device).options(lazyload('*')).filter(~Device.model.has(DeviceModel.group.has(DeviceGroup.type == ModelType.TYPE_BLADE_SERVER))).all() SQL: SELECT device.id AS device_id, device.hostname AS device_hostname, device.model_id AS device_model_id FROM device WHERE NOT (EXISTS (SELECT 1 FROM device_model WHERE device_model.id = device.model_id AND (EXISTS (SELECT 1 FROM device_group WHERE device_group.id = device_model.group_id AND device_group.type = ?)))) > > Thank you for your participation and response. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
