Adding lazy="subquery" in relationship with link model causes deletes not to cascade to the link table

[meehande]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from sqlmodel import Field, SQLModel, Relationship
from datetime import datetime
from typing import Optional, List


class GroupMembers(SQLModel, table=True):
    group_id: Optional[int] = Field(default=None, foreign_key="child.id", primary_key=True)
    member_id: Optional[int] = Field(default=None, foreign_key="member.id", primary_key=True)
    created_at: datetime = Field(default_factory=datetime.now)
    updated_at: datetime = Field(default_factory=datetime.now)


class Group(SQLModel, table=True):
    
    id: Optional[int] = Field(default=None, primary_key=True)

    name: str = Field()

    created_at: datetime = Field(default_factory=datetime.now)
    updated_at: datetime = Field(default_factory=datetime.now)
   
    members: List["Member"] = Relationship(
        back_populates="groups",
        link_model=GroupMembers, 
        sa_relationship_kwargs=dict(lazy="subquery"),
    )


class Member(SQLModel, table=True):

    id: Optional[int] = Field(default=None, primary_key=True)
    
    name: str = Field()
    
    created_at: datetime = Field(default_factory=datetime.now)
    updated_at: datetime = Field(default_factory=datetime.now)
   
    groups: List["Group"] = Relationship(
        back_populates="groups",
        link_model=GroupMembers,
    )


class MemberRead(SQLModel):
    id: int
    name: str
    

class GroupRead(SQLModel):
    id: int
    name: str
    members: Optional[List[MemberRead]]

Description

I have a working FastAPI application where I had create / delete endpoints for members and groups above.

I was adding read endpoints, and I want the GET /group/{id} endpoint to return a GroupRead object from the above that shows the details of the members by using SQLModel relationships.

I achieved this by adding sa_relationship_kwargs=dict(lazy="subquery") to the relationship in the Group model. Before I added that, I was getting an error:

sqlalchemy.orm.exc.DetachedInstanceError: Parent instance <Group at 0x406ab3b880> is not bound to a Session; lazy load operation of attribute 'members' cannot proceed

However, after adding this, the delete functionality does not work - and error about foreign key violation occurs around the GroupMembers table.

Adding cascade: delete on the relationship would result in rows being deleted from members table on deletion of a group, which is not what I want. I have also tried adding on-delete: cascade to the `GroupMembers table on the foreign key relationship, which also did not work.

What I want is

• to be able to select a row from GroupMembers and have details of the members be returned
• to ensure rows are deleted from the association table GroupMembers when the group is deleted, but no deletions from the Member table

Has anyone come across this issue before where lazy loading appears to affect cascading deletes ?

Operating System

Linux, macOS

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.9.11

Additional Context

No response

[meehande]

It turns out this was all caused by a delete of an object elsewhere in the code that referenced an instance of Group retrieved from a different session, which was revealed by adding this code, but caused by a different endpoint deleting incorrectly. Closing issue as resolved!