Как составить JPQL-запрос для получения сущности, дочерние сущности которой могут быть null (при этом эти сущности участвуют в условии WHERE)?

Question

Имеется сущность Truck. Она связана с сущностями Dirty, Gravel и Sand отношением OneToOne. Причём две из трёх этих сущностей всегда null (как бы грузовик может в один момент иметь только один груз, остальные будут null).

Truck:

@Entity
public class Truck {
    
    @Id
    Integer id;

    @Column(name = "owner")
    String owner;

    @OneToOne(mappedBy = "truck")
    Dirty dirty;
    
    @OneToOne(mappedBy = "truck")
    Gravel gravel;

    @OneToOne(mappedBy = "truck")
    Sand sand;
}

Классы грузов (наследуются от AbstractCargo):

@MappedSuperClass
public abstract class AbstractCargo {

    @OneToOne
    @JoinColumn(name = "truck_id")
    Truck truck;

    @Column(name = "weight")
    int weight;
}

@Entity
public class Dirty {
}

@Entity
public class Gravel {
}

@Entity
public class Sand {
}

Теперь мне необходимо получить список сущностей Truck, свойство owner которых равно некоторой строке и которые связаны с сущностями Dirty, Gravel или Sand, в которых свойство weight равно некоторому числу (напоминаю, что у одной сущности Truck может быть только один груз единовременно, остальные будут null). Кстати говоря, я использую EntityGraph, поэтому использование JOIN нежелательно (Hibernate начинает ругаться).

Если говорить человеческим языком, то мне надо получить все грузовики, принадлежащие некоторому владельцу, у которых есть груз с массой равной некоторому числу.

Я долго пытался составить корректный JPQL-запрос, получилось только вот это:

SELECT t FROM Truck t, Dirty d, Gravel g, Sand s
    WHERE t.owner=:owner
    AND ((d.truck.id=t.id AND d.weight=:weight) OR
         (g.truck.id=t.id AND g.weight=:weight) OR
         (s.truck.id=t.id AND s.weight=:weight))

Но результат получается невменяемым (строки дублируются).

Как мне здесь правильно составить запрос?

Answer 1

SELECT t.somecolumn,
       COALESCE(d.somecolumn, g.somecolumn, s.somecolumn) AS somecolumn
FROM Truck t
LEFT JOIN Dirty d ON d.truck.id=t.id AND d.weight=:weight
LEFT JOIN Gravel g ON g.truck.id=t.id AND g.weight=:weight
LEFT JOIN Sand s ON s.truck.id=t.id AND s.weight=:weight
WHERE t.owner=:owner

Answer 2

JPQL поддерживает фильтрацию по ассоциации, так что вот такой запрос по идее должен работать:

from Truck t
  left join t.dirty d ON d.weight = :weight
  left join t.gravel g ON g.weight = :weight
  left join t.sand s ON s.weight = :weight
WHERE t.dirty IS NOT NULL
   OR t.gravel IS NOT NULL
   OR t.sand IS NOT NULL

Тут используется left join, разновидность outer join. Рассмотрим как работает (на примере dirty, для других так же).

Для основной записи (в нашем случае из таблицы Truck) ищется подходящая под условие запись в Dirty. Если нашлась - хорошо, в поле Truck.dirty будет записана информация об этом (в этом случае в виде прокси так как нет fetch), если не нашлась, то поле dirty будет установлено в null, но truck все равно вернется в результате запроса.

В нашем случае мы используем тот факт, что если запись в таблице, которую ми join-им не найдена, то тогда поле dirty == null и мы фильтруем те Truck-и, у которых ни одной ассоциации не удалось найти по условию на вес.

Если нужно чтобы ассоциация доставалась не лениво, то замените left join на left join fetch.

Answer 3

Можно получить корректный вывод, если использовать мой изначальный запрос с DISTINCT, хотя я не уверен в производительности такого решения.

SELECT DISTINCT t FROM Truck t, Dirty d, Gravel g, Sand s
    WHERE t.owner=:owner
    AND ((d.truck.id=t.id AND d.weight=:weight) OR
         (g.truck.id=t.id AND g.weight=:weight) OR
         (s.truck.id=t.id AND s.weight=:weight))

БЛОГ НА HUSL

Как составить JPQL-запрос для получения сущности, дочерние сущности которой могут быть null (при этом эти сущности участвуют в условии WHERE)?

Ответы (3 шт):