Selecting where an entity contains a list which elements have another list

Question

I am writing a JPQL query in spring JPA and i have the following scenario. I have a entity Margin which contains a list of PerPeriodMargin and each element of PerPeriodMargin contains a list of MarginFactor. code:

@Entity
public class Margin {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  @OneToMany(mappedBy = "margin", cascade = CascadeType.ALL, orphanRemoval = true)
  private List<PerPeriodMargin> perPeriodMargins;
}


@Entity
public class PerPeriodMargin{
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  @ManyToOne
  private Margin margin;
  @OneToMany(mappedBy = "perPeriodMargin", cascade = CascadeType.ALL, orphanRemoval = true)
  private List<MarginFactor> marginFactors;
}

@Entity
public class MarginFactor{

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  @ManyToOne
  private Underlying underlying;
  @ManyToOne
  private PerPeriodMargin perPeriodMargin;
}

I would like to select all Margin where MarginFactor underlying.id is passed as a parameter in a single jpql query? Any suggestions?

i activated hibernate logging by this line in application.properties "logging.level.org.hibernate.SQL=DEBUG" and i have been confused about the generated SQL queries. its seems there is something wrong about that multiple join. can any one explain this.

select * from margin m inner join per_period_margin ppm on m.id = ppm.margin_id join margin_factor mf on ppm.id = mf.per_period_margin_id where mf.underlying_id = ? and m.id = (select margin_id from trading_account ta where ta.id = ?)

and on for perPeriodMargin

select perperiodm0_.margin_id as margin_i5_12_0_, perperiodm0_.id as id1_12_0_, perperiodm0_.id as id1_12_1_, perperiodm0_.end_time as end_time2_12_1_, perperiodm0_.margin_id as margin_i5_12_1_, perperiodm0_.name as name3_12_1_, perperiodm0_.start_time as start_ti4_12_1_ from per_period_margin perperiodm0_ where perperiodm0_.margin_id=?

until now all is seams good.

finally ther are two other queries that try to get marginFactors.

select marginfact0_.per_period_margin_id as per_peri6_9_0_, marginfact0_.id as id1_9_0_, marginfact0_.id as id1_9_1_, marginfact0_.bid as bid2_9_1_, marginfact0_.notional as notional3_9_1_, marginfact0_.offer as offer4_9_1_, marginfact0_.per_period_margin_id as per_peri6_9_1_, marginfact0_.settlement as settleme5_9_1_, marginfact0_.underlying_id as underlyi7_9_1_, underlying1_.id as id1_24_2_, underlying1_.digits as digits2_24_2_, underlying1_.display as display3_24_2_, underlying1_.enable as enable4_24_2_, underlying1_.enable_buy as enable_b5_24_2_, underlying1_.enable_sell as enable_s6_24_2_, underlying1_.focus_digits as focus_di7_24_2_, underlying1_.focus_position as focus_po8_24_2_, underlying1_.left_currency_id as left_cu11_24_2_, underlying1_.name as name9_24_2_, underlying1_.right_currency_id as right_c12_24_2_, underlying1_.temporary_disable as tempora10_24_2_, currency2_.id as id1_3_3_, currency2_.digits as digits2_3_3_, currency2_.enable_buy as enable_b3_3_3_, currency2_.enable_sell as enable_s4_3_3_, currency2_.name as name5_3_3_, currency2_.symbol as symbol6_3_3_, currency2_.temporary_disable as temporar7_3_3_, currency3_.id as id1_3_4_, currency3_.digits as digits2_3_4_, currency3_.enable_buy as enable_b3_3_4_, currency3_.enable_sell as enable_s4_3_4_, currency3_.name as name5_3_4_, currency3_.symbol as symbol6_3_4_, currency3_.temporary_disable as temporar7_3_4_ from margin_factor marginfact0_ left outer join underlying underlying1_ on marginfact0_.underlying_id=underlying1_.id left outer join currency currency2_ on underlying1_.left_currency_id=currency2_.id left outer join currency currency3_ on underlying1_.right_currency_id=currency3_.id where marginfact0_.per_period_margin_id=?

and as we see here in the last query there are only one where condition on perPeriodmarginId. as I think it must also contain underlying condition, because this query is the responsible of fetching marginFactors where we must extract the data that who have a specific underlyingId.

I'm really serious about this question please can someone explain that or is it a bug in hibernate!

what have you tried so far? Add your code to the question please — Leviand, Jul 20 '18 at 10:41
Use native query, ORM is not for this kind of complex relationship query. You need be very careful about the SQL generated by JPQL. — Jacob, Jul 20 '18 at 10:58
i found this post [https://stackoverflow.com/questions/47996810/how-to-search-with-jparepository-and-nested-list-of-objects?noredirect=1&lq=1], it is not verry similar with my case but i'm asking if we can do something like that right here — kimo815, Jul 20 '18 at 11:03
Is this?: `select m FROM Margin m JOIN m.perPeriodMargins ppm JOIN ppm.marginFactors mf JOIN mf.underlying und WHERE und.id = :idUnderlying` — Dherik, Jul 20 '18 at 12:14
sorry no it's not correct. i have a freak NullPointerException. — kimo815, Jul 20 '18 at 13:09
i created this nativeQuery: select * from margin m inner join per_period_margin ppm on m.id = ppm.margin_id join margin_factor mf on ppm.id = mf.per_period_margin_id where mf.underlying_id = 2 and m.id = (select margin_id from trading_account ta where ta.id = 1). the problem is when i excute this on mysql it gives the right solution but in spring data using @Query(value="", nativeQuery= true) it return a false result. — kimo815, Jul 20 '18 at 14:51

score 0 · Answer 1 · answered Jul 26 '18 at 14:50

0

This is the query:

select m 
FROM Margin m 
JOIN m.perPeriodMargins ppm 
JOIN ppm.marginFactors mf 
JOIN mf.underlying und 
WHERE und.id = :id

answered Jul 26 '18 at 14:50

Vlad Mihalcea

142,745
71
566
911

It returns a false result. for example i put und.id = 2 and it return all the margin factors (und.id = 2 and 3). can you explain that please. – kimo815 Jul 26 '18 at 15:10
Maybe multiple Margins have that Underlying with that id. Check the SQL query and the data to be sure. – Vlad Mihalcea Jul 26 '18 at 15:25
to be more clear i'm added to this query a condition on margin id "where m.id = :marginId". and it also give a false result – kimo815 Jul 26 '18 at 16:09

Selecting where an entity contains a list which elements have another list

1 Answers1