CODEWITHSUNDEEP
pythondatabasesqlalchemymany-to-manyflask-sqlalchemy
oliv7
oliv7

Reputation: 3

sqlachemy Many to Many: remove is not working and delete is too much

I am working on a project including to link two class: User and Group.
A user can be in some groups, and each group can have some users.

So, I created a third table : group_guest, to hold user_id and group_id.
When I create user1, user2, group1 and group2, I can add to user1 the group1 and the group2.
And add to user2 the group1

Issue: then I cannot remove only the group1 from user1 And delete group1 is not fine : user2 has no more group :/

I have tried almost every combinaisons: cascade, backref, delete orphan...
If someone as an idea... Should I modify the model ?
I would be gratefull !

code: 

from app import db  
from flask_login import UserMixin  

group_guest = db.Table('group_guest',  
                       db.Column('user_id', db.Integer, db.ForeignKey('user.id')),  
                       db.Column('group_id', db.Integer, db.ForeignKey('group.id'))  
                       )


class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)

    group_guest_group_id = db.relationship(
        "Group",
        secondary=group_guest,
        back_populates="group_guest_user_id",
        cascade="all, delete",
        lazy='dynamic'
    )


class Group(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    entitled = db.Column(db.String(64))

    group_guest_user_id = db.relationship(
        "User",
        secondary=group_guest,
        back_populates="group_guest_group_id",
        lazy='dynamic'
    )

Then : 

user1.group_guest_group_id.remove(group1)
db.session.commit()
user1.guested_group().all()

It should (in my mind) return only group2 but it return the both

Upvotes: 0

Views: 661

Answers (1)

Attack68
Attack68

Reputation: 4765

It seems that you want an asymmetric relationship across your table:

  • If you delete a User then obviously all user-group secondary references to that User should also be deleted, i.e. cascaded in the secondary table.
  • But you do not want to be able to delete a Group if at least one User is present in the group, deleting an active Group raises an integrity error.

In that case you can try the following:

group_guest = db.Table('group_guest',  
                   db.Column('user_id', db.Integer, db.ForeignKey('user.id', ondelete='CASCADE'), primary_key=True),  
                   db.Column('group_id', db.Integer, db.ForeignKey('group.id', ondelete='RESTRICT'), primary_key=True)
                   )

class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)

    group_guest_group_id = db.relationship("Group",
        secondary=group_guest,
        back_populates="group_guest_user_id",
        passive_deletes='all', lazy='dynamic'
    )


class Group(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    entitled = db.Column(db.String(64))

    group_guest_user_id = db.relationship("User",
        secondary=group_guest,
        back_populates="group_guest_group_id",
        passive_deletes='all', lazy='dynamic'
    )

Passive deletes prevents SQLAlchemy from auto-executing cascading deletions and the Database low-level instructions specified by 'ondelete' of the foreignKey dictate the action directly.

Since the primary key on the secondary table cannot be NULL then you cannot delete the parent Group anyway. I just like the ondelete='Restrict' line since it explictly states the rules and can be easier to maintain/debug later on if you ever revisit it.

edited

the following works for me without issue:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import uuid

Base= declarative_base()

group_guest= Table('group_guest', Base.metadata,
    Column('user_id', Integer, ForeignKey('user.id', ondelete='CASCADE'), primary_key=True),
    Column('group_id', Integer, ForeignKey('group.id', ondelete='RESTRICT'), primary_key=True))

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    username = Column(String(64), index=True, unique=True)

    group_guest_group_id = relationship("Group",
        secondary=group_guest,
        back_populates="group_guest_user_id",
        passive_deletes='all', lazy='dynamic'
    )

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    entitled = Column(String(64))

    group_guest_user_id = relationship("User",
        secondary=group_guest,
        back_populates="group_guest_group_id",
        passive_deletes='all', lazy='dynamic'
    )


e = create_engine("sqlite://")
Base.metadata.create_all(e)

s = Session(e)

u1 = User(username='A')
u2 = User(username='B')
g1 = Group()
g2 = Group()
s.add_all([u1, u2, g1, g2])
s.commit()

u1.group_guest_group_id.append(g1)
u1.group_guest_group_id.append(g2)
g1.group_guest_user_id.append(u2)
g2.group_guest_user_id.append(u2)

s.add_all([u1, u2, g1, g2])
s.commit()

u1.group_guest_group_id.remove(g1)
s.add(u1)
s.commit()

print([group for group in u1.group_guest_group_id])
print([group for group in u2.group_guest_group_id])

Upvotes: 2

Related Questions