Добрый день! Есть необходимость создать запросы более сложные, чем “SELECT * FROM Entity e”, однако валидный jpql запрос начинает сыпаться после дополнения условями из других компонентов экрана. Приведу пример. Есть необходимость сделать выборку суммы площадей свободных помещений на диапазон дат(Например, на следующую неделю есть 5 свободных помещений общей площадью 200м2). Есть сущность Room (помещение), Есть сущность Rental(хранит данные об аренде. содержит поля room,from,to, а так же building и type - здание и вид помещения). На экране 2 таблицы, сумма площадей по зданиям и данные об аренде (вторая таблица на экране сейчас не очень интересна). Исходные запросы выглядят так:
<keyValueCollection id="bcDc">
<loader id="bcDl">
<query>
<![CDATA[
select room.building as bd,
0 as szb,
room.floor as fl,
SUM (room.size) as sz
from rc_Room room
group by room.building, room.floor]]>
</query>
</loader>
<properties>
<property name="bd" class="ru.dgtserv.realtycontrol.entity.Building"/>
<property name="szb" datatype="decimal"/>
<property name="fl" datatype="decimal"/>
<property name="sz" datatype="decimal"/>
</properties>
</keyValueCollection>
<collection id="rentalsDc"
class="ru.dgtserv.realtycontrol.entity.Rental">
<fetchPlan extends="_base">
<property name="room" fetchPlan="_base">
<property name="type" fetchPlan="_base"/>
<property name="purpose" fetchPlan="_base"/>
</property>
</fetchPlan>
<loader id="rentalsDl">
<query>
<![CDATA[select e from rc_Rental e]]>
</query>
</loader>
</collection>
Далее я создал собственный VAADIN-компонент, который модифицирует запрос перед выполнением (для чего опишу позже, с этим будет связана еще одна проблема):
SELECT room.building AS bd,
0 AS szb,
room.floor AS fl,
SUM (room.size) AS sz
FROM rc_Room room
LEFT JOIN rc_Rental r ON r.room = room
AND NOT (r.FROM > :r_fromlCZErkzH
OR r.to < :r_tohqjrDlUi)
GROUP BY room.building,
room.floor
Запрос отрабатывает. Далее я нажимаю на сортировку в таблице и запрос падает на этапе выполнения в PostgreSQL. Ошибка возникает потому что t1.BUILDING_ID не попадает в group by. Вот в такой SQL транслировался JQPL запрос до сортировки:
SELECT t0.ID AS a1,
t0.ADRESS AS a2,
t0.DELETED_BY AS a3,
t0.DELETED_DATE AS a4,
t0.FLOOR_ AS a5,
t0.NAME AS a6,
t0.BUSINESS_CENTER_ID AS a7,
?,
t1.FLOOR_ AS a8,
SUM(t1.SIZE_)
FROM RC_ROOM t1
LEFT OUTER JOIN RC_RENTAL t2 ON ((t2.ROOM_ID = t1.ID)
AND NOT (((t2.FROM_ > ?)
OR (t2.TO_ < ?)))), RC_BUILDING t0
WHERE (((t2.ROOM_ID IS NULL)
AND (t1.DELETED_DATE IS NULL))
AND ((t2.DELETED_DATE IS NULL)
AND ((t0.ID = t1.BUILDING_ID)
AND (t0.DELETED_DATE IS NULL))))
GROUP BY t0.ID,
t0.ADRESS,
t0.DELETED_BY,
t0.DELETED_DATE,
t0.FLOOR_,
t0.NAME,
t0.BUSINESS_CENTER_ID,
t1.FLOOR_
LIMIT ?
OFFSET ?
А вот в такой после:
-- до этого момента все идентично
AND (t0.DELETED_DATE IS NULL))))
GROUP BY t0.ID,
t0.ADRESS,
t0.DELETED_BY,
t0.DELETED_DATE,
t0.FLOOR_,
t0.NAME,
t0.BUSINESS_CENTER_ID,
t1.FLOOR_
ORDER BY t1.BUILDING_ID
LIMIT ?
OFFSET ?
Пример номер два. Я хочу выборку отфильтровать по какому-нибудь полю сущности Room, например type. добавляю код в дескриптор экрана:
<propertyFilter id="filterType" property="type" operation="EQUAL" dataLoader="bcDl"/>
Получаю ошибку
IllegalArgumentException: Property ‘building’ not found in sys_KeyValueEntity
Пробую зайти с другой стороны. Т.к. таблицы у меня две и загрузчика два, добавляю propertyFilter к rentalsDl (для нашей вышеупомянутой второй таблицы, она будет вспомогательной),
<propertyFilter id="filterType" property="room.type" operation="EQUAL" dataLoader="rentalsDl"/>
а в коде перекидываю его условия к bcDl:
@Subscribe
public void onInit(InitEvent event) {
Stream.of(filterBusinessCenter, filterType, filterPurpose).forEach(f->{
PropertyCondition pc = f.getQueryCondition().copy(); // Беру условие из фильтра аренды rentalsDl
pc.setProperty(pc.getProperty().substring("room.".length())); // Обрезаем room. из начала условия
f.addValueChangeListener(ev->{ // При изменнии значения фильтра перезагружаю bcDl
pc.setParameterValue(ev.getValue());
bcDl.load();
});
updateDataLoaderCondition(bcDl, pc);// Добавляю модифицированные условия из фильтра rentalsDl к bcDl
});
}
protected void updateDataLoaderCondition(DataLoader dataLoader, PropertyCondition queryCondition) {
dataLoader.setCondition(updateCondition(dataLoader.getCondition(), queryCondition));
}
protected Condition updateCondition(Condition rootCondition, PropertyCondition queryCondition){
Condition newCondition = rootCondition;
if (newCondition == null)
newCondition = LogicalCondition.and();
if (!(newCondition instanceof LogicalCondition))
newCondition = LogicalCondition.and().add(newCondition);
((LogicalCondition) newCondition).add(queryCondition);
return newCondition;
}
Выглядит довольно костыльно и хотелось бы способ попроще, но это еще не все.
У меня не получилось добавить в jpql EXISTS. С этим (на сколько я помню) беда у самого jpql, но exists можно заменить на left join где присоединяемая сущность IS NULL. Я добавил JOIN (как упоминалось выше) с условием
PropertyCondition.isSet(alias+"."+path, false);
где alias+"."+path это “r.room” и вот как выглядят условия в итоге:
Однако после применения всех фильтров запрос все равно падает, потому что ко всем без разбора выражениям добавляется room.:
SELECT room.building AS bd,
0 AS szb,
room.floor AS fl,
SUM(room.size) AS sz
FROM rc_Room room
LEFT JOIN rc_Rental r ON r.room = room
AND NOT (r.from > :r_fromRkSbHGSx
OR r.to < :r_toNpzOukIZ)
WHERE (room.r.room IS NULL
AND room.building.businessCenter = :room_building_businessCenterIikVqibh)
GROUP BY room.building,
room.floor
И последняя проблема заключается в том, что я не нашел возможности в конструкторе запросов в коде добавить отрицание NOT, пришлось захардкодить строкой:
AND NOT (r.FROM > :r_fromlCZErkzH
OR r.to < :r_tohqjrDlUi)
Прошу прощения за очень длинное описание, но теперь к, собственно, вопросу:
Где и как, согласно архитектуры платформы лучше писать такие запросы?
- Писать простые запросы и описывать логику в контроллере экрана: проблемы будут когда придется выбрать большое количество данных (более 5000 по стандартным настройкам), без агрегатных функций на уровне БД тут не обойтись.
- Писать полный JPQL в дескрипторе - не масштабируемо и столкнусь с частью тех же самых проблем.
- Самым правильным способом выглядит путь, по которому я хотел пойти изначально, создав VAADIN компонент, который подобно остальным нативным фильтрам добавляет новые PropertyCondition к запросу, но я сталкиваюсь с тем что либо функционала недостаточно, либо он есть, но я не смог найти по нему документацию. Как сделать Exists, NOT (<условиe>), JOIN и подружить мой запрос с другими элементами формы
- Ну или все совсем плохо и мне надо как-то из контроллера экрана выполнять SQL запросы напрямую, игнорируя весь высокоуровневый функционал платформы