ForeignKey reverse relations do not take into account configured schema (postgres).

[naturalethic]

Describe the bug
When using postgres with multiple schemas, while the direct relation with ForeignKey works, the reverse relation fails with an error:

asyncpg.exceptions.UndefinedTableError: invalid reference to FROM-clause entry for table "al0994_tablename"

To Reproduce
Define a relation between schemas. That is, configure in the MetaData class for one entity to be of one schema, and the related entity of antoher.

class AlphaMeta(ModelMeta):
    metadata = MetaData(schema="alpha")
    database = db

class BetaMeta(ModelMeta):
    metadata = MetaData(schema="beta")
    database = db

class Alpaca(Model):
    class Meta(AlphaMeta):
        tablename = "alpaca"

    id: str = UUID(primary_key=True)

class Bear(Model):
    class Meta(BetaMeta):
        tablename = "bear"

    id: str = UUID(primary_key=True)
    alpaca: Alpaca = ForeignKey(Alpaca)

Under this scenario, bear.alpaca succeeds, alpaca.bears.all() will fail.

Expected behavior
That the reverse relation executes a query taking the related tables schema into account.

Screenshots
N/A

Versions (please complete the following information):

• Database backend used (mysql/sqlite/postgress) postgres
• Python version 3.9.6
• ormar version 0.10.5
• pydantic version 1.8.2
• if applicable fastapi version N/A

Additional context
N/A

[naturalethic]

I've duplicated your relations test using separate schemas here:

https://github.com/naturalethic/ormar/blob/foreign-keys-across-schemas/tests/test_relations/test_foreign_keys_across_schemas.py

I get a different error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'tracks.album' could not find table 'beta.albums' with which to generate a foreign key to target column 'id'

[collerek]

The error is caused by using two separate metadata that don't know about each other. So each table is registered in a separate namespace.

To achieve what you want you would probably have to have a way of declaring schema on a model level rather than on a metadata level.

I know you can have multiple databases/schemas but usually, the use case is to query them separately (one at a time). Does the functionality you want actually work in sqlalchemy? I recall something about __table_args__ but not sure if I remember correctly.

[naturalethic]

What was intresting is that the specifically defined relation worked, only the generated reverse relation did not work. I haven't tried with just sqlalchemy. For now I have worked around not having this, but I expect I'll come back to it.

[alexreznikoff]

@collerek @naturalethic Hi!
I created a branch that allows you to specify schemas in models.
Can you check my code if the tests are enough? https://github.com/alexreznikoff/ormar/blob/models-schemas-support/tests/test_relations/test_foreign_keys_across_schemas.py