Is sorting supported?

[astetsa]

I use PagingAndSortingRepository.
And I try to get pageable parameter by request like this:

@GetMapping("/find")
public Page<Data> find(@PageableDefault(size = Integer.MAX_VALUE, sort = "timestamp") final Pageable pageable) {
	return dataRepository.findAll(pageable);
}

But when I try to use Page findAll(Pageable pageable) I catch UnsupportedOperationException:
"Sorting not supported for find all scan operations".

Is there any way to sort data? Thanks!

[astetsa]

I try to use solution from issue #24
without sort in pageable parameter.
But I catch other exception "Sort not supported for scan expressions".

[derjust]

Which GSIs do you have defined?
Please also see aws-amplify/aws-sdk-ios#346 for some context

[derjust]

And while I'm working on the Spring5 support ( #98 ) - which spring-data version are you using in your project? Looks like they changed some null value into a null object. Might a transitive dependency maybe update your spring-data version?

[astetsa]

I use spring boot version '1.5.3.RELEASE'.

[derjust]

Any chance that you can try version 5.0.0?

[astetsa]

Unfortunately I can not use Spring 5 :(

[derjust]

@astetsa Any chance you can provide the output of mvn dependency:tree | grep spring-data of you setup?
To me it looks like spring-data 2.0.x is active in your scenario. If that's the case, https://github.com/derjust/spring-data-dynamodb/tree/v4.6.x should do the trick :)

[astetsa]

I use gradle

buildscript {
	ext {
		springBootVersion = '1.5.8.RELEASE'
	}
	repositories {
		mavenCentral()
		mavenLocal()
	}
	dependencies {
		classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
	}
}

apply plugin: 'java'
apply plugin: 'idea'
apply plugin: 'org.springframework.boot'

group = 'com.dynamodbs3'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = 1.8

repositories {
	mavenCentral()
	mavenLocal()
}


dependencies {
	compile 'org.springframework.boot:spring-boot-starter-web'

	compile 'com.github.derjust:spring-data-dynamodb:4.5.0'
	compile 'com.amazonaws:aws-java-sdk-dynamodb:1.11.205'

	compile 'com.amazonaws:aws-java-sdk:1.11.213'

	compile 'org.projectlombok:lombok:1.16.16'
}

[astetsa]

I have userId as hash key and creationTimestamp as sorting key.
Method works well:
Page findByUserIdOrderByCreationTimestampAsc(String userId, Pageable pageable);

But there are cases when same userId trys to add a record in same time. So I need every row in table has unique id.

I try to create the table like this: id as hash key. Then I add global secondary index: userId as hash key and creationTimestamp as range key. But when I try to use findByUserIdOrderByCreationTimestampAsc I catch an exception "Sorting not supported for scan operations".

Then I try to create the table like this: userId as hash key, id as sorting key. And I try to use local secondary index instead global: userId as hash key and creationTimestamp as range key. But I catch other exception: "Sorting only possible by [id] for the criteria specified".

Where I am mistaken?

[astetsa]

I've solved the problem. It works with the global index. It was necessary to add annotations to the model:

@DynamoDBIndexHashKey(globalSecondaryIndexName = "IndexName")
@DynamoDBIndexRangeKey(globalSecondaryIndexNames = {"IndexName"})

[derjust]

Thanks for updating the issue. Looks like it's worth to put your finding into the documentation (wiki)

Thanks again

[gauravbrills]

Ya I also observed the use of @EnableScan @EnableScanCount Page<contact> findAll(Pageable pageable); fails now which used to work in older version . Is it cause now the default sort enum is coming as UNSORTED ..

this leads to the exception for the sorting check

"Sorting not supported for find all scan operations"

I guess we should put a UNSORTED check here or something likewise .

`@Override
public Page findAll(Pageable pageable) {

	if (pageable.getSort() != null) {
		throw new UnsupportedOperationException("Sorting not supported for find all scan operations");
	}`

[derjust]

Not addressing this issue per say with PR #130 but handling the findAll case reported above. This somewhat slipped through

[gauravbrills]

@derjust Ahh Thanks ,do let me know when this will be released just upgraded and saw this .

[astetsa]

I have a table with id

	@DynamoDBHashKey(attributeName = "id")
	@DynamoDBAutoGeneratedKey
	private String id;

And global secondary index with projection type 'key only'

	@DynamoDBAttribute
	@DynamoDBIndexHashKey(globalSecondaryIndexName = TENANT_ID_INDEX_NAME)
	private String tenantId;

	@DynamoDBAttribute
	@DynamoDBTyped(DynamoDBMapperFieldModel.DynamoDBAttributeType.N)
	@DynamoDBTypeConverted(converter = DynamoDbInstantConverter.class)
	@DynamoDBIndexRangeKey(globalSecondaryIndexNames = TENANT_ID_INDEX_NAME)
	private Instant creationTimestamp;

Also I have repository method
findByTenantIdOrderByCreationTimestampAsc(String tenantId, Pageable pageable);
Search and sorting work well.

Now I need have folow method
findByTenantIdAndExpirationDateGreaterThanOrderByCreationTimestampAsc(String tenantId, long expirationDate, Pageable pageable)

I tried to create second global secondary index by expirationDate and creationTimestamp. But I caught an exception: java.lang.UnsupportedOperationException: Sort not supported for scan expressions

I tried modify first global secondary index. I setted projection type as 'include' and set expirationDate as non key attribut. But I caught the same exception.

How can I implement needed behavior?

Many thanks for considering my request!

[astetsa]

I think I know what the problem is. Let's look at the interface of DynamoDBHashKeyExtractingEntityMetadata and its implementation of DynamoDBEntityMetadataSupport. The constructor fills field Map <String, String []> globalSecondaryIndexNames. It contains models fields marked with annotations @DynamoDBIndexHashKey and @DynamoDBIndexRangeKey. Then in class AbstractDynamoDBQueryCriteria there is a method protected String getGlobalSecondaryIndexName () which defines the index name. But it cannot defin index name and scaning starts.
So I need add additional index attributes (expirationDate for my case) to globalSecondaryIndexNames. For this goal may add new annotation (e.c. @DynamoDBIndexIncludeAttribute) and put marked this annotation field to globalSecondaryIndexNames.

[peavers]

Don't suppose there is an example with POJOs and table setup for sorting? Either using 4.x or 5.x? I'm struggling to get anything working due to my poor knowledge of Dynamo!

[gauravbrills]

@derjust awaiting eagerly on this fix :) can we have this in some minor release Please 🥇

[derjust]

@astetsa i tried to follow your case but this case is hitting the ceiling with DynamoDB:

findByTenantIdOrderByCreationTimestampAsc(String tenantId, Pageable pageable
works as there is a GSI for TenantId (Hash) and CreationTimestamp (Range) to allow for query and sorting.

findByTenantIdAndExpirationDateGreaterThanOrderByCreationTimestampAsc(String tenantId, long expirationDate, Pageable pageable)
On the other hand tries to work on 3 attributes which doesn't work in DynamoDB in that constellation:

• A GSI with ExpirationDate & CreationTimestamp would allow for a query (=sorting) but leaves TenantId out (as it is included it triggers a Scan which in turn doesn't support sorting).
• A GSI with TenantId and ExpirationDate would allow for a query (at least less consumed throughput) but doesn't allow for sorting

Thus I'm unsure what kind of change you are looking for.
Keeping the information regarding what is part of the projection (for the GSI) is somewhat unimportant for the retrieval in the first place - it only is important what data is available to the mapper to produce the result entities (and the DynamoDBMapper is lenient if attributes are not available in the result set). Thus you are free to use a include projection but that doesn't help your search query.

[yoga-guptha]

I have a table with id

	@DynamoDBHashKey(attributeName = "id")
	@DynamoDBAutoGeneratedKey
	private String id;

And global secondary index with projection type 'key only'

	@DynamoDBAttribute
	@DynamoDBIndexHashKey(globalSecondaryIndexName = TENANT_ID_INDEX_NAME)
	private String tenantId;

	@DynamoDBAttribute
	@DynamoDBTyped(DynamoDBMapperFieldModel.DynamoDBAttributeType.N)
	@DynamoDBTypeConverted(converter = DynamoDbInstantConverter.class)
	@DynamoDBIndexRangeKey(globalSecondaryIndexNames = TENANT_ID_INDEX_NAME)
	private Instant creationTimestamp;

Also I have repository method
findByTenantIdOrderByCreationTimestampAsc(String tenantId, Pageable pageable);
Search and sorting work well.

Now I need have folow method
findByTenantIdAndExpirationDateGreaterThanOrderByCreationTimestampAsc(String tenantId, long expirationDate, Pageable pageable)

I tried to create second global secondary index by expirationDate and creationTimestamp. But I caught an exception: java.lang.UnsupportedOperationException: Sort not supported for scan expressions

I tried modify first global secondary index. I setted projection type as 'include' and set expirationDate as non key attribut. But I caught the same exception.

How can I implement needed behavior?

Many thanks for considering my request!

Hi
May I know how did you fix this scenario, I have the same case
Can you provide your approach It would helpful to me a lot
Thanks.

[sidhant-p]

I am sorry if am missing anything, but was this problem addressed?
am building an app with boot v 2.4.4 and spring-data v 5.1.0 and code encounters the same error for sorting.

[PremRanjanDev]

Hi, @everyone,
Is this issue resolved?
I am banging my head for the last few days still got no clue. These are the exceptions I am getting in different combination of solutions Sorting not supported for scan expressions and no HASH key for GSI using DynamoDBPagingAndSortingRepository
Please please help me.!!

[PremRanjanDev]

Repository:

@Repository
@EnableScan
@EnableScanCount
interface StatementRepository : DynamoDBPagingAndSortingRepository<Statement?, String?> {
    override fun findById(id: String): Optional<Statement?>
}

Entity:

@DynamoDBTable(tableName = "statements")
class Statement {
    @get:DynamoDBAutoGeneratedKey
    @get:DynamoDBHashKey
    var id: String? = null

    @get:DynamoDBAttribute
    var statementText: String? = null

    @get:DynamoDBAttribute
    var source: String? = null

    @get:DynamoDBAttribute
    @get: DynamoDBIndexHashKey(globalSecondaryIndexName = "id")
    @get: DynamoDBIndexRangeKey(globalSecondaryIndexNames = ["id-createdAt-index"])
    var createdAt: Long? = null
}

Service:

statementRepository.findAll(PageRequest.of(page, size, Sort.by("createdAt").descending())).forEach {...

Index on the table: (not sure if created right)
DynamoDB Table Index

No clue what is missing, someone please help or suggest to me any other way to achieve it.

Thank you in advance!

[PremRanjanDev]

Getting no HASH key for GSI in the above scenario.