Hi!
I have the following set of entities:
// Customer, Channel //
@Entity
@SequenceGenerator(name = "CUSTOMER_SEQ", sequenceName = "CUSTOMER_SEQ")
public final class Customer {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "CUSTOMER_SEQ")
private int id;
}
@Entity
@SequenceGenerator(name = "CHANNEL_SEQ", sequenceName = "CHANNEL_SEQ")
public final class Channel {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "CHANNEL_SEQ")
private int id;
@ManyToOne
@JoinColumn(name = "customer_id")
private final Customer customer;
}
// Subject hierarchy //
@Entity
@Inheritance
@DiscriminatorColumn(name = "SUBJECT_TYPE", length = 1)
@SequenceGenerator(name = "SUBJECT_SEQ", sequenceName = "SUBJECT_SEQ")
public abstract class Subject implements Serializable {
@Id
@GeneratedValue(strategy = SEQUENCE, generator = "SUBJECT_SEQ")
private int id;
}
@Entity
@DiscriminatorValue("U")
public class User extends Subject {
...
}
@Entity
@DiscriminatorValue("S")
public class Server extends Subject {
...
}
// RoleAssignment hierarchy //
@Entity
@Inheritance
@DiscriminatorColumn(name = "ASSIGNMENT_TYPE", length = 2)
@SequenceGenerator(name = "ROLE_ASSIGNMENT_SEQ", sequenceName = "ROLE_ASSIGNMENT_SEQ")
public abstract class RoleAssignment {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ROLE_ASSIGNMENT_SEQ")
private int id;
@ManyToOne
@JoinColumn(name = "subject_id", referencedColumnName = "id")
private final Subject subject;
@ManyToOne
@JoinColumn(name = "role_id", referencedColumnName = "id")
private final Role role;
}
@Entity
@DiscriminatorValue("CU")
public final class CustomerRoleAssignment extends RoleAssignment {
@ManyToOne
@JoinColumn(name = "customer_id", referencedColumnName = "id")
private final Customer customer;
}
@Entity
@DiscriminatorValue("CH")
public final class ChannelRoleAssignment extends RoleAssignment {
@ManyToOne
@JoinColumn(name = "channel_id", referencedColumnName = "id")
@QueryInit("customer")
private final Channel channel;
}
... and this JPQL query:
select distinct user from User user
inner join ChannelRoleAssignment channelRoleAssignment on channelRoleAssignment.subject.id = user.id
where channelRoleAssignment.channel.customer = ?1
While it returns the desired result the generated SQL query references the subject table twice:
SELECT DISTINCT t2.ID, t2.SUBJECT_TYPE, t2.EMAIL, t2.FIRSTNAME, t2.LASTNAME, t2.TYPE, t2.customer_id
FROM SUBJECT t3, SUBJECT t2, CHANNEL t1, ROLEASSIGNMENT t0
WHERE (((t1.customer_id = ?) AND (t2.SUBJECT_TYPE = ?)) AND ((((t3.ID = t0.subject_id) AND (t3.ID = t2.ID)) AND (t0.ASSIGNMENT_TYPE = ?)) AND (t1.ID = t0.channel_id)))
bind => [1, U, CH]
The subject table is already explicitly included via "from User user" so there's no need to join it again. I would expect the following query, which returns the exact same result:
SELECT DISTINCT t2.ID, t2.SUBJECT_TYPE, t2.EMAIL, t2.FIRSTNAME, t2.LASTNAME, t2.TYPE, t2.customer_id
FROM SUBJECT t2, CHANNEL t1, ROLEASSIGNMENT t0
WHERE (((t1.customer_id = ?) AND (t2.SUBJECT_TYPE = ?)) AND (((t2.ID = t0.subject_id) AND (t0.ASSIGNMENT_TYPE = ?)) AND (t1.ID = t0.channel_id)))
Can anybody explain why this happens or how to avoid it?
Thanks,
Marian