Support direct DTO mapping for repository query methods

[Zaky7]

Hi,

I am writing a custom query but as I am not able to cast the expression to some custom objects. In JPA you can do write custom queries like

@Query("SELECT new com.baeldung.aggregation.model.custom.CommentCount(c.year, COUNT(c.year)) "
  + "FROM Comment AS c GROUP BY c.year ORDER BY c.year DESC")
List<CommentCount> countTotalCommentsByYearClass(); 

But with ReactiveCassandraRepository I am getting errors org.springframework.data.cassandra.CassandraQuerySyntaxException:

How can I cast to custom object in ReactiveCassandraRepository?

[mp911de]

You can't because CQL isn't JPQL. JPQL is being parsed by JPA implementations while Cassandra is passing on the query directly to the Cassandra server.

[Zaky7]

@mp911de I understand. how we can project Dto in the custom query?

 @Query("select MAX(salary) as maxSalary , MIN(salary) as minSalary from users where department = ?0")
    fun getAggSalary(
        departMent: String,
    ): Flux<AggSalary>


    data class AggSalary(val maxSalary, val minSalary) 

because I am able to run this query on cqsh client but on spring data Cassandra repository, it is giving error while projecting

[mp911de]

Right, sorry, I forgot to mention that you can use interface or DTO projections depending on what you're aiming for. Can you attach the exception message of what's going wrong? I assume that the result is being mapped onto the underlying entity before being projected to the DTO.

[Zaky7]

the exception basically says the maxSalary and minSalary not found in the class Users.

[Zaky7]

as soon as I add maxSalary and minSalary in my Users class, it is working fine. is any better way of doing this??

[mp911de]

We currently do not have the means to map results through query methods onto DTO's directly as we always need to consider the underlying entity in terms of column and type mapping. That is a general issue we have with all Spring Data modules and we plan to do something about it in general. For the time being, please use the CassandraTemplate directly to run aggregation queries without intermediate entity mapping.