java - Hibernate (JPA) inheritance with One-To-Many

Question

I am building a rule engine system and I want to allow users to fetch their requested rules and rules they are subscribed to as notification recipients

I'm struggling to write a query that by given a username fetch all rules requested by the username OR he's listed in the notifications

Since I am kinda new in Hibernate + Spring JPA I have difficulty to decide if this is a case of poor design or just a lack of knowledge in building complex queries

My scheme described below.

Rule.java

@Entity
@Table(name = "rule")
public class Rule implements Serializable {
    ...
    @Column(name = "requester")
    private String requester;

    @OneToMany
    @JoinColumn(name = "rule_id", referencedColumnName = "id")
    @Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
    private Set<Action> actions = new HashSet<>();
    ...
// getter, setters
}

Action.java

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "action_type")
public class Action implements Serializable {
...
    @Id
    @GeneratedValue
    private Long id;

    @Column(name = "active")
    private Boolean active;
...
// getter, setters
}

Task

@Entity
public class Task extends Action {
...
    @Column(name = "task_name")
    private String taskName;
...
// getter, setters
}

Alert

@Entity
public class Alert extends Action implements Serializable {
...
    @ManyToMany
    @JoinTable(
        name = "alert_notification",
        joinColumns = @JoinColumn(name = "alert_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "notification_id", referencedColumnName = "id"))
    private Set<Notification> notifications = new HashSet<>();
...
// getter, setters
}

Notification

@Entity
@Table(name = "notification")
@Inheritance(strategy = InheritanceType.JOINED)
public class Notification implements Serializable {
...
    @Column(name = "username")
    private String username;
// getter, setters
...
}

EmailNotification

@Entity
@Table(name = "email_notification")
public class EmailNotification extends Notification {
...
    @Column(
        name = "email",
        nullable = false
    )
    private String email;
...
// getter, setters
}

SlackNotification

@Entity
@Table(name = "teams_notification")
public class SlackNotification extends Notification {
...
// getter, setters
...
}

I tried to run the below JPA query, but it didn't work.

@Transactional(readOnly = true)
    public List<Rule> findAllByLogin(String login) {
        TypedQuery<Rule> query = em.createQuery("select r from Rule r join fetch r.actions a join fetch a.notifications n " +
            "where type(a) = Alert and n.login = '" + login +"' or r.requester = '" + login +"'", Rule.class);
        return query.getResultList();
    }

Any help appreciated.

Answer 1

Your design seems ok. I assume the query error is because there is no relation between action and notification, so the join

join fetch a.notifications

should be made with the alert entity. Based on this answer you should use the TREAT instruction:

    TypedQuery<Rule> query = em.createQuery("select r from Rule r "
        + "join fetch TREAT (r.actions as Alert) a "
        + "join fetch a.notifications n  " 
        + "where type(a) = Alert and n.login = '" + login +"' or r.requester = '" + login +"'", Rule.class);