Join ambiguity in SQLAlchemy

[Jollyfant]

Hi, I was reinstalling the Federator with Docker and it is now raising errors with the newest update of SQLAlchemy (1.3.0b1 @ 2018-11-17). It seems to be because of this change:

https://docs.sqlalchemy.org/en/latest/changelog/changelog_13.html#change-1.3.0b1 (look for #4365)

Here is the full error:

<EIDA> 2018-11-21T12:29:43+0000 CRITICAL flask.app.stationlite.stationlite_resource 41 fdsnws.py:190 - Traceback information: ['Traceback (most recent call last):\n', '  
File "/var/www/stationlite/venv3/lib/python3.5/site-packages/stationlite-0.9.3rc4-py3.5.egg/eidangservices/utils/fdsnws.py", line 180, in decorator\n    return func(self, *args, **kwargs)\n', '  
File "/var/www/stationlite/venv3/lib/python3.5/site-packages/stationlite-0.9.3rc4-py3.5.egg/eidangservices/stationlite/server/routes/stationlite.py", line 81, in get\n    response = self._process_request(args, stream_epochs)\n', '  
File "/var/www/stationlite/venv3/lib/python3.5/site-packages/stationlite-0.9.3rc4-py3.5.egg/eidangservices/stationlite/server/routes/stationlite.py", line 144, in _process_request\n    maxlon=args[\'maxlongitude\'])\n', '  
File "/var/www/stationlite/venv3/lib/python3.5/site-packages/stationlite-0.9.3rc4-py3.5.egg/eidangservices/stationlite/engine/dbquery.py", line 154, in find_streamepochs_and_routes\n    join(orm.Routing).\\\n', '  
File "/var/www/stationlite/venv3/lib/python3.5/site-packages/SQLAlchemy-1.3.0b1-py3.5-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2135, in join\n    from_joinpoint=from_joinpoint)\n', '  
File "<string>", line 2, in _join\n', '  
File "/var/www/stationlite/venv3/lib/python3.5/site-packages/SQLAlchemy-1.3.0b1-py3.5-linux-x86_64.egg/sqlalchemy/orm/base.py", line 207, in generate\n    fn(self, *args[1:], **kw)\n', '  
File "/var/www/stationlite/venv3/lib/python3.5/site-packages/SQLAlchemy-1.3.0b1-py3.5-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2299, in _join\n    outerjoin, full\n', '  
File "/var/www/stationlite/venv3/lib/python3.5/site-packages/SQLAlchemy-1.3.0b1-py3.5-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2319, in _join_left_to_right\n    self._join_determine_implicit_left_side(left, right, onclause)\n', '  
File "/var/www/stationlite/venv3/lib/python3.5/site-packages/SQLAlchemy-1.3.0b1-py3.5-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2439, in _join_determine_implicit_left_side\n    "Can\'t determine which FROM clause to join "\n', "sqlalchemy.exc.InvalidRequestError: Can't determine which FROM clause to join from, there are multiple FROMS which can join to this entity. Try adding an explicit ON clause to help resolve the ambiguity.\n"]

I guess we need to explicitly tell the ORM what field to join on? https://github.com/EIDA/mediatorws/blob/ebf528666e7a8be5664fff2bdb66a0c2e554c72d/eidangservices/stationlite/engine/dbquery.py#L154-L159

[Jollyfant]

Something like this.. but it's not working..

    join(orm.Routing, orm.Routing.channel_epoch_ref == orm.ChannelEpoch.oid).\
    join(orm.Endpoint, orm.Routing.endpoint_ref == orm.Endpoint.oid).\
    join(orm.Service, orm.Endpoint.service_ref == orm.Service.oid).\
    join(orm.Network, orm.ChannelEpoch.network_ref == orm.Network.oid).\
    join(orm.Station, orm.ChannelEpoch.station_ref == orm.Station.oid).\
    join(orm.StationEpoch, orm.StationEpoch.station_ref == orm.Station.oid).\

[damb]

Hi @Jollyfant, thx. The bug is reproducible for me. However, SQLAlchemy==1.3.0b1 is still indicated as a beta release.

I've got already a fix. Some checks, and we should be fine.