Gino: JOIN queries do not work

[brncsk]

• GINO version: 0.5.5
• Python version: 3.6.3
• Operating System: macOS 10.13

Description

I expected something along the following lines to work (please note that I am using the Sanic extension):

await Model1.query.where(Model1.id == id).join(Model2).select().gino.first_or_404()

However it did not. Could you please point me to a working example on writing JOIN queries involving Gino models?

What I Did

Traceback:

 File "/usr/local/lib/python3.6/site-packages/sanic/app.py", line 556, in handle_request
    response = await response
  File "/data/dev/project/src/api/routes/model1.py", line 16, in model1
    s = await Model1.query.where(Model1.id == id).join(Model2).select().gino.first_or_404()
  File "/usr/local/lib/python3.6/site-packages/gino/ext/sanic.py", line 23, in first_or_404
    rv = await self.first(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/gino/api.py", line 121, in first
    conn, self._query, *multiparams, **params)
  File "/usr/local/lib/python3.6/site-packages/gino/dialect.py", line 243, in do_first
    connection, clause, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/gino/dialect.py", line 225, in _execute_clauseelement
    prepared = await context.prepare()
  File "/usr/local/lib/python3.6/site-packages/gino/dialect.py", line 118, in prepare
    return await self.connection.prepare(self.statement)
  File "/usr/local/lib/python3.6/site-packages/gino/dialect.py", line 63, in prepare
    rv = self._stmt = await self._conn.prepare(statement)
  File "/usr/local/lib/python3.6/site-packages/gino/pool.py", line 94, in wrapper
    return await getattr(conn, attr)(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/asyncpg/connection.py", line 332, in prepare
    stmt = await self._get_statement(query, timeout, named=True)
  File "/usr/local/lib/python3.6/site-packages/asyncpg/connection.py", line 286, in _get_statement
    statement = await self._protocol.prepare(stmt_name, query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 168, in prepare
asyncpg.exceptions.PostgresSyntaxError: subquery in FROM must have an alias
HINT:  For example, FROM (SELECT ...) [AS] foo.

[fantix]

Ah yes, please try something like this:

Model1.__table__.join(Model2.__table__).where(Model1.id == id).gino.first_or_404()

Here the point is, stick to SQLAlchemy core on constructing queries; Model.field is a GINO shortcut for Table.c.field, and Model.__table__ is just Table. Other than that, it is just SQLAlchemy core.

[brncsk]

@fantix Thanks for the rapid solution! Any chance for a cleaner syntax in the future? (To be frank, joins were only an afterthought for me, what I was originally looking for were relationships, but afaics, that's still in the planning phase, as per #13).

[fantix]

Good to know it working for you 😃 Yeah but a quick join sugar won't hurt relationship design if any perhaps I think. Let me create a new ticket for that.

[kinware]

Would it be possible to add a join to one of the examples? I tried the above suggested

Model1.__table__.join(Model2.__table__).where(Model1.id == id).gino.first_or_404()

but all I got was AttributeError: 'Join' object has no attribute 'where'. Not quite sure where I went wrong. Also wondering how to do a LEFT (OUTER) JOIN?

[brncsk]

@kinware Yeah, a select() seems to be missing from @fantix's example:

Model1.__table__.join(Model2.__table__).select().where(Model1.id == id).gino.first_or_404()

This is, however, a consequence of SQLAlchemy's semantics and is unrelated to Gino.

[brncsk]

@fantix I just submitted https://github.com/fantix/gino/pull/113, could you please take a look at it? I'm not sure if this aligns with your plans, but it certainly makes things easier for me ;).

[fantix]

@kinware yeah I must have missed that, apologies.
@brncsk yep that looks awesome, thanks a lot!

[fantix]

Released 0.5.6, guess we can close this issue?

[fantix]

Oh just again missed the friends outerjoin~~

[LuckyDenis]

@kinware Yeah, a select() seems to be missing from @fantix's example:

Model1.__table__.join(Model2.__table__).select().where(Model1.id == id).gino.first_or_404()

This is, however, a consequence of SQLAlchemy's semantics and is unrelated to Gino.

I Like You!!! Thaks!!!