Error when comparing Key

[beunick]

Hi,
I have issue, I am trying to do NOT EQUAL on key and I separate it in two queries as you can see in the code below. But I keep having error about the " ID property was required but does not exist for the type".
Can you please let me know why that ?

`
@repository
public interface ProductRepository extends DatastoreRepository<Product, Key> {
@query("select * from product where id > @product_key and code = @product_code")
Product existsByCodeLow(@PARAM("product_code")String code, @PARAM("product_key")Key key);

  @Query("select * from product  where id < @product_key and code = @product_code")
   Product existsByCodeSup(@Param("product_code")String code, @Param("product_key")Key key);

}
`

Here is the error I got:

org.springframework.cloud.gcp.data.datastore.core.mapping.DatastoreDataException: An ID property was required but does not exist for the type: class com.google.cloud.datastore.Key

[dmitry-s]

@beunick in this case you should pass an entity instead of a Key. It will be converted to a key automatically.

Let me know if that works for you.

Thanks

[beunick]

@dmitry-s Thanks for the note but no it is not working.
here is my code change.

@Query("select * from product where id < @product and code = @product_code") Product existsByCodeSup(@Param("product_code")String code, @Param("product") Product product);

And I still have this error message:
An ID property was required but does not exist for the type: class com.google.cloud.datastore.Key

Any other idea ?

Thanks

[dmitry-s]

@beunick the message looks strange because you don't have any parameters of type com.google.cloud.datastore.Key.

Could you provide sample code so I could try to replicate your issue?

Thanks

[beunick]

Sorry @dmitry-s sorry I copy and paste the wrong error message, that message was what I got before code change :

When changing the code as suggested:
@Query("select * from product where id < @product and code = @product_code") Product existsByCodeSup(@Param("product_code")String code, @Param("product") Product product);

Here is the message I still have:

`
com.google.datastore.v1.client.DatastoreException: no matching index found. recommended index is:

• kind: product
  properties:
  • name: code
  • name: id
    `

[dmitry-s]

@beunick ok that makes sense.

Datastore requires you to create indexes for inequality filters unless you only use one field (see https://cloud.google.com/datastore/docs/concepts/queries#inequality_filters_are_limited_to_at_most_one_property).

It's very easy to create indexes, see https://cloud.google.com/datastore/docs/concepts/indexes for reference.

Let me know if that works for you.

Thanks

[beunick]

Thanks @dmitry-s I did add the indexes, I now have no error, the query seems to be executing successfully but the problem is I have no value return.
If I do this (Superior):
@Query("select * from product where id < @product and code = @product_code") Product existsByCodeSup(@Param("product_code")String code, @Param("product") Product product);
It return nothing.

When I do this (Inferior)
@Query("select * from product where id > @product and code = @product_code") Product existsByCodeLow(@Param("product_code")String code, @Param("product") Product product);
It return nothing

Even when I do this (Equal):
@Query("select * from product where id = @product and code = @product_code") Product existsByCodeSup(@Param("product_code")String code, @Param("product") Product product);

I have a feeling that the compare with the id (Key) inside the @query() is not working at all... Unless I miss something.
I really don't know what else to try to do the NOT Equal on the Key. This is strange.

Any help will be much appreciated. Thank You!!

[dmitry-s]

@beunick I tested this approach and it works for me.

I would recommend testing your GQL query in GCP Datastore console in the web ui to make sure the query works as you expect. You can use key literals in GQL using this syntax: Key(kind, id)

So your query would look similar to this one:

select * from product where id > Key(your_kind, 1) and code = 9

[beunick]

Hi @dmitry-s
Thanks for the note.
Have you tried with UUID ? I notice that you have Long there, but in my case I am using UUID though.

I just tested this query below in GCP datastore console and the return value is empty.

select * from product where id < Key(product, '9ad36754-df55-45f5-811d-2d151c027774') and code = 'string2H'

select * from product where id = Key(product, '9ad36754-df55-45f5-811d-2d151c027774') and code = 'string2H'

select * from product where id > Key(product, '9ad36754-df55-45f5-811d-2d151c027774') and code = 'string2H'

All those query in the console return empty value.

I really don't know why.

[beunick]

Hi @dmitry-s any luck testing this using Key with UUID ?

[dmitry-s]

Hi @beunick,

I just verified that it works with string keys.

SELECT * FROM `books` where __key__ = Key(books, '76dac4ea-2ab2-4319-9b5b-128b8ba86588') and author = 'author2'

I am not sure why your query is not working. Try removing the and code = 'string2H' part to see if the first part of the filter works.

[beunick]

Hi @dmitry-s
Thanks for the note. From the console when using key instead of id it works indeed with UUID. All the query I describe in my previous post now work with key in the console.

Inside my application now, when I replace "id" by "key" like this
@Query("select * from product where __key__ > @product and code = @product_code ")

I have this error:
org.springframework.cloud.gcp.data.datastore.core.mapping.DatastoreDataException: An ID property was required but does not exist for the type: class com.google.cloud.datastore.Key

I am wondering if in your test you can make it work successfully using UUID in the Key inside your @query in your code ?

[dmitry-s]

@beunick I've tested that today and have confirmed that it works with String ID as well as Long.

The error indicates that there was an issue with retrieving id property from an entity.

Just to clarify, are you using String field annotated with @Id in your pojo?

Could you share the source code for both your entity and the method from the repository?

Thanks

[beunick]

Thank you @dmitry-s for the note. Below you will see code for Entity, Repository, Service and the code I create to generate the Key.

The code in Repository with ID in the query:
`.

   @Repository

    public interface ProductRepository extends DatastoreRepository<Product, Key> { 

@Query("select * from product where id < @product and code = @product_code and store_ref_id= @store_id")
Product existsByCodeWithKeySup(@Param("product")Product product, @Param("product_code")String code, 
    @Param("store_id")Key storeRefKey);

    @Query("select * from product where id > @product and code = @product_code and store_ref_id= @store_id")
Product existsByCodeWithKeyLow(@Param("product")Product product, @Param("product_code")String code, 
    @Param("store_id")Key storeRefKey);

    }

`

The code in Repository with key in the query:

`

  @Repository

   public interface ProductRepository extends DatastoreRepository<Product, Key> { 

@Query("select * from product where __key__ < @product and code = @product_code and store_ref_id= @store_id")
Product existsByCodeWithKeySup(@Param("product")Product product, @Param("product_code")String code, 
    @Param("store_id")Key storeRefKey);

    @Query("select * from product where __key__ > @product and code = @product_code and store_ref_id= @store_id")
Product existsByCodeWithKeyLow(@Param("product")Product product, @Param("product_code")String code, 
    @Param("store_id")Key storeRefKey);

`

Product Entity:

`

   @Entity(name="product")
   public class Product {
   
    @Id
private Key id;
  
private String name;

private String code;
    
    @Field(name="store_ref_id")
    private Key storeId

    // GETTER and SETTER

}
`

In the service:
`

   private void validateUniqueProductUpdate(Product product) {
             Product productFoundSup = this.productRepo.existsByCodeWithKeySup(product, product.getCode(), product.getStoreId());
	Product productFoundLow = this.productRepo.existsByCodeWithKeyLow(product, product.getCode(), product.getStoreId());

    //....REST OF CODE

}

`

By the way just to let you know, here is the method that I used to generate my Key every time I create a new entity before saving to the DB:
`

   public Key getIdCreation(String kindName) {
	Datastore datastore = DatastoreOptions.getDefaultInstance().getService();
	
	Key id = datastore.newKeyFactory()
		    .setKind(kindName)
		    .newKey(UUID.randomUUID().toString());
	return id;
}

`

[dmitry-s]

Hi @beunick ,

I was unable to replicate the issue using the code you provided - everything worked as expected for me. Could you create a small application that I can run and get this error message? (perhaps on github)

I am curious - could you explain what exactly you are trying to accomplish with the validateUniqueProductUpdate method?

Btw, the queries you use could be expensive because they are going to fetch all rows. I would recommend using limit 1 if you just need one entity.

Also, which version of spring-cloud-gcp are you using?

Thanks