Introduce support to compare two columns in `Criteria`

[koenpunt]

I'm trying to build a criteria where the resulting SQL should look something like;

OR (start_time = end_time AND start_time >= $1 AND start_time < $2)

The code I'm currently using is;

val startCol = Criteria.where("start_time")
val endCol = Criteria.where("end_time")

startCol.isEqual(startTime)
    .or(
        startCol.`is`(endCol)
            .and(startCol.greaterThanOrEquals(startTime))
            .and(startCol.lessThan(endTime))
    )
)

But unfortunately startCol.`is`(endCol) is not working, because it tries to process the endCol as a value.

I've also tried using SQL.literalOf("end_time") and Column.create("end_time", Table.create("appointments")), but both without success.

Is there a different syntax that can be used to compare 2 columns with each other?

[hariohmprasath]

Hi @koenpunt,
Looking through the code in Criteria.java and CriteriaDefinition.java currently it's not possible (may the committers can confirm this behavior). But for now the workaround would be is to do something like below:

@Query("SELECT * FROM DUMMY_ENTITY where start_time = end_time and start_time >= :startTime and start_time < :endTime")
List<DummyEntity> findAllWithSql(@Param("startTime") startTime, @Param("endTime") endTime);

[mp911de]

I had a look into our QueryMappers. We have two of them, one is for R2DBC, the other is for JDBC. We should refactor these first to have a single abstraction, otherwise we end up with duplicate complexity. Out of that refactoring, we should have contextual criteria objects for typing details that differ between JDBC and R2DBC.

QueryMapper assumes the other side is always a value, so we need to have a proper handling to reflect that the other side can reference a column.

[schauder]

We already have a ticket for merging the QueryMapper classes: #1504