PostgreSQL function used in QueryDSL is not working, returns ERROR: syntax error at or near ".&-CodePudding

This is my very first question on stackoverflow, so sorry in advance if anything is not as precise as it should be

In my project, I use Hibernate (as ORM framework) with QueryDSL lib, PostgreSQL as a database.

Basically, I need to check the size of a list 'arr', which is a property of some 'X' class, so I googled and found a way to use postgres functions with querydsl as follows (before you ask, I can't use native queries by the requirements):

BooleanBuilder builder = new BooleanBuilder();

builder.and(Expressions.booleanTemplate("function('array_length', {0})", qX.arr)
                .castToNum(Integer.class).gt(0));

Everything compiles fine, but when the repository method is being called, I get an error:

ERROR: syntax error at or near "." Position: ...

I checked everything, but there are no "." in that position and near positions as well. However, after setting spring.jpa.show-sql=true I found out that there is indeed a "." symbol somewhere in that position, and the result SQL statement looks like this:

... and cast(array_length(.) as int4)>?

which means, that JPA can't put my 'arr' inside the array_length() function (is that so?)

Why does this happen? Am I doing something wrong? Thank you in advance

My entity class looks like that:

    @EqualsAndHashCode(callSuper = true)
    @Entity
    @Table
    @Data
    @NoArgsConstructor
    @TypeDefs({
            @TypeDef(name = "list-array", typeClass = ListArrayType.class)
    })
    public class X extends BaseClass {
    
        // private fields
    
        @Type(type = "list-array")
        @Column(name = "arr", columnDefinition = "bigint[]")
        @ElementCollection
        @OrderColumn
        private List<Long> arr;
    }

I tried without @ElementCollection and @OrderColumn annotations but that gives me cast errors

CodePudding user response：

@ElementCollection and @OrderColumn are causing a first problem here. After they are removed (and the schema is setup correctly), the function call (SQL template) needs to be corrected.

The problem with @ElementCollection and @OrderColumn is that they represent an alternative approach for storing lists/arrays as part of an entity.
@ElementCollection stores the elements in a separate table, with each element in a separate row (each referencing the entity). To "remember" the correct order, an @OrderColumn is needed as part of the separate table, since rows are returned in arbitrary order if no order is specified (https://stackoverflow.com/a/20050403).

In contrast, ListArrayType and @Column(columnDefinition = "bigint[]") will enable saving the sequence of elements in one column of an entity row. Therefore, no separate table is used, and since the elements are not saved in separate rows, no additional order information is needed.

So without @ElementCollection and @OrderColumn the list mapping is already correctly setup. Be aware that your schema might currently be in a bad state, and you need to make sure that there is a bigint[] column in the entity table (can e.g. be auto-created by hibernate when @ElementCollection and @OrderColumn are removed).

2. Fixing the PostgresQL function call: array_length needs a second argument indicating the dimension of the array along which the length is returned (https://www.postgresql.org/docs/current/functions-array.html). So specifying the template string as follows should get you the correct result:
"function('array_length', {0}, 1)"
("1" being the requested array dimension).