There is often the business question to show all categories and how often these categories are used.
This question is easy to answer with an query:
SELECT c.*, count(*) FROM category_assignment ca LEFT JOIN category c on ca.c_id = c.id group by c.id
What i am asking for is your suggested way to map the result set based on the following:
@Entity
public class CategoryAssignment {
@Id
int id;
@ManyToOne(fetch = FetchType.EAGER)
private Category category;
@ManyToOne(fetch = FetchType.EAGER)
private Car car;
}
@Entity
public class Category {
@Id
String id;
TypeEnum type;
...
}
@Entity
public class Car {
@Id
int id;
String name;
...
}
From my point of view the best result of the mapping would be to get a custom object which contains the Category as entity and the count number as additional variable right out of the repository call:
MappingResult result = repository.getCategoriesAndTheirCountOfType(TypeEnum type);
public class MappingResult {
Category category;
BigInteger count;
}
To only way i was able to achieve it until now was to map the result set manually. But i hope there are easier ways to map it.
You can use Projections to get it:
public interface CategoryAndCount {
Category getCategory();
Long getUsageCount();
}
public interface CategoryAssignmentRepository extends CrudRepository<CategoryAssignment, Integer> {
@Query("select c as category, count(*) as usageCount from CategoryAssignment ca join ca.category c where c.type = ?1 group by c")
CategoryAndCount getCategoriesAndTheirCountOfType(TypeEnum type);
}
Don't forget to add alias to field in the query (c as category, count(*) as usageCount).