QueryDSL: Pagination with Order By on composite key not possible

[theobisproject]

Entities

@Entity
public class MessageBundle {
    @EmbeddedId
    private MessageBundleIdEmbeddable id;
    @Column(name = "VALUE", columnDefinition = "VARCHAR(1024)")
    private String value;
}

@Embeddable
public class MessageBundleIdEmbeddable  {
    @Column(name = "LOCALE", nullable = false, columnDefinition = "VARCHAR(2)")
    private String locale;
    @Column(name = "KEY", nullable = false, columnDefinition = "VARCHAR(256)")
    private String key;
    @Column(name = "BUNDLE", nullable = false, columnDefinition = "VARCHAR(256)")
    private String bundle;
}

Query Method

QMessageBundle messageBundle = QMessageBundle.messageBundle;

final BlazeJPAQuery<MessageBundle> query =
        new BlazeJPAQuery<>(getEm(), getCriteriaBuilderFactory()).select(messageBundle)
                .from(messageBundle);

if (StringUtils.isNotEmpty(criteria.getErrorCode())) {
    query.where(messageBundle.id().key.containsIgnoreCase(criteria.getErrorCode()));
}

query.orderBy(messageBundle.id().key.lower());

query.fetchPage(criteria.getOffset(), criteria.getLimit());

Observed behaviour

Executing the query gives me this exception from PaginatedCriteriaBuilderImpl#prepareAndCheck

The order by items of the query builder are not guaranteed to produce unique tuples! Consider also ordering by the entity identifier!

The error is correct since the keys are not unique.

Problem

None of my embeddable columns are unique. I tried to add all columns to the order by which would make the order persistent but since all columns themself are not unique the exception is still thrown.
Is there any way to use the whole embeddable in the order by? I failed to find an example for QueryDSL in the documentation and the tests in the repo.
I believe the check in prepareAndCheck can never work with and EmbeddedId.

I'm starting the discussion to clarify if I need a different approach in my search or if this is currently a limitation.

[jwgmeligmeyling]

• messageBundle.id().key.lower() is entropy reducing, therefore uniqueness cannot be determined anymore and Blaze-Persistence rejects this kind of ordering for pagination purposes in order to provide deterministic results.
• You need to order by all columns of the PK. So .orderBy(messagebundleId.id().key, messagebundleId.id().locale, messagebundleId.id().bundle).

[theobisproject]

Ah. I see now where my thoughts were not correct. I already have tried ordering by all columns but I always included the .lower() call on the key. e.g. .orderBy(messageBundle.id().key.lower().asc(), messageBundle.id().locale.asc(), messageBundle.id().bundle.asc()). Removing it fixes the error. Thanks for the pointer in the right direction