I'm still pretty new to sqlalchemy so any guidance is appreciated. I have the following class that does a 1 to 1 relationship with itself and works just fine:

class Unit(Base):
  __tablename__ = 'unit'
  uic: Mapped[str] = mapped_column(String(10), primary_key=True)

  parent = relationship("Unit", remote_side=[uic], uselist=False)
  parentuic = Column(String, ForeignKey('unit.uic'), nullable=True)

So when I query the unit, I'll get "unit.parentuic" as an expected string value. And I'll get "unit.parent" as a unit object. Looks like it's working as I expected it. I'm able to create a bunch of units that has the same parentuic and when I query the parent, I get a list of all the child unit objects so I'm good to go.

But I also want to add a 1 to Many relationship to the same uic. Basically, I'm keeping track of a specific group of parents so I added this:

  # Keep track of a specific group of parent objects
  parent_group: Mapped[list["Unit"]] = 
       relationship(back_populates="parent_group_uic")
  parent_group_uic: Mapped["Unit"] = relationship(
       "Unit", remote_side=[uic],
       back_populates="parent_group"
  )

This gives me all kinds of weird behaviors so I'm doing this all wrong I think. When I try to add a parent group, I get "maximum recursion depth exceeded". Or if I start just looking at only a parent, it brings along parent_groups with it even though I didn't create a parent group yet. So my relationship logic is all wrong I'm guessing.

Any ideas?

Thanks for any help you can provide. Jon

SQLAlchemy            2.0.36
fastapi               0.112.0
asyncpg               0.30.0

Edit to add Additional information requested by “python_user”

Let me give a better description of what I’m trying to achieve. Basically, I’m creating a hierarchical structure of a unit org structure. This structure should never change. So it would look like:

parent_unit 
  |
  |__ __ child_unit1 __ __ child_unit2
                    |
                    |__ __ sub_child_unit3

So a child has a 1 to 1 relationship to its parent. But when I query the parent, it can return many childs. So maybe I explained that wrong? The key here is that once the org structure is created, it technically will stay the same.

In addition to the above relationship, I want to also create groups with these units that CAN be changed. For example, I want to create a group comprised of child_unit2, child_unit1, and sub_child_unit3 only. And arrange them in an order like:

child_unit2
  |
  |__child_unit1 __ __ sub_child_unit3

But I can also have another group where child_unit1 is used like:

new_unit
  |
  |__ child_unit1

So that means with regards to the groups, a unit can have a 1 to many relationship with many parents (ie child_unit1 has a parent of new_unit and a parent of child_unit2). Does that make sense? So I want to use the Unit table to do all this since all the units are kept there and the org structure is created from it based on a parent column. But I also want to mix and match them in to custom groups. And I just noticed that as I have the code now, if I create unit AA as the parent, BB as the child and CC as the child, and then query the parent unit, it gives me this output back:

“parent_group”: [
  { 
      “uic”: “BB”,
  },
  {
      “uic”: “CC”,
  }
]

And I’m not sure why “parent_group” has the child data instead of “parent”. I didn’t add a single thing to parent_group yet.

So when it comes to the org structure, the child can only have a single parent unit. But when it comes to groups, the child can have many different parent units.

解决方案

This did the trick for me...names changed to protect the innocent. I basicaly had to create a many-to-one relationship and a one-to-many relationship within the same table. It seems to be giving me the correct output. I'll update if I need to change anything.

  parentuic: Mapped[int] = mapped_column(ForeignKey('unit.uic'), nullable=True)
  parent_unit: Mapped['Unit'] = relationship(
      'Unit', 
      remote_side='Unit.uic', 
      foreign_keys=[parentuic], 
      back_populates="child_units",
  )

  child_units: Mapped[List['Unit']] = relationship(
      'Unit', 
      back_populates="parent_unit", 
      foreign_keys=[parentuic]
  )

  second_parent_uic: Mapped[int] = mapped_column(ForeignKey('unit.uic'), nullable=True)

  second_parents: Mapped[list['Unit']] = relationship(
      'Unit', 
      foreign_keys=[second_parent_uic],
      back_populates="second_child_units",
      remote_side="Unit.uic",
  )

  second_child_units: Mapped[list['Unit']] = relationship(
      'Unit', 
      back_populates="second_parents", 
      foreign_keys=[second_parent_uic]
  )