int4range, NOT IN (VALUES), ON NOT (with LEFT JOIN) in Query

Question

我想知道如何在QueryDSL 4中制作int4range / NOT IN (VALUES) / ON NOT (with LEFT JOIN)。我写了这个SQL请求：

SELECT DISTINCT ON (numero_semaine, jour_semaine)
numero_semaine, jour_semaine, heure_debut, heure_fin, id_box
FROM (
SELECT po.* FROM (
(SELECT DISTINCT ON (numero_semaine, jour_semaine)
numero_semaine, jour_semaine, heure_debut, heure_fin, id_box
FROM accueil.semaine_type_box
INNER JOIN accueil.plage_ouverture
ON accueil.plage_ouverture.id_semaine_type = accueil.semaine_type_box.id_semaine_type
INNER JOIN accueil.semaine_type
ON accueil.semaine_type.id = accueil.semaine_type_box.id_semaine_type
INNER JOIN accueil.box
ON accueil.box.id = accueil.semaine_type_box.id_box
WHERE semaine_type_box.numero_semaine >= 48
AND semaine_type_box.numero_semaine <= 52
AND (numero_semaine, jour_semaine)
---->NOT IN (VALUES (48,1), (48,2), (48,3), (48,4), (52,6), (52,7))<----
AND semaine_type.site = 'UR130'
AND box.ouvert_cotisant = TRUE
ORDER BY numero_semaine, jour_semaine, heure_debut
)
UNION ALL
(SELECT DISTINCT ON (numero_semaine, jour_semaine)
extract(week from plage_exceptionnelle.date) as numero_semaine,
extract(isodow from plage_exceptionnelle.date) as jour_semaine,
heure_debut, heure_fin, id_box
FROM accueil.plage_exceptionnelle
INNER JOIN accueil.box ON
accueil.box.id= accueil.plage_exceptionnelle.id_box 
WHERE plage_exceptionnelle.date >= '2018-11-30'
AND plage_exceptionnelle.date <= '2018-12-28'
AND ouverte = TRUE
AND box.site = 'UR130'
AND box.ouvert_cotisant = TRUE
ORDER BY numero_semaine, jour_semaine, heure_debut
)
) po
LEFT JOIN (
SELECT
extract(week from plage_bloquee.date) as numero_semaine,
extract(isodow from plage_bloquee.date) as jour_semaine,
heure_debut, heure_fin, id_box
FROM accueil.plage_bloquee
INNER JOIN accueil.box ON accueil.box.id = accueil.plage_bloquee.id_box 
WHERE box.site = 'UR130'
AND box.ouvert_cotisant = TRUE
) pb
---->ON NOT(po.jour_semaine = pb.jour_semaine<----
---->AND int4range(po.heure_debut, po.heure_fin) && int4range(pb.heure_debut, pb.heure_fin)<----
AND po.id_box = pb.id_box
AND po.numero_semaine = pb.numero_semaine
)
WHERE pb.id_box IS NOT NULL
) end_table
ORDER BY numero_semaine,jour_semaine,heure_debut

执行此SQL请求的方法是什么？

我已经写了这个，但我找不到用左联接写一个不在、不在的方法，而且QueryDSL 4:不存在int4range

public List<PlageDisponibleWS> findDayAvailableDao(String organisme, String site, MediaEnum media) {
final LocalDate startDate = LocalDate.now();
final short startWeekOfWeekYear = (short) startDate.getWeekOfWeekyear();
final short startDay = (short) startDate.getDayOfWeek();
final LocalDate endDate = startDate.plusMonths(1);
final short endWeekOfWeekYear = (short) endDate.getWeekOfWeekyear();
final int MONDAY_START_DAY = 1;
final int TUESDAY_START_DAY = 2;
final int WEDNESDAY_START_DAY = 3;
final int THURSDAY_START_DAY = 4;
final int FRIDAY_START_DAY = 5;
final int SATURDAY_START_DAY = 6;
final int SUNDAY_START_DAY = 7;
PostgreSQLQuery<Tuple> plagesOuvertes = queryFactory
.select(qSemaineTypeBox.numeroSemaine, qSemaineTypeBox.idBox, qPlageOuverture.jourSemaine,
qPlageOuverture.heureDebut, qPlageOuverture.heureFin)
.distinctOn(qSemaineTypeBox.numeroSemaine, qPlageOuverture.jourSemaine).from(qSemaineTypeBox)
.innerJoin(qPlageOuverture).on(qPlageOuverture.idSemaineType.eq(qSemaineTypeBox.idSemaineType))
.innerJoin(qSemaineType).on(qSemaineType.id.eq(qSemaineTypeBox.idSemaineType)).innerJoin(qBox)
.on(qBox.id.eq(qSemaineTypeBox.idBox)).where(qSemaineTypeBox.numeroSemaine.goe(startWeekOfWeekYear))
.where(qSemaineTypeBox.numeroSemaine.loe(endWeekOfWeekYear))
// NOT IN (VALUES (startWeekOfWeekYear,MONDAY_START_DAY), (startWeekOfWeekYear,TUESDAY_START_DAY),
// (startWeekOfWeekYear,WEDNESDAY_START_DAY), (startWeekOfWeekYear,THURSDAY_START_DAY),
// (endWeekOfWeekYear,SATURDAY_START_DAY), (endWeekOfWeekYear,SUNDAY_START_DAY)) ??
.where(qSemaineType.site.eq(site)).where(qBox.ouvertCotisant.eq(true))
.orderBy(qSemaineTypeBox.numeroSemaine.asc(), qPlageOuverture.jourSemaine.asc(),
qPlageOuverture.heureDebut.asc());
PostgreSQLQuery<Tuple> plagesExceptionnelles = queryFactory
.select(qPlageExceptionnelle.date.week().as("numero_semaine"),
qPlageExceptionnelle.date.dayOfWeek().as("jour_semaine"), qPlageExceptionnelle.heureDebut,
qPlageExceptionnelle.heureFin, qPlageExceptionnelle.idBox)
.distinctOn(qSemaineTypeBox.numeroSemaine, qPlageOuverture.jourSemaine).from(qPlageExceptionnelle)
.innerJoin(qBox).on(qBox.id.eq(qPlageExceptionnelle.idBox))
.where(qPlageExceptionnelle.date.goe(startDate)).where(qPlageExceptionnelle.date.loe(endDate))
.where(qPlageExceptionnelle.ouverte.eq(true)).where(qBox.site.eq(site))
.where(qBox.ouvertCotisant.eq(true)).orderBy(qSemaineTypeBox.numeroSemaine.asc(),
qPlageOuverture.jourSemaine.asc(), qPlageOuverture.heureDebut.asc());
PostgreSQLQuery<Tuple> plagesBloquees = queryFactory
.select(qPlageBloquee.date.week().as("numero_semaine"),
qPlageBloquee.date.dayOfWeek().as("jour_semaine"), qPlageBloquee.heureDebut,
qPlageBloquee.heureFin, qPlageBloquee.idBox)
.from(qPlageBloquee).innerJoin(qBox).on(qBox.id.eq(qPlageBloquee.idBox)).where(qBox.site.eq(site))
.where(qBox.ouvertCotisant.eq(true));
@SuppressWarnings("unchecked")
Expression<Tuple> unionSubQuery = queryFactory.query().unionAll(plagesOuvertes, plagesExceptionnelles)
.as("po");
final PathBuilder<Object> aliasPb = new PathBuilder<>(Object.class, "pb");
PostgreSQLQuery<Tuple> leftJoinSubQuery = queryFactory.select(unionSubQuery)
.leftJoin(plagesBloquees, aliasPb)
// ON NOT
// INT4RANGE
;
final PathBuilder<Object> aliasEnd = new PathBuilder<>(Object.class, "end_table");
query = queryFactory
.select(Projections.constructor(PlageDisponibleWS.class, qSemaineTypeBox.numeroSemaine,
qSemaineTypeBox.idBox, qPlageOuverture.jourSemaine, qPlageOuverture.heureDebut,
qPlageOuverture.heureFin))
.distinctOn(qSemaineTypeBox.numeroSemaine, qPlageOuverture.jourSemaine)
.from(leftJoinSubQuery, aliasEnd).orderBy(qSemaineTypeBox.numeroSemaine.asc(),
qPlageOuverture.jourSemaine.asc(), qPlageOuverture.heureDebut.asc())
.fetch();
return query;
}

那么，你有什么办法解决我的问题吗？

提前非常感谢。

Answer 1

对于具有多列的"in values"构造，您可以尝试以下操作：

.where(list(table.column1, table.column2).
in(list(select(constant("aaa"), constant("bbb")),
select(constant("ccc"), constant("ddd")),
select(constant("eee"), constant("fff")))))

功能"列表"one_answers"选择来自这些导入：

import static com.querydsl.core.types.dsl.Expressions.*;
import static com.querydsl.sql.SQLExpressions.*;

对于"左加入+不加入"，请尝试以下操作：

.from(table1)
.leftJoin(table2)
.on(table1.id.ne(table2.id))

不幸的是，我不熟悉int4range函数，尽管你可以尝试使用自定义数据库函数的模板表达式：

Expressions.template(Integer.class, "custom_db_function({0}, {1})", 10, 20);