英文:
Can't Create a View Projection with Two Nested Associations
问题
我有一个查询由于多个关联关系而花费了很长时间。因此，我想创建一个视图来获取只必要的数据，并使用JOIN Fetch来优化这个查询。然而，JPA返回了一个错误：在结果元组中找不到别名！请确保你的查询定义了别名！
这是我创建的视图的定义：
public interface BoatContractView {
Integer getId();
String getName();
Boolean getMaintenance();
Set<ServiceBoatView> getBoatServices();
interface ServiceBoatView {
Integer getId();
List<ContractDocumentDto> getContracts();
interface ContractDocumentDto {
Integer getId();
ContractType getContractType();
}
}
}
以及提供者中的方法：
@Query("FROM Boat b JOIN FETCH b.boatServices bs WHERE b.marina.id = ?1 AND b.active = true ")
List<BoatContractView> findBoatContractViewByMarinaIdAndActiveTrue(Integer marinaId);
英文:
I have a query that is taking a long time due to multiple relationships. So, I want to create a View to fetch only the necessary data and use JOIN Fetch to optimize this query. However, JPA is returning an error No aliases found in result tuple! Make sure your query defines aliases!.
Here is the definition of the view I created:
public interface BoatContractView {
Integer getId();
String getName();
Boolean getMaintenance();
Set<ServiceBoatView> getBoatServices();
interface ServiceBoatView {
Integer getId();
List<ContractDocumentDto> getContracts();
interface ContractDocumentDto {
Integer getId();
ContractType getContractType();
}
}
}
And the method within the provider:
@Query("FROM Boat b JOIN FETCH b.boatServices bs WHERE b.marina.id = ?1 AND b.active = true ")
List<BoatContractView> findBoatContractViewByMarinaIdAndActiveTrue(Integer marinaId);
答案1
得分: 0
你看到的错误信息是因为你在查询中没有使用别名。你可以尝试使用以下查询语句：
@Query("SELECT b.id as id, b.name as name, b.maintenance as maintenance, bs.id as serviceId, cd.id as contractId, cd.contractType as contractType FROM Boat b JOIN b.boatServices bs JOIN bs.contracts cd WHERE b.marina.id = ?1 AND b.active = true ")
List<BoatContractView> findBoatContractViewByMarinaIdAndActiveTrue(Integer marinaId);
这个查询使用别名将查询结果映射到视图中的字段。然后，你可以使用这些别名来填充你的视图。
英文:
The error message you are seeing is because you are not using aliases in your query. You can try the following query instead:
@Query("SELECT b.id as id, b.name as name, b.maintenance as maintenance, bs.id as serviceId, cd.id as contractId, cd.contractType as contractType FROM Boat b JOIN b.boatServices bs JOIN bs.contracts cd WHERE b.marina.id = ?1 AND b.active = true ")
List<BoatContractView> findBoatContractViewByMarinaIdAndActiveTrue(Integer marinaId);
This query uses aliases to map the results of the query to the fields in your view. You can then use these aliases to populate your view.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论