I need to create a table with m2m field references to itself

[serg-yalosovetsky]

I need to create a table User with m2m field references to the table User itself

I have this table User. Each user may have a friends, and this is peer-to-peer bounds.
Also each user may have a followers, another users. Each user may follow some users (so each users may have a followers and fuhrers).
And follower bound is a directed bound, because i need to know who is followers of some user, and by who he is follow.

To describe this bounds between users, i created this tables:

class User(Table, tablename="zeem_auth_app_user"):
    id = Integer(primary_key=True)
    nickname = Varchar(length=36, null=True)
   
    friends = M2M(LazyTableReference("Friends", module_path=__name__))
    followers = M2M(LazyTableReference("Fuhrership", module_path=__name__))
    fuhrers = M2M(LazyTableReference("Fuhrership", module_path=__name__))


class Fuhrership(Table, tablename="zeem_auth_app_fuhrership"):
    follower_id = ForeignKey(User)
    fuhrer_id = ForeignKey(User)
    datetime_joined = Timestamptz()

class Friends(Table, tablename="zeem_auth_app_user_friends"):
    from_user_id = ForeignKey(User)
    to_user_id = ForeignKey(User)

but this is not worked, i receive

File ~\job\zeem-publish-worker\venv\Lib\site-packages\piccolo\columns\m2m.py:285, in M2MAddRelated._run(self)
    274     joining_table_row = joining_table(**self.extra_column_values)
    275     setattr(
    276         joining_table_row,
    277         self.m2m._meta.primary_foreign_key._meta.name,
   (...)
    281         ),
    282     )
    283     setattr(
    284         joining_table_row,
--> 285         self.m2m._meta.secondary_foreign_key._meta.name,
    286         getattr(
    287             row,
    288             row._meta.primary_key._meta.name,
    289         ),
    290     )
    291     joining_table_rows.append(joining_table_row)
    293 return await joining_table.insert(*joining_table_rows).run()

File ~\job\zeem-publish-worker\venv\Lib\site-packages\piccolo\columns\m2m.py:240, in M2MMeta.secondary_foreign_key(self)
    237     if fk_column._foreign_key_meta.resolved_references != self.table:
    238         return fk_column
--> 240 raise ValueError("No matching foreign key column found!")

ValueError: No matching foreign key column found!

while i am trying to add users to followers or friends fields

u1: User = await User.objects().get(User.id == 1)
u2: User = await User.objects().get(User.id == 2)
await u1.add_m2m(u2, m2m=User.followers)
await u1.add_m2m(u2, m2m=User.friends)

(it is the same error to add user to any field)

[sinisaos]

@serg-yalosovetsky Based on this old discussion, I don't think that's possible right now. Maybe you can patch your local Piccolo installation to try this out and see if this suits your needs.

# in piccolo/columns/m2m.py
def get_select_string(self, engine_type: str, with_alias=True) -> str:
    m2m_table_name_with_schema = (
        self.m2m._meta.resolved_joining_table._meta.get_formatted_tablename()  # noqa: E501
    )  # noqa: E501
    m2m_relationship_name = self.m2m._meta.name

    fk_1 = self.m2m._meta.primary_foreign_key
    fk_1_name = fk_1._meta.db_column_name
    table_1 = fk_1._foreign_key_meta.resolved_references
    table_1_name = table_1._meta.tablename
    table_1_name_with_schema = table_1._meta.get_formatted_tablename()
    table_1_pk_name = table_1._meta.primary_key._meta.db_column_name

    fk_2 = self.m2m._meta.secondary_foreign_key
    fk_2_name = fk_2._meta.db_column_name
    table_2 = fk_2._foreign_key_meta.resolved_references
    # if primary and secondary table are the same
    if table_1 == table_2:
        table_2_name = table_1._meta.tablename
        table_2_name_with_schema = table_1._meta.get_formatted_tablename()
        table_2_pk_name = table_1._meta.primary_key._meta.db_column_name
        inner_select = f"""
            {m2m_table_name_with_schema}
            JOIN {table_1_name_with_schema} "inner_{table_1_name}" ON (
                {m2m_table_name_with_schema}."{fk_1_name}" = "inner_{table_1_name}"."{table_1_pk_name}"
            )
            WHERE {m2m_table_name_with_schema}."{fk_1_name}" = "{table_1_name}"."{table_1_pk_name}"
        """  # noqa: E501
    else:
        table_2_name = table_2._meta.tablename
        table_2_name_with_schema = table_2._meta.get_formatted_tablename()
        table_2_pk_name = table_2._meta.primary_key._meta.db_column_name

        inner_select = f"""
            {m2m_table_name_with_schema}
            JOIN {table_1_name_with_schema} "inner_{table_1_name}" ON (
                {m2m_table_name_with_schema}."{fk_1_name}" = "inner_{table_1_name}"."{table_1_pk_name}"
            )
            JOIN {table_2_name_with_schema} "inner_{table_2_name}" ON (
                {m2m_table_name_with_schema}."{fk_2_name}" = "inner_{table_2_name}"."{table_2_pk_name}"
            )
            WHERE {m2m_table_name_with_schema}."{fk_1_name}" = "{table_1_name}"."{table_1_pk_name}"
        """  # noqa: E501
...
# and later
@property
def secondary_foreign_key(self) -> ForeignKey:
    """
    See ``primary_foreign_key``.
    """
    # if primary and secondary table are the same
    for fk_column in self.foreign_key_columns:
        if fk_column._foreign_key_meta.resolved_references != self.table:
            return fk_column
        if (
            fk_column._foreign_key_meta.resolved_references
            == self.primary_table
        ):
            return self.foreign_key_columns[-1]
    raise ValueError("No matching foreign key column found!")

[sinisaos]

I agree with you about get_m2m and experimented with it some more and came up with this. I passed the reverse argument to the get_m2m method which is used to somehow distinguish the direction in which we execute queries on the join table. You can try if you want. Changes are this:

# in piccolo/table.py
def get_m2m(self, m2m: M2M, reverse: t.Optional[bool] = None) -> M2MGetRelated:
    return M2MGetRelated(row=self, m2m=m2m, reverse=reverse)

# in piccolo/columns/m2m.py
@dataclass
class M2MGetRelated:
    row: Table
    m2m: M2M
    reverse: t.Optional[bool] = False

    async def run(self):
        joining_table = self.m2m._meta.resolved_joining_table

        secondary_table = self.m2m._meta.secondary_table
       
        if self.reverse: 
            try:
                ids = (
                    await joining_table.select(
                        getattr(
                            self.m2m._meta.primary_foreign_key,
                            secondary_table._meta.primary_key._meta.name,
                        )
                    )
                    .where(self.m2m._meta.secondary_foreign_key == self.row)
                    .output(as_list=True)
                )

                results = await secondary_table.objects().where(
                    secondary_table._meta.primary_key.is_in(ids)
                )
            except ValueError:
                results = []

            return results
        else:
            try:
                # TODO - replace this with a subquery in the future.
                ids = (
                    await joining_table.select(
                        getattr(
                            self.m2m._meta.secondary_foreign_key,
                            secondary_table._meta.primary_key._meta.name,
                        )
                    )
                    .where(self.m2m._meta.primary_foreign_key == self.row)
                    .output(as_list=True)
                )

                results = await secondary_table.objects().where(
                    secondary_table._meta.primary_key.is_in(ids)
                )
            except ValueError:
                results = []

            return results

Usage is:

user_1 = await User.objects().get(User.id == 1)
print(f"Fuhrers: {await user_1.get_m2m(User.fuhrers)}")
print(f"Followers: {await user_1.get_m2m(User.followers, reverse=True)}")

The results are the same as in the previous comment.

[serg-yalosovetsky]

This is very helpful!!
Thank you very much!

Is it possible to get same result with select?

[sinisaos]

I don't think this is possible without changes in the source code. I'll see if I can do something over the weekend. In the meantime, you can use M2M join table queries or get_m2m method with or without reverse argument (from previous comments).

[sinisaos]

Same thing can be done with select, you just need to add the reverse argument to M2MSelect and do the reverse query. Here are the changes that need to be made

class M2MSelect(Selectable):
  def __init__(
      self,
      *columns: Column,
      m2m: M2M,
      as_list: bool = False,
      load_json: bool = False,
      reverse: t.Optional[bool] = None,
  ):
      """
      :param columns:
          Which columns to include from the related table.
      :param as_list:
          If a single column is provided, and ``as_list`` is ``True`` a
          flattened list will be returned, rather than a list of objects.
      :param load_json:
          If ``True``, any JSON strings are loaded as Python objects.
      :param reverse:
          If ``True``, make reverse query to self reference tables.

      """
      self.as_list = as_list
      self.columns = columns
      self.m2m = m2m
      self.load_json = load_json
      self.reverse = reverse
      ...
     def get_select_string(self, engine_type: str, with_alias=True) -> str:
        m2m_table_name_with_schema = (
            self.m2m._meta.resolved_joining_table._meta.get_formatted_tablename()  # noqa: E501
        )  # noqa: E501
        m2m_relationship_name = self.m2m._meta.name
        ...
        # if primary and secondary table are the same
        if table_1 == table_2:
            table_2_name = table_1._meta.tablename
            table_2_name_with_schema = table_1._meta.get_formatted_tablename()
            table_2_pk_name = table_1._meta.primary_key._meta.db_column_name
            # check reverse argument. If True change direction in query
            if self.reverse: 
                inner_select = f"""
                    {m2m_table_name_with_schema}
                    JOIN {table_1_name_with_schema} "inner_{table_1_name}" ON (
                        {m2m_table_name_with_schema}."{fk_1_name}" = "inner_{table_1_name}"."{table_1_pk_name}"
                    )
                    WHERE {m2m_table_name_with_schema}."{fk_2_name}" = "{table_2_name}"."{table_2_pk_name}"
                """  # noqa: E501
            else:
                inner_select = f"""
                    {m2m_table_name_with_schema}
                    JOIN {table_2_name_with_schema} "inner_{table_2_name}" ON (
                        {m2m_table_name_with_schema}."{fk_2_name}" = "inner_{table_2_name}"."{table_2_pk_name}"
                    )
                    WHERE {m2m_table_name_with_schema}."{fk_1_name}" = "{table_1_name}"."{table_1_pk_name}"
                """  # noqa: E501
...
class M2M:
    def __init__(
        self,
        joining_table: t.Union[t.Type[Table], LazyTableReference],
        foreign_key_columns: t.Optional[t.List[ForeignKey]] = None,
    ):
        """
        :param joining_table:
            A ``Table`` containing two ``ForeignKey`` columns.
        :param foreign_key_columns:
            If for some reason your joining table has more than two foreign key
            columns, you can explicitly specify which two are relevant.

        """
        if foreign_key_columns and (
            len(foreign_key_columns) != 2
            or not all(isinstance(i, ForeignKey) for i in foreign_key_columns)
        ):
            raise ValueError("You must specify two ForeignKey columns.")

        self._meta = M2MMeta(
            joining_table=joining_table,
            _foreign_key_columns=foreign_key_columns,
        )

    def __call__(
        self,
        *columns: t.Union[Column, t.List[Column]],
        as_list: bool = False,
        load_json: bool = False,
        reverse: t.Optional[bool] = None,
    ) -> M2MSelect:
        """
        :param columns:
            Which columns to include from the related table. If none are
            specified, then all of the columns are returned.
        :param as_list:
            If a single column is provided, and ``as_list`` is ``True`` a
            flattened list will be returned, rather than a list of objects.
        :param load_json:
            If ``True``, any JSON strings are loaded as Python objects.
        """
        columns_ = flatten(columns)

        if not columns_:
            columns_ = self._meta.secondary_table._meta.columns

        if as_list and len(columns_) != 1:
            raise ValueError("`as_list` is only valid with a single column argument")

        return M2MSelect(
            *columns_,
            m2m=self,
            as_list=as_list,
            load_json=load_json,
            reverse=reverse,
        )

The usage is (the result is the same as the reverse usage in get_m2m) :

print(await User.select(User.fuhrers(User.id, as_list=True)).where(User.id == 1))
print(
    await User.select(User.followers(User.id, as_list=True, reverse=True)).where(
        User.id == 1
    )
)

[serg-yalosovetsky]

Thank you you very much!
Your answers are vey helpfull!)