Re: [openstack-dev] [Neutron] need help in translating sql query to sqlalchemy query
Was perusing the documentation again this morning and there is another thing I found - you can call join() with the aliased=True flag to get similar results. Check out the "Constructing Aliases Anonymously" section. http://docs.sqlalchemy.org/en/latest/orm/query.html -- Sean M. Collins __ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [Neutron] need help in translating sql query to sqlalchemy query
On 12/01/2015 09:06 PM, Sean M. Collins wrote: On Tue, Dec 01, 2015 at 10:22:41AM EST, Venkata Anil wrote: Thanks Sean. I will check that. Meanwhile I tried this and it is working port1 = orm.aliased(models_v2.Port, name="port1") port2 = orm.aliased(models_v2.Port, name="port2") router_intf_qry = context.session.query(RouterPort.router_id).join((port1, port1.id==RouterPort.port_id), (port2, port2.device_id==RouterPort.router_id)).filter(port1.network_id==int_net_id, port2.network_id==ext_net_id).distinct() for router in router_intf_qry: router_id =router.router_id That looks pretty close. My only suggestion would be to try and see if you can just alias it once, instead of twice. Basically see if it is possible to replace all the port1 references with "models_v2.Port" Thanks Sean. Sure, I will try that suggestion. __ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [Neutron] need help in translating sql query to sqlalchemy query
On Tue, Dec 01, 2015 at 10:22:41AM EST, Venkata Anil wrote: > Thanks Sean. I will check that. > > Meanwhile I tried this and it is working > > port1 = orm.aliased(models_v2.Port, name="port1") > port2 = orm.aliased(models_v2.Port, name="port2") > router_intf_qry = > context.session.query(RouterPort.router_id).join((port1, > port1.id==RouterPort.port_id), (port2, > port2.device_id==RouterPort.router_id)).filter(port1.network_id==int_net_id, > port2.network_id==ext_net_id).distinct() > >for router in router_intf_qry: > router_id =router.router_id > That looks pretty close. My only suggestion would be to try and see if you can just alias it once, instead of twice. Basically see if it is possible to replace all the port1 references with "models_v2.Port" -- Sean M. Collins __ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [Neutron] need help in translating sql query to sqlalchemy query
Thanks Sean. I will check that. Meanwhile I tried this and it is working port1 = orm.aliased(models_v2.Port, name="port1") port2 = orm.aliased(models_v2.Port, name="port2") router_intf_qry = context.session.query(RouterPort.router_id).join((port1, port1.id==RouterPort.port_id), (port2, port2.device_id==RouterPort.router_id)).filter(port1.network_id==int_net_id, port2.network_id==ext_net_id).distinct() for router in router_intf_qry: router_id =router.router_id Thanks Anil Venkata On 12/01/2015 06:35 PM, Sean M. Collins wrote: Consult the API: http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.join In fact, there is already one join happening a couple lines above your change: https://github.com/openstack/neutron/blob/stable/liberty/neutron/db/l3_db.py#L800 Most likely, you will also need to use the aliased() function - since there are some examples that are similar to what you are trying to do - check out the "Joins to a Target with an ON Clause" section in the first link. http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.aliased __ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [Neutron] need help in translating sql query to sqlalchemy query
Consult the API: http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.join In fact, there is already one join happening a couple lines above your change: https://github.com/openstack/neutron/blob/stable/liberty/neutron/db/l3_db.py#L800 Most likely, you will also need to use the aliased() function - since there are some examples that are similar to what you are trying to do - check out the "Joins to a Target with an ON Clause" section in the first link. http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.aliased -- Sean M. Collins __ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
[openstack-dev] [Neutron] need help in translating sql query to sqlalchemy query
Hi All I have the below sql query which - "lists routers connected to given internal and external networks" select DISTINCT routerports.router_id from routerports inner join ports as ports1 on (ports1.id=routerports.port_id and ports1.network_id=internal_network_id) inner join ports as ports2 on (ports2.device_id=routerports.router_id and ports2.network_id=external_network_id); In the above query I am joining port table to routerport table twice. Can someone help me in translating that to SQLAlchemy query? This is required for the change https://review.openstack.org/#/c/220135/2/neutron/db/l3_db.py (see review comments) Thanks Anil Venkata __ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
