Query collection without involving Owner entity fields

#1

Hi, I am trying to get the Set of entities that belongs to a Parent Entity. The problem is the Owner Entity has a Table Per Class mapping, and when I try to do a join (between Owner and Items) the HQL creates a sub union query for the Owner (the hierarchy) tables and then the join, even though I am not retrieving any field from the Owner.

Hibernate 5.2.12

This is the query created by Hibernate:

SELECT email2_.id            AS col_0_0_, 
       email2_.discriminator AS col_1_0_, 
       email2_.address       AS col_2_0_, 
       email2_.description   AS col_3_0_ 
FROM   (_SELECT id, _
_               opt_lock_version, _
_               NAME, _
_               NULL AS birth_date, _
_               NULL AS first_name, _
_               NULL AS last_name, _
_               NULL AS middle_name, _
_               NULL AS nickname, _
_               1    AS clazz_ _
_        FROM   person_legal _
_        UNION ALL _
_        SELECT id, _
_               opt_lock_version, _
_               NULL AS NAME, _
_               birth_date, _
_               first_name, _
_               last_name, _
_               middle_name, _
_               nickname, _
_               2    AS clazz__ 
        FROM   person_real) person0_ 
       INNER JOIN (zelation_person_email emails1_ 
                   INNER JOIN email email2_ 
                           ON emails1_.email_id = email2_.id) 
               ON person0_.id = emails1_.person_id 
                  AND ( person0_.id = email2_.id ) 
WHERE  person0_.id = ? 
ORDER  BY email2_.address 

This is the named query:
<query name="Email.findById">
		SELECT e.id, type(e), e.address, e.description FROM Person p JOIN p.emails e ON p.id = e.id  WHERE p.id = :personId order by e.address
	</query>

Person abstract class:
@Entity
@Inheritance(strategy=InheritanceType.TABLE_PER_CLASS)
public abstract class Person extends {
@Id()
	@GeneratedValue(strategy=GenerationType.AUTO)
	@Column(name="id")
	protected Long id;
	
	@Version()
	@Column(name="opt_lock_version")
	private Integer optimisticLockVersion;
@ManyToMany(fetch=FetchType.LAZY, targetEntity=Email.class)
	@JoinTable(name="zelation_person_email",
		joinColumns= @JoinColumn(name="person_id", referencedColumnName="id"),
	    inverseJoinColumns= @JoinColumn(name="email_id", referencedColumnName="id")        
	)
	protected Set<Email> emails;
:
:

Person_Legal class:
@Entity
@Table(name="person_legal",
		uniqueConstraints =  @UniqueConstraint(
        name = "person_legal_uc_name",
        columnNames = "name"
        )
		
)
public class LegalPerson extends Person {
	public static final int TYPE_DISCRIMINATOR = 2;
	
	@Column(name="name", length=40, nullable=false)
	protected String name;

:
:
@Entity
@Table(name="person_real",
		uniqueConstraints =  @UniqueConstraint(
        name = "person_real_uc_firstname_middlename_lastname",
        columnNames = {"first_name","middle_name","last_name"}
        ),
		indexes = {
				@Index(
						name = "person_real_idx_first_name",
						columnList = "first_name",
						unique = false
				),
				@Index(
						name = "person_real_idx_last_name",
						columnList = "last_name",
						unique = false
				)
		}
)
public class RealPerson extends Person {
	public static final int TYPE_DISCRIMINATOR = 1;
	
	@Column(name="first_name", length=15, nullable=false)
	protected String firstName;
	
	@Column(name="middle_name", length=15, nullable=true)
	protected String middleName;
	
	@Column(name="last_name", length=40, nullable=false)
	protected String lastName;
	
	@Column(name="nickname", length=30, nullable=true)
	protected String nickName;
	
	@Column(name="birth_date", nullable=true)
	protected Date birthDate; 

:
:
@Entity
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name="discriminator", discriminatorType=DiscriminatorType.INTEGER)
@Table(name="email", 
		indexes =  @Index(
				name = "email_idx_address",
				columnList = "address",
				unique = false
		)
)
public abstract class Email {
	
	@Column(name="address", length=50, nullable=false)
	protected String address;
	
	@Column(name="description", length=100, nullable=true)
	protected String description; 
:
:

I tried with different Join alternatives, but didn’t work.

#2

TABLE_PER_CLASS is not very efficient, you should use SINGLE_TABLE or JOINED instead.

The UNION query is what TABLE_PER_CLASS, and to avoid it, you could just fetch emails for concrete classes using 2 queries:

SELECT 
    e.id, type(e), e.address, e.description 
FROM LegalPerson p 
JOIN p.emails e ON p.id = e.id  
WHERE p.id = :personId 
order by e.address

SELECT 
    e.id, type(e), e.address, e.description 
FROM RealPerson p 
JOIN p.emails e ON p.id = e.id  
WHERE p.id = :personId 
order by e.address

This way, you get the LegalPerson and RealPerson without generating a UNION.

#3

Thanks for your suggestion. I will evaluate a switch to a Joined strategy.
I also found that if I do the query to a specific Class, like LegalPerson, it works fine.


SELECT email2_.id            AS col_0_0_, 
       email2_.discriminator AS col_1_0_, 
       email2_.address       AS col_2_0_, 
       email2_.description   AS col_3_0_ 
FROM   person_real realperson0_ 
       INNER JOIN zelation_person_email emails1_ 
               ON realperson0_.id = emails1_.person_id 
       INNER JOIN email email2_ 
               ON emails1_.email_id = email2_.id 
WHERE  realperson0_.id = ? 
ORDER  BY email2_.address

This solves this particular query problem, it won’t work if I need to get all the emails that belong to a list of Person, like person.id in (id1,id2,…)
It will be good for Hibernate to optimize when the Parent object is not needed, but I do understand that it is also trying to reproduce the logic of the Object oriented paradigm, where you get the reference to the object first, and then the collection.