Filtering out nested objects in JPA query.

huangapple go评论77阅读模式
英文:

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: &quot;test&quot;,
    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: &quot;test 2&quot;,
    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: &quot;test 3&quot;,
    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: &quot;test&quot;,
    journeys: [
      {
        id: 5
      }
    ]
    promMeasurements: [
      {
        id: 101,
        value: 6,
        user: {
          id: 12
        }
      }
    ]
  },
  {
    id: 2,
    name: &quot;test 2&quot;,
    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&lt;Prom&gt; 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&lt;Prom&gt; findAllByJourneysIdAndPromMeasurements_Patient_Id(Long journeyId, Long patientId);
List&lt;Prom&gt; 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.

Filtering out nested objects in JPA query.

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 = &quot;prom_journey&quot;, joinColumns = @JoinColumn(name = &quot;prom_id&quot;),
		inverseJoinColumns = @JoinColumn(name = &quot;journey_id&quot;))
  @JsonBackReference(value = &quot;prom-journeys&quot;)
  private Set&lt;Journey&gt; journeys = new HashSet&lt;&gt;();

  @OneToMany(mappedBy = &quot;prom&quot;)
  @JsonManagedReference(value = &quot;prom-prom-measurements&quot;)
  private List&lt;PromMeasurement&gt; promMeasurements;
}

public class PromMeasurement extends DateAudit {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @ManyToOne(fetch = FetchType.EAGER)
  @JsonIgnoreProperties({ &quot;hibernateLazyInitializer&quot;, &quot;handler&quot; })    
  @JsonBackReference(value = &quot;prom-prom-measurements&quot;)
  private Prom prom;

  @ManyToOne(fetch = FetchType.LAZY)
  @JsonIgnoreProperties({ &quot;hibernateLazyInitializer&quot;, &quot;handler&quot; })
  @JsonBackReference(value = &quot;patient-prom-measurements&quot;)
  private User patient;

  @ManyToOne(fetch = FetchType.LAZY)
  @JsonIgnoreProperties({ &quot;hibernateLazyInitializer&quot;, &quot;handler&quot; })
  @JsonBackReference(value = &quot;journey-prom-measurements&quot;)
  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 = &quot;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&quot;)

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.

huangapple
  • 本文由 发表于 2020年8月5日 21:50:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/63266634.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定