Deleting with One-To-Many Relationship

Delete children in one to many relationship

After trial and error and research, here's where I ended up.

@OneToMany(cascade = CascadeType.ALL, fetch=FetchType.EAGER, orphanRemoval=true)
private Collection<Event>   events;

And then Event:

@ManyToOne(fetch=FetchType.EAGER)
private Schedule    schedule;

And then the delete method.

public void delete(Schedule sched) 
        throws DataAccessException{
    //grab the events
    Collection<Event> events = sched.getEvents();
    //clear the collection
    events.clear();
    //flush the buffers
    getHibernateTemplate().flush();
    //reload
    sched = retrieve(sched);
    //delete the schedule
    super.delete(sched);
}

Thanks for all the help!

Deleting the parent in a one-to-many relationship throws MySQLIntegrityConstraintViolationException

You should set cart attribute of items as null before removing the cart:

Cart cartToDel = cartRepository.getOne(1l);
cartToDel.getItems().forEach(item -> item.setCart(null));
cartRepository.delete(cartToDel);

Hibernate: Deleting in One-to-Many/Many-To-One fails

Here is my solution:

   public void deleteStudents(){
        List<Student> all = studentRepository.findAll();
       deleteStudent(all.get(2));
     }

     public void deleteStudent(Student student) {

        Set<Course> courses = student.getCourses();

         for (Course course: courses) {
             if (course.getStudents().size() == 1 ){
                 course.getGuide().getCourses().remove(course);

                 courseRepository.delete(course);
             } else {
                 course.getStudents().remove(student);
             }
         }
         studentRepository.delete(student);
    }

Deleting with one-to-many relationship

If you keep your model as it is, the solution would be to query first for the relevant objects of the relation Game:

// …
let objectToDelete = gameSystems[indexPath.row]
let gameSystemName = objectToDelete.gameSystemName
realm.write {
    let games = realm.objects(Game).filter("gameSystemName = ?", gameSystemName)
    realm.delete(games)
    realm.delete(objectToDelete)
}
// …

Model Recommendation

I'd propose instead that you add an explicit link to your model, instead of expressing the relationship through a loosely linked foreign key. But the object-mapping is very individual and may be dependent on further constraints going beyond the scope of your question and this answer. For further reference, that would look like below:

 class GameSystem : Object {
      dynamic var name = ""
      let games = List<Game>()
 }

 class Game : Object {
      dynamic var title = ""
      dynamic var genre = ""

      // Use a backlink
      // (https://realm.io/docs/swift/latest/#inverse-relationships)
      dynamic var gameSystem: GameSystem? {
           return linkingObjects(GameSystem.self, forProperty: "games").first
      }
 }

If you setup your model like this, you can delete your games very easy:

// …
let objectToDelete = gameSystems[indexPath.row]
realm.write {
    realm.delete(objectToDelete.games)
    realm.delete(objectToDelete)
}
// …

Note: In the future, Realm will bring Cascading Deletes as feature. Once that is released, you won't even need to take care of the manual deletion of associated games, but you will rather be able to declare the strong relationship in your model, so that the Games are automatically deleted.

Alternative Link Declaration

You can also declare your link vice-versa, but that would make it likely harder to use a feature like Cascading Deletes in the future. However the code to delete them for now would look the same as above.

 class GameSystem : Object {
      dynamic var name = ""

      // Use a backlink
      // (https://realm.io/docs/swift/latest/#inverse-relationships)
      dynamic var games: [Game] {
           return linkingObjects(Game.self, forProperty: "gameSystem")
      }
 }

 class Game : Object {
      dynamic var title = ""
      dynamic var genre = ""
      let gameSystem: GameSystem? = nil
 }

One to many relationship supporting reads & deletes but not inserts

See code comments on why it fails:

@RunWith(SpringJUnit4ClassRunner.class)
@DataJpaTest
@Transactional
public class OrganizationRepositoryTests {

    @Autowired
    private OrganizationRepository organizationRepository;

    @Autowired
    private DepartmentRepository departmentRepository;

    @PersistenceContext
    private Entitymanager em;

    @Test
    public void testDeleteOrganization() {
        Organization organization = 
                organizationRepository.findByName(organizationName).get(); 

        Department d1 = new Department();
        d1.setName("d1");
        d1.setOrganization(organization);

        Department d2 = new Department();
        d2.setName("d2");
        d2.setOrganization(organization);

        departmentRepository.save(d1);
        departmentRepository.save(d2);

        // this fails because there is no trip to the database as Organization 
        // (the one loaded in the first line)
        // already exists in the current entityManager - and you have not 
        // updated its list of departments.
        // uncommenting the following line will trigger a reload and prove 
        // this to be the case: however it is not a fix for the issue.

        // em.clear();

         assertEquals(2,
             organizationRepository.getOne(
               organization.getId()).getDepartments().size()); 

        //similary this will execute without error with the em.clear() 
        //statement uncommented
        //however without that Hibernate knows nothing about the cascacding 
        //delete as there are no departments
        //associated with organisation as you have not added them to the list.
        organizationRepository.deleteById(organization.getId());

        assertFalse(organizationRepository.findByName(organizationName).isPresent());
        assertEquals(0, departmentRepository.findAll().size()); 
    }
}

The correct fix is to ensure that the in-memory model is always maintained correctly by encapsulating add/remove/set operations and preventing
direct access to collections.
e.g.

public class Department(){
    public void setOrganisation(Organisation organisation){
        this.organisation = organisation;

        if(! organisation.getDepartments().contains(department)){
            organisation.addDepartment(department);
        }
    }
}

public class Organisation(){

    public List<Department> getDepartments(){
        return Collections.unmodifiableList(departments);
    }

    public void addDepartment(Department departmenmt){
        departments.add(department);

        if(department.getOrganisation() != this){
            department.setOrganisation(this);
        }
    }
}

Deleting only one entry from Many-to-Many relationship

You should simply keep the cascade delete option turned on for that relationship.

In the many-to-many relationship, none of the sides "owns" the other. Instead, the relationship is maintained by a third entity (hidden in your case) called "link" table, so for instance deleting a contact will simple delete the group links tied to that contact, not the actual group entities. The same applies when deleting a group.

EDIT: While the above applies in general, it turns out that the actual problem is caused by the two one-to-many relationships (User->Contact and User->Group) not mentioned in the question which by default has cascade delete turned on. Which leads to a classical multiple cascade paths issue - when deleting a User record, the link table GroupContact records can be deleted by either User->Contact->GroupContact or User->Group->GroupContact, hence is a multiple delete path.

So you have to turn at least one of the User->Contact, User->Group or Contact<->Group relationship cascade delete off. Unfortunately that will cause you maintenance problems because you'll not be able to simple the delete one of the entities involved. Since I guess you are not deleting users so often, I would suggest you to turn User->Group relationship cascade delete off and manually delete the related User.Groups before deleting a User. Or put a delete trigger on the User table inside the database.

Issues with add and delete rows with one to many relationship

If you model indeed is as simple as you write, I would use the Association Proxy to simplify such many-to-many relationship.

Your code will change in the following way:

• add a _genre_find_or_create function:

def _genre_find_or_create(category):
    obj = Genre.query.filter_by(category=category).first()
    return obj or Genre(category=category)

• change the Movie definition to wrap the genres

class Movie(db.Model):
    # ...

    _genres = db.relationship(
        "Genre",
        secondary="association",
        backref=db.backref("movies"),
    )
    genres = association_proxy(
        "_genres",
        "category",
        creator=_genre_find_or_create,
    )

Then you can use it as follows:

# pre-adding data to ensure the category already exists
genre = Genre(category="action")
db.session.add(genre)
db.session.commit()

# adding new data
movie = Movie(title="transformers")  # , director="mb")
movie2 = Movie(title="transformers2")  # , director="mb")
db.session.add(movie)
db.session.add(movie2)
# OLD way:
# genre = Genre(category="action")
# movie.genres.append(genre)
# movie2.genres.append(genre)
# NEW way: use just 'category'
movie.genres.append("action")
movie2.genres.append("action")

db.session.commit()

---

See also another question with this solution.

---

I still do not understand why you have issues with DELETE though. Your sample code runs correctly, producing the SQL statements below when I run it:

DELETE FROM association WHERE association.movies_id = ? AND association.genres_id = ?
DELETE FROM movies WHERE movies.id = ?

---

Update: deletion
Given updated model, it is clear that the deletion is related to the one-to-one relationship between Movie and Rating.

The simple solution would be to add cascade="delete" to the Movie.rating relationship, which will delete the Rating related instance in situations where the Movie is deleted:

rating = db.relationship(
    "Rating",
    uselist=False,
    back_populates="movie",
    cascade="delete",  # <- **NEW**
)

Read more on Cascades

---

Related Topics