Tortoise-orm: Error value query with source_field and FK field

[lntuition]

Describe the bug
When use values with source_field and ForeignKey, query is not executed (It leads to wrong query :disappointed: ) Similar to #345 , but a bit different.

To Reproduce
Full snippets here

from tortoise import Tortoise, fields, run_async
from tortoise.models import Model


class Tournament(Model):
    id = fields.IntField(pk=True)
    name = fields.TextField()

    events: fields.ReverseRelation["Event"]

class Event(Model):
    id = fields.IntField(pk=True)
    name = fields.TextField()
    tournament: fields.ForeignKeyRelation[Tournament] = fields.ForeignKeyField(
        "models.Tournament", related_name="events", source_field="tournament_source"
    )

async def run():
    await Tortoise.init(db_url="sqlite://:memory:", modules={"models": ["__main__"]})
    await Tortoise.generate_schemas()

    tour1 = await Tournament.create(name="tournament1")
    event1 = await Event.create(name="event1", tournament=tour1)

    ret = await Event.all().values("tournament__name")
    print(ret)


if __name__ == "__main__":
    run_async(run())

Expected behavior

[{'tournament__name' : 'tournament1'}]

Additional context
Current result is below.

SELECT "event__tournament"."name" "tournament__name" FROM "event" LEFT OUTER JOIN "tournament" "event__tournament" ON "event__tournament"."id"="event"."tournament_id"
returning exception no such column: event.tournament_id
Traceback (most recent call last):
  File "/root/.pyenv/versions/py37/lib/python3.7/site-packages/aiosqlite/core.py", line 153, in run
    result = function()
  File "/root/.pyenv/versions/py37/lib/python3.7/site-packages/aiosqlite/core.py", line 140, in _execute_fetchall
    cursor = self._conn.execute(sql, parameters)
sqlite3.OperationalError: no such column: event.tournament_id
Traceback (most recent call last):
  File "/storage/tortoise-orm/tortoise/backends/sqlite/client.py", line 29, in translate_exceptions_
    return await func(self, query, *args)
  File "/storage/tortoise-orm/tortoise/backends/sqlite/client.py", line 134, in execute_query_dict
    return list(map(dict, await connection.execute_fetchall(query, values)))
  File "/root/.pyenv/versions/py37/lib/python3.7/site-packages/aiosqlite/core.py", line 228, in execute_fetchall
    return await self._execute(self._execute_fetchall, sql, parameters)
  File "/root/.pyenv/versions/py37/lib/python3.7/site-packages/aiosqlite/core.py", line 167, in _execute
    return await future
  File "/root/.pyenv/versions/py37/lib/python3.7/site-packages/aiosqlite/core.py", line 153, in run
    result = function()
  File "/root/.pyenv/versions/py37/lib/python3.7/site-packages/aiosqlite/core.py", line 140, in _execute_fetchall
    cursor = self._conn.execute(sql, parameters)
sqlite3.OperationalError: no such column: event.tournament_id

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "examples/ttt.py", line 46, in <module>
    run_async(run())
  File "/storage/tortoise-orm/tortoise/__init__.py", line 636, in run_async
    loop.run_until_complete(coro)
  File "/root/.pyenv/versions/3.7.6/lib/python3.7/asyncio/base_events.py", line 583, in run_until_complete
    return future.result()
  File "examples/ttt.py", line 41, in run
    ret = await Event.all().values("tournament__name")
  File "/storage/tortoise-orm/tortoise/queryset.py", line 1096, in _execute
    result = await self._db.execute_query_dict(str(self.query))
  File "/storage/tortoise-orm/tortoise/backends/sqlite/client.py", line 31, in translate_exceptions_
    raise OperationalError(exc)
tortoise.exceptions.OperationalError: no such column: event.tournament_id

[lntuition]

Also i'm looking into this problem. But i'm not sure when to fix it, so I report it first.

[grigi]

I did some investigation and found that the left outer join ON statement isn't translated.:

ON "sometable__fk"."eyedee"="sometable"."fk_id"

It should be:

ON "sometable__fk"."sometable_id"="sometable"."fk_sometable"

I have a test that repeatedly fails.

[grigi]

The fix I did is quite deep in the call stack, and was commonly called, and probably affected much more than this...

859 tests are obviously not quite enough. Ah well. :shrug:
At least it's easy to reproduce the issues in the tests, and the library code size is only ⅔ the tests size.

And then I went off topic:
Code: 6116 lines
Tests: 9453 lines
Docs: 2862(ReST) + 1658(docstrings) + 1021(examples) → 5541 lines

I think this may be the highest doc-to-code and test-to-code ratio of anything I have been working on... Yay!

[lntuition]

@grigi Oh, you are really quick. I checked TC and it looks good 👍