So i have this @Entity
public class RecipeDAO extends AbstractDAO {
private Boolean visible;
@ManyToMany(mappedBy = "recipes", cascade = CascadeType.ALL)
private Set<TopicDAO> topics;
@MapKey(name = "id.locale")
@OneToMany(mappedBy = "recipe", cascade = CascadeType.ALL, orphanRemoval = true)
private Map<String, Localization> localizations;
@MapKey(name = "id.locale")
@OneToMany(mappedBy = "recipe", cascade = CascadeType.ALL, orphanRemoval = true)
private Map<String, JsonData> json;
}
and in the repository interface i have this @Query:
@Query("select new com.fullstack.dtos.RecipeDTO(t.id, t.createdBy, t.lastModifiedBy, t.created, t.lastModified, t.version, t.visible, l.title, l.description, l.footnote, topics) from recipes t inner join t.localizations l inner join t.topics topics where l.id.locale = :lang and l.title like %:title%")
Page<RecipeDTO> findAllByTitle(String lang, String title, Pageable pageable);
This is a multi-lingue system hence the localizations and json in maps representing multiple languages and this query works if i don't include topics. Now if i do include topics i cant seem to find a way to put them into the projection class. If i use Collection (or List or Set) it says the type isnt the same and expects TopicDAO. If i include topicDAO if finds all topics but assigns a topic to a recipe This is a set of logs i made:
TopicDAO(id=1771b663-e5d2-4a09-a758-9dec919cb3c6, topic=savory)
TopicDAO(id=1de01b0d-b01c-42da-a5c5-78b2048b8f1a, topic=cheap)
TopicDAO(id=342fdc0d-a091-4669-9753-69c6b9293562, topic=not vegan)
TopicDAO(id=46ad594e-4aaf-4438-90fc-7401fca4fda4, topic=nice)
TopicDAO(id=67477796-4a6d-4b48-9c69-c9692d341d7a, topic=easy)
TopicDAO(id=be7a016d-7d6f-4cd1-a1ab-4faadf484c30, topic=nutritious)
TopicDAO(id=c29347bd-e755-4414-9a63-28f70a2498cb, topic=meat)
TopicDAO(id=d28cbaba-cd24-4256-8767-6cf9f75e5e1b, topic=lunch)
TopicDAO(id=f845f510-15ad-471e-8cfb-6b46d0ab1ec6, topic=dinner)
TopicDAO(id=fa97c9be-346b-435f-a8a1-4400c13f26cd, topic=breakfast)
Page 1 of 1 containing com.fullstack.dtos.RecipeDTO instances
[RecipeDTO(visible=true, topics=["easy"]), RecipeDTO(visible=true, topics=["breakfast"]), RecipeDTO(visible=true, topics=["nice"]), RecipeDTO(visible=true, topics=["lunch"]), RecipeDTO(visible=true, topics=["not vegan"]), RecipeDTO(visible=true, topics=["dinner"]), RecipeDTO(visible=true, topics=["cheap"]), RecipeDTO(visible=true, topics=["meat"]), RecipeDTO(visible=true, topics=["savory"]), RecipeDTO(visible=true, topics=["nutritious"])]
Now in the projection im mapping a TopicDAO to the Set basically using Set.of from the set interface. and it works. The problem is says page 1 of 1 because the database only has one item so spring is basically replicating the recipe for as many topics as it has. If i include more info on the prints, it all matches, including the @Id of the recipe meaning is the same. Is there any keywork in JPQL im missing to force this topics to go into a Collection of some sort? I even have SQL queries being logged from hibernate and it does a select * from topics where... multiple times (one for each topic) instead of selecting and returning as a collection
EDIT: AbstractDAO is a common parent to all my daos, and AbstractDTO a parent to all dtos that just contain the stuff that is global like the @Id, @Created, @CreatedBy, @Version fields
EDIT 2: I have also tried with interface based projections the result is the same, it all ends up separated eacly the same way, 10 objects in the page with each topic (basically 10 replicas of the recipe one for each topic).