JPA Criteria Query with JOIN on Polymorphic Subclass-CodePudding

I have the following (simplified) data model:

@Entity
public class Person
{
    @ManyToOne Animal likes;
}

@Entity
@Inheritance(strategy=InheritanceType.JOINED)
public class Animal{}

@Entity
public class Pet extends Animal
{
    @ManyToOne Home home;
}

@Entity
public class Domestic extends Animal
{
    @ManyToOne Farm farm;
}

@Entity public class Home {}
@Entity public class Farm {}

Now I want a group the persons by home and count on different homes:

CriteriaBuilder cB = em.getCriteriaBuilder();
CriteriaQuery<Tuple> cQ = cB.createTupleQuery();
Root<Person> person = cQ.from(Person.class);

Join<Person,Pet> jPet = person.join("likes");
Join<Pet,Home> jHome = jPet.join("home");

Here I'm getting the error Unable to locate Attribute with the the given name [home] on this ManagedType [Animal] the generated SQL query obviously explains why: INNER JOIN animal a ON person.likes_id = animal.id. The next approach was to use .treat:

Join<Person,Pet> jPet = cB.treat(debit.join("likes), Pet.class);
Join<Pet,Home> jHome = jPet.join("home");

This seemed to be promising with the new .treat feature of JPA 2.1, but the generated SQL looks like this

INNER JOIN animal ON person.likes_id = animal.id
LEFT OUTER JOIN pet ON pet.id = animal.id
LEFT OUTER JOIN domestic ON domestic.id = animal

In fact it is joining all subclasses of Animal where I'm only interested in Pet. So I tried to limit to the specific class with a Predicate cB.equal(jPet.type(),Pet.class). This generates some interesting SQL (which I have never seen before and where I wonder what is inserted in END=?)

WHERE CASE
  WHEN pet.id IS NOT NULL THEN 1
  WHEN domestic.id IS NOT NULL THEN 2
  WHEN animal IS NOT NULL THEN 0
  END = ?

But it still does not work as expected. How can I count the different home with JPA criteria queries where I start from a Person?

CodePudding user response：

You may use multiple roots (mine: I believe that is the most common scenario) in Criteria API query:

Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.

So, your query might look like:

Root<Person> person = cQ.from(Person.class);
Root<Pet> pet = cQ.from(Pet.class);
cQ.where(cB.equal(pet, person.get("likes")));

Moreover, that is not required to define X-TO-Y associations in order to write a query - we may join "unrelated" entities as well.