英文:
Filtering out nested objects in JPA query
问题
你想要查询满足两个条件的数据:journey的id为5和promMeasurement的user的id为12。你尝试过的查询似乎有问题,导致结果中每个'prom'显示两次。这里是一个可能的查询示例,可能有助于解决问题:
List<Prom> findAllByJourneysIdAndPromMeasurements_User_Id(Long journeyId, Long userId);
这个查询应该返回满足journey的id为5和promMeasurement的user的id为12条件的'prom'列表。希望这可以帮助你解决问题。
英文:
I'm struggling to filter on a third level nested object within my JPA repository query.
I have the following object, in which I want to receive all the proms which are part of the journey with id = 5 AND only the promMeasurement objects from the user with id = 12.
proms: [
{
id: 1,
name: "test",
journeys: [
{
id: 5
},
{
id: 6
},
]
promMeasurements: [
{
id: 101,
value: 6,
user: {
id: 12
}
},
{
id: 102,
value: 2,
user: {
id: 13
}
},
{
id: 103,
value: 8,
user: {
id: 14
}
},
]
},
{
id: 2,
name: "test 2",
journeys: [
{
id: 5
},
{
id: 6
},
]
promMeasurements: [
{
id: 223,
value: 1,
user: {
id: 12
}
},
{
id: 224,
value: 5,
user: {
id: 13
}
},
{
id: 225,
value: 3,
user: {
id: 14
}
},
]
},
{
id: 3,
name: "test 3",
journeys: [
{
id: 7
}
]
promMeasurements: [
{
id: 223,
value: 1,
user: {
id: 26
}
},
{
id: 224,
value: 5,
user: {
id: 33
}
},
{
id: 225,
value: 3,
user: {
id: 4
}
},
]
}
]
So the outcome of this all would be to have the following object in the end:
proms: [
{
id: 1,
name: "test",
journeys: [
{
id: 5
}
]
promMeasurements: [
{
id: 101,
value: 6,
user: {
id: 12
}
}
]
},
{
id: 2,
name: "test 2",
journeys: [
{
id: 5
}
]
promMeasurements: [
{
id: 223,
value: 1,
user: {
id: 12
}
}
]
}
]
Right now, I'm already able to do the filtering part on journeys by using this query:
List<Prom> findAllByJourneysId(Long id);
But filtering the promMeasurements didn't yet succeed.
A few examples of things I've tried. But those don't work as they just return the same result as the query above.
List<Prom> findAllByJourneysIdAndPromMeasurements_Patient_Id(Long journeyId, Long patientId);
List<Prom> findAllByJourneysIdAndPromMeasurementsPatientId(Long journeyId, Long patientId);
Query log of the last function:
[DEBUG] 2020-08-06 10:57:56.028 [qtp1976725830-44] SQL - select prom0_.id as id1_38_, prom0_.createdAt as createdA2_38_, prom0_.updatedAt as updatedA3_38_, prom0_.name as name4_38_, prom0_.threshold as threshol5_38_ from prom prom0_ left outer join prom_journey journeys1_ on prom0_.id=journeys1_.prom_id left outer join journey journey2_ on journeys1_.journey_id=journey2_.id left outer join prommeasurement prommeasur3_ on prom0_.id=prommeasur3_.prom_id left outer join user user4_ on prommeasur3_.patient_id=user4_.id left outer join user_address user4_1_ on user4_.id=user4_1_.user_id where journey2_.id=? and user4_.id=?
When I manually execute this function, I get the following result.
As you can see, each 'prom' is shown two times, which is already not correct.
Someone that can help me with this?
Entities:
public class Prom extends DateAudit {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "prom_journey", joinColumns = @JoinColumn(name = "prom_id"),
inverseJoinColumns = @JoinColumn(name = "journey_id"))
@JsonBackReference(value = "prom-journeys")
private Set<Journey> journeys = new HashSet<>();
@OneToMany(mappedBy = "prom")
@JsonManagedReference(value = "prom-prom-measurements")
private List<PromMeasurement> promMeasurements;
}
public class PromMeasurement extends DateAudit {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne(fetch = FetchType.EAGER)
@JsonIgnoreProperties({ "hibernateLazyInitializer", "handler" })
@JsonBackReference(value = "prom-prom-measurements")
private Prom prom;
@ManyToOne(fetch = FetchType.LAZY)
@JsonIgnoreProperties({ "hibernateLazyInitializer", "handler" })
@JsonBackReference(value = "patient-prom-measurements")
private User patient;
@ManyToOne(fetch = FetchType.LAZY)
@JsonIgnoreProperties({ "hibernateLazyInitializer", "handler" })
@JsonBackReference(value = "journey-prom-measurements")
private Journey journey;
private Integer value;
}
public class User extends DateAudit {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
}
public class Journey extends DateAudit {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
}
答案1
得分: 2
你可以尝试使用 @Query 注解:
@Query(value = "SELECT distinct p FROM Prom p JOIN FETCH p.journeys pj JOIN FETCH p.promMeasurements pp JOIN FETCH pp.patient ppp WHERE pj.id = :journeyId AND ppp.id = :patientId")
List<Prom> findDistinctByJourneysIdAndPromMeasurementsPatientId(Long journeyId, Long patientId);
英文:
You can try it with the @Query annotation
@Query(value = "SELECT distinct p FROM Prom p JOIN FETCH p.journeys pj JOIN FETCH p.promMeasurements pp JOIN FETCH pp.patient ppp WHERE pj.id = :journeyId AND ppp.id = :patientId")
List<Prom> findDistinctByJourneysIdAndPromMeasurementsPatientId(Long journeyId, Long patientId);
答案2
得分: 1
从Spring Boot版本2.x开始,下划线是不必要的。
可能问题出在您混合使用下划线:
PromMeasurements_Patient_Id
以及没有下划线的查询部分:
findAllByJourneysId
尝试在查询语言使用上保持一致。
英文:
Not sure which version of spring-boot you are using but from version 2.x the underscores and not necessary.
Possibly the problem comes with the fact that you are mixing both underscores:
PromMeasurements_Patient_Id
and without underscores parts of the query:
findAllByJourneysId
Try to stay consistent in your query language usage.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论