Thank you very much for such a detailed answer. I am happy that everything works as it should.
пятница, 21 сентября 2018 г., 19:02:43 UTC+3 пользователь Mike Bayer написал: > > On Fri, Sep 21, 2018 at 10:52 AM Алексанр Платонов > <al.al.p...@gmail.com <javascript:>> 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+...@googlegroups.com <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > > 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.
