Delete cascade N+1 problem

Description

In a production app using JPA an object graph should be deleted using a cascaded delete on the root entity. This has been implemented using Ebean since JPA produces a gazillion of single delete queries. However the object graph causes Ebean to produce way more queries than expected and needed (but still better than JPA).

So for now the expected behavior below needs to be implemented manually in that app to avoid many queries.

Expected behavior

select t0.id from city t0 where country_id=?;
select t0.id, t0.mayor_id, t0.vice_mayor_id from city t0 where t0.id = any(?);
select t0.id from calendar t0 where person_id = any(?);

delete from event where (calendar_id) = any(?);
delete from calendar where id  = any(?);
delete from city where id = any(?);
delete from person where id = any(?);
delete from country where id=? and version=?;

Actual behavior

txn[1002] select t0.id from city t0 where country_id=? ; --bind(1)
txn[1002] select t0.id, t0.mayor_id, t0.vice_mayor_id from city t0 where t0.id = any(?); --bind(Array[3]={1,2,3})
txn[1002] delete from city where id=?; -- bind(1)
txn[1002] select t0.id from calendar t0 where person_id=? ; --bind(1)
txn[1002] delete from event where (calendar_id) = any(?); -- bind(Array[2]={1,2}) rows(20)
txn[1002] delete from calendar where id  = any(?); -- bind(Array[2]={1,2}) rows(2)
txn[1002] delete from person where id=?; -- bind(1)
txn[1002] select t0.id from calendar t0 where person_id=? ; --bind(2)
txn[1002] delete from event where (calendar_id) = any(?); -- bind(Array[2]={3,4}) rows(20)
txn[1002] delete from calendar where id  = any(?); -- bind(Array[2]={3,4}) rows(2)
txn[1002] delete from person where id=?; -- bind(2)
txn[1002] delete from city where id=?; -- bind(2)
txn[1002] select t0.id from calendar t0 where person_id=? ; --bind(3)
txn[1002] delete from event where (calendar_id) = any(?); -- bind(Array[2]={5,6}) rows(20)
txn[1002] delete from calendar where id  = any(?); -- bind(Array[2]={5,6}) rows(2)
txn[1002] delete from person where id=?; -- bind(3)
txn[1002] select t0.id from calendar t0 where person_id=? ; --bind(4)
txn[1002] delete from event where (calendar_id) = any(?); -- bind(Array[2]={7,8}) rows(20)
txn[1002] delete from calendar where id  = any(?); -- bind(Array[2]={7,8}) rows(2)
txn[1002] delete from person where id=?; -- bind(4)
txn[1002] delete from city where id=?; -- bind(3)
txn[1002] select t0.id from calendar t0 where person_id=? ; --bind(5)
txn[1002] delete from event where (calendar_id) = any(?); -- bind(Array[2]={9,10}) rows(20)
txn[1002] delete from calendar where id  = any(?); -- bind(Array[2]={9,10}) rows(2)
txn[1002] delete from person where id=?; -- bind(5)
txn[1002] select t0.id from calendar t0 where person_id=? ; --bind(6)
txn[1002] delete from event where (calendar_id) = any(?); -- bind(Array[2]={11,12}) rows(20)
txn[1002] delete from calendar where id  = any(?); -- bind(Array[2]={11,12}) rows(2)
txn[1002] delete from person where id=?; -- bind(6)
txn[1002] delete from country where id=? and version=?; -- bind(1,1)

Steps to reproduce

Consider the following entities:

@MappedSuperclass
public class BaseModel extends Model {

  @Version
  private long version = 0;
}

@Entity
public class Country extends BaseModel {

  @Id
  private Long id;

  @OneToMany(cascade = CascadeType.ALL)
  private List<City> cities = new ArrayList<>();

  public void addCity(City city) {
    cities.add(city);
  }
}

@Entity
public class City extends BaseModel {

  @Id
  private Long id;

  @OneToOne(cascade = CascadeType.ALL, optional = false)
  private Person mayor;

  @OneToOne(cascade = CascadeType.ALL, optional = false)
  private Person viceMayor;

  public City(Person mayor, Person viceMayor) {
    this.mayor = mayor;
    this.viceMayor = viceMayor;
  }
}

@Entity
public class Person extends BaseModel {

  @Id
  private Long id;

  @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
  private List<Calendar> calendars = new ArrayList<>();

  public void addCalendar(Calendar calendar) {
    calendars.add(calendar);
  }
}

@Entity
public class Calendar extends BaseModel {

  @Id
  private Long id;

  @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
  private List<Event> events = new ArrayList<>();

  public void addEvent(Event event) {
    events.add(event);
  }
}

@Entity
public class Event extends BaseModel {

  @Id
  private Long id;
}

And the following simple code to produce the above log

public static void main(String[] args) throws InterruptedException {
  // init Ebean 
  // setupDb(); 

  var country = new Country();
  for (int a = 0; a < 3; a++) {
    var mayor = createPerson();
    var viceMayor = createPerson();
    country.addCity(new City(mayor, viceMayor));
  }
  country.save();

  Thread.sleep(2000);

  country.deletePermanent();
}

private static Person createPerson() {
  var person = new Person();
  for (int a = 0; a < 2; a++) {
    var calendar = new Calendar();
    for (int b = 0; b < 10; b++) {
      calendar.addEvent(new Event());
    }
    person.addCalendar(calendar);
  }
  return person;
}

The reproduction starts at country.deletePermanent() after country.save(), using the Country-to-City-to-Person-to-Calendar-to-Event cascade in the supplied main and createPerson methods. Trace Ebean's cascade-delete handling and compare emitted SQL with the expected batched statements; done means the graph is deleted without the repeated per-person selects and deletes shown in Actual behavior.