Select only specific columns from joined tables (Many-to-Many) in Spring Data JPA

Question

The purpose is to select columns from joined tables (Many-to-Many).
The problem i have is to select two columns from a joined Many-to-Many table.

I'am using Springboot 2.3 and Spring data Jpa.

I have this data model, and what i want to fetch are the blue boxed fields

So the native query could look like this (if i am right ...)

SELECT bg.id, bg.name, p.name, c.name, c.short_desc FROM boardgame as bg 
 JOIN boardgame_category bgc on bg.id = bgc.fk_game 
 JOIN publisher p on bg.fk_publisher = p.id 
 JOIN category c on bgc.fk_category = c.id 
 WHERE bg.id = :id 

I first tried to work with dto in JPQL statment

public class BoardgameDto {

    private long id;

    private String name;

    private String publisherName;

    private Set<CatregoryDto> categoryDto;

    // setter, getter etc...
}

public class CategoryDto {

    private String name;

    private String shortDesc;

    // setter, getter etc...
}

The JQPL query could look like this , but it doesn't work (IDE shows errors on CategoryDto)

/* THIS DOESN'T WORK */
SELECT new org.moto.tryingstuff.dto.BoardgameDto(bg.id, bg.name, p.name, 
new org.moto.tryingstuff.dto.CategoryDto(c.name, c.short_desc)) FROM Boardgame as bg, Publisher as p, Category as c

Well, I think the problem I have with this way of doing is that the dto's contructor can't receive a collection as written here, and i think neither another contructor in parameter.

Then i started looking at Criteria Queries, especialy multiselect, Tuple, Dto, but it look like i had same kind of problems so i didn't dive deeper into it.

Finally i used a JpaRepository and it's findById() method like this

public interface BoardgameRepository extends JpaRepository<Boardgame, Long> {
}

// In a test or service method
Boardgame game = repository.findById(long id);

Then i filter the fields i need to keep through mappings in Service or Controller layer. So the front only received need datas.

But it feel a bit overkill,
Am I missing something, any part of the framework that would allow me to select only specific columns?

Answer 1

As you wrote, you can't use a collection as the parameter of a constructor expression. That's because the expression gets applied to each record in the result set. These records are a flat data structure. They don't contain any collections. Your database returns a new record for each element in that collection instead.

But your constructor expression fails for a different reason. You're trying to combine 2 constructor expressions, and that's not supported. You need to remove the 2nd expression and perform that operation within the constructor of your DTO.

So, your query should look like this:

SELECT new org.moto.tryingstuff.dto.BoardgameDto(bg.id, bg.name, p.name, c.name, c.short_desc) FROM Boardgame as bg <Your JOIN CLAUSES HERE>

And the constructor of your BoardgameDto like this:

public class BoardgameDto {

  public BoardgameDto(Long id, String gameName, String publisherName, String categoryName, String description) {
     this.id = id;
     this.name = gameName;
     this.publisherName = publisherName;
     this.category = new Category(categoryName, description);
  }

  ...
}