Spring Data - Projection and @Query nested property

Question

Assume I have those DTO:

public interface ForumDTO extends ForumBaseDTO{
    Integer getId();
    ThreadDTO getLastThread();
}
public interface ThreadDTO {
    Integer getId();
    Integer getCommentCount()
}

In my Repository I have this query using those DTO as projection:

   @Query("select forum.id as id, " +
   "forum.name as name, " +
   "lastThread.id as lastThread_id " +
   "from Forum forum " +
   "inner join forum.lastThread as lastThread " +
   "where forum.parent.id = ?:"
   )
   Iterable<ForumDTO> findAllByParentId(Integer id);

I can access id,name in ForumDTO using this repo just fine, but with lastThread it just return null. I have tried as lastThread.Id,as lastThread_id, as lastThreadId but none of them work.

Answer 1

You're almost there.

You need to access it from forum to follow out the foreign key:

@Query("select forum.id as id, " +
   "forum.name as name, " +
   "**forum.lastThread.id** as lastThread_id " +
   "from Forum forum " +
   "inner join forum.lastThread as lastThread " +
   "where forum.parent.id = ?:"
   )
   Iterable<ForumDTO> findAllByParentId(Integer id);

That said, you're killing yourself with extra work. The same Query can be written as:

@Query("select forum from Forum where forum.parent.id = :forumId")
Iterable<ForumDTO> findAllByParentId(@Param("forumId")Integer id);

You just need to make sure that the foreign key to Parent is present on the entity.

Also notice the @Param annotation. It makes your parameters easier to track, and also does some basic type checking against the db. It's very useful to prevent SQL injection attacks, and UncheckedTypeExceptions.