Getting undefined table error when running a query.

[AmazingAkai]

Getting undefined table when trying to delete from the table however inserting works fine...

Code:

class ModerationCase(Table, tablename="moderation_cases"):
    id = Serial(primary_key=True)
    guild_id = BigInt()
    moderator_id = BigInt()
    target_id = BigInt()
    action = Varchar(choices=ModerationAction)
    reason = Varchar(length=1024, null=True)


class Warn(Table, tablename="warnings"):
    target_id = BigInt()
    moderator_id = BigInt()
    guild_id = BigInt()
    reason = Varchar(length=1024, null=True)
    mod_case = ForeignKey(references=ModerationCase)

# -- snip --
mod_case = await Warn.delete().where(Warn.mod_case._.id == case_id).returning(Warn.mod_case._.target_id)
# -- snip --

Traceback:

2024-03-09 21:26:25 ERROR    core.bot Command 'remove' raised an exception: UndefinedTableError: missing FROM-clause entry for table "None"
Traceback (most recent call last):
  File "/home/akai/Projects/Sugari-Bot/.venv/lib/python3.11/site-packages/discord/app_commands/commands.py", line 827, in _do_call
    return await self._callback(self.binding, interaction, **params)  # type: ignore
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/akai/Projects/Sugari-Bot/cogs/moderation/moderation.py", line 664, in warning_remove
    mod_case = await Warn.delete().where(Warn.mod_case._.id == case_id).returning(Warn.mod_case._.target_id)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/akai/Projects/Sugari-Bot/.venv/lib/python3.11/site-packages/piccolo/query/base.py", line 202, in run
    return await self._run(node=node, in_pool=in_pool)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/akai/Projects/Sugari-Bot/.venv/lib/python3.11/site-packages/piccolo/query/base.py", line 184, in _run
    results = await engine.run_querystring(
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/akai/Projects/Sugari-Bot/.venv/lib/python3.11/site-packages/piccolo/engine/postgres.py", line 539, in run_querystring
    response = await self._run_in_pool(query, query_args)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/akai/Projects/Sugari-Bot/.venv/lib/python3.11/site-packages/piccolo/engine/postgres.py", line 500, in _run_in_pool
    response = await connection.fetch(query, *args)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/akai/Projects/Sugari-Bot/.venv/lib/python3.11/site-packages/asyncpg/connection.py", line 691, in fetch
    return await self._execute(
           ^^^^^^^^^^^^^^^^^^^^
  File "/home/akai/Projects/Sugari-Bot/.venv/lib/python3.11/site-packages/asyncpg/connection.py", line 1794, in _execute
    result, _ = await self.__execute(
                ^^^^^^^^^^^^^^^^^^^^^
  File "/home/akai/Projects/Sugari-Bot/.venv/lib/python3.11/site-packages/asyncpg/connection.py", line 1892, in __execute
    result, stmt = await self._do_execute(
                   ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/akai/Projects/Sugari-Bot/.venv/lib/python3.11/site-packages/asyncpg/connection.py", line 1925, in _do_execute
    stmt = await self._get_statement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/akai/Projects/Sugari-Bot/.venv/lib/python3.11/site-packages/asyncpg/connection.py", line 433, in _get_statement
    statement = await self._protocol.prepare(
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "asyncpg/protocol/protocol.pyx", line 166, in prepare
asyncpg.exceptions.UndefinedTableError: missing FROM-clause entry for table "None"

[dantownsend]

It looks like the problem is with the returning clause .returning(Warn.mod_case._.target_id).

Looking at the Piccolo code, the returning clause for delete queries doesn't currently support joins.

For now, you could do it in two queries:

mod_case = await Warn.select(Warn.mod_case._.target_id).where(Warn.mod_case._.id == case_id)
await Warn.delete().where(Warn.mod_case._.id == case_id)