M2M and Joining tables that are in schemas

[quinnalfaro]

Hey there!
Is there support for joining of two tables in the same schema with a m2m connection?
ie)

class Band(Table, schema="music"):
    name = Varchar()
    genres = M2M(LazyTableReference("GenreToBand", module_path=__name__))


class Genre(Table, schema="music"):
    name = Varchar()
    bands = M2M(LazyTableReference("GenreToBand", module_path=__name__))


# This is our joining table:
class GenreToBand(Table, schema="music"):
    band = ForeignKey(Band)
    genre = ForeignKey(Genre)

[dantownsend]

That's a good point.

We recently added support for Postgres schemas, and it looks like M2M might be something we overlooked.

It just seems to be using the table name, without the schema name.

piccolo/piccolo/columns/m2m.py

Line 66 in cd5595c

table_1_name = table_1._meta.tablename

It's quite a simple fix though - will turn this into an issue.

[sinisaos]

@dantownsend I just tried that and just need to add the schema name in m2m inner_select like this and everything works, but that need to be checked.

m2m_table_schema = (
    self.m2m._meta.resolved_joining_table._meta.schema or "public"
)

inner_select = f"""
    "{m2m_table_schema}"."{m2m_table_name}"
    JOIN "{m2m_table_schema}"."{table_1_name}" "inner_{table_1_name}" ON (
        "{m2m_table_schema}"."{m2m_table_name}"."{fk_1_name}" = "inner_{table_1_name}"."{table_1_pk_name}"
    )
    JOIN "{m2m_table_schema}"."{table_2_name}" "inner_{table_2_name}" ON (
        "{m2m_table_schema}"."{m2m_table_name}"."{fk_2_name}" = "inner_{table_2_name}"."{table_2_pk_name}"
    )
    WHERE "{m2m_table_schema}"."{m2m_table_name}"."{fk_1_name}" = "{m2m_table_schema}"."{table_1_name}"."{table_1_pk_name}"
"""  # noqa: E501

[sinisaos]

Yes. If no schema is specified, the schema will be None. So I added this line

m2m_table_schema = (
    self.m2m._meta.resolved_joining_table._meta.schema or "public"
)

to make sure that "public" will be used if the schema name is None.

[dantownsend]

Another gotcha is SQLite, which doesn't support schemas, so defaulting to public might not work for SQLite. Best to omit the schema if it's not defined if possible.

[sinisaos]

You're right, I hadn't considered SQLite.
We can use something like this with two types of inner_select.That works, but it's just an idea.

m2m_table_schema = (
    self.m2m._meta.resolved_joining_table._meta.schema or "public"
)

if engine_type in ("postgres", "cockroach"):
    inner_select = f"""
        "{m2m_table_schema}"."{m2m_table_name}"
        JOIN "{m2m_table_schema}"."{table_1_name}" "inner_{table_1_name}" ON (
            "{m2m_table_schema}"."{m2m_table_name}"."{fk_1_name}" = "inner_{table_1_name}"."{table_1_pk_name}"
        )
        JOIN "{m2m_table_schema}"."{table_2_name}" "inner_{table_2_name}" ON (
            "{m2m_table_schema}"."{m2m_table_name}"."{fk_2_name}" = "inner_{table_2_name}"."{table_2_pk_name}"
        )
        WHERE "{m2m_table_schema}"."{m2m_table_name}"."{fk_1_name}" = "{m2m_table_schema}"."{table_1_name}"."{table_1_pk_name}"
    """  # noqa: E501
else:
    inner_select = f"""
        "{m2m_table_name}"
        JOIN "{table_1_name}" "inner_{table_1_name}" ON (
            "{m2m_table_name}"."{fk_1_name}" = "inner_{table_1_name}"."{table_1_pk_name}"
        )
        JOIN "{table_2_name}" "inner_{table_2_name}" ON (
            "{m2m_table_name}"."{fk_2_name}" = "inner_{table_2_name}"."{table_2_pk_name}"
        )
        WHERE "{m2m_table_name}"."{fk_1_name}" = "{table_1_name}"."{table_1_pk_name}"
    """  # noqa: E501

[dantownsend]

This should be fixed now in piccolo==0.115.0.

[quinnalfaro]

thank you!