real production scenario. Background: 6 tables: fund, account, period, weight, weight, position.
Fund: The fund information, for example: fund name, total Asset, start time.. etc. Account: each fund has an account (one to one) to fund. period: time period ( for example: 2000-01-01 to 2000-12-31) periodweight: at a certain period, the target holding weight. holding: IBM, Oracle, GE are stock holdings. position: IBM($3000), oracle($2000), GE($5000)
if I have a fund name: a fake fund that has a target holding for IBM (30%), Oracle (20%), GE (50%) for the period (2000-01-01 to 2000-12-31), valid the position for 2000-01-01 is 10%, 10%, 80% and 2000-01-02 is 20%, 20%, 60% will be presented as these entries in the table
Account: id account_Number Fund_id * 1 0001 10 2 0002 11 Fund: id name other properties... * 10 fake fund xxx 11 another fake one xxx period: id start_time end_time fund_id * 3 2000-01-01 2000-12-31 10 4 2001-01-01 2001-12-31 10 periodWeight: id target_weight holding_id period_id *11 30% 21 3 *12 20% 22 3 *13 50% 23 3 holding: id name order other properties... *21 IBM 1 xxx *22 Oracle 2 xxx *23 GE 3 xxx position: id Account_id holding_id date actual_position 1 1 11 2000-01-01 10% 2 1 12 2000-01-01 10% 3 1 13 2000-01-01 80% 4 1 11 2000-01-02 20% 5 1 12 2000-01-02 20% 6 1 13 2000-01-02 60%
Java class
Account{ @onetoOne(mappedby="account") Fund f; @oneToMany Set<Position> positions; } Fund{ @manyToOne Account account; @oneToMany(mappedby="fund") Set<Period> periods; } Period{ @manyToOne Fund fund; @oneToMany(mappedby="period") Set<PeriodWeight> periodWeights; } PeriodWeight{ @manyToOne Period period; @ManyToOne Holding holding } Holding{ @OneToMany(mappedby="holding") Set<PeriodWeight> periodWeights; @OneToMany Set<Position> positions; } Position{ @manyToOne Account account; @manyToOne Holding holding; }
I want to receive a request: Based on the date (2000-01-01) and the name of the fund (fake fund). I want to create a fund object that contains an account and a period (2000-01-01 - 2000-12-31), and the period contains the Weight period and periodWeight contains the holding, and the holding has positions for (2000-01-01). when there is no such position, for example, I request 2000-01-03 and a fake fund, I want to have a structure, just the position is an empty set in the holding.
this hql can load the structure correctly if there is data.
select f from Fund f inner join fetch f.account a inner join fetch f.period p inner join fetch p.periodWeight w inner join fetch w.holding h inner join fetch h.positions po where f.name=:name and :date between p.start_date and p.end_date and :date=po.date and po.account= a
the problem is that there is no data in the position table on that day, it returns null. I need sql to give me a structure, when there is no data, it can load everything except the position, just leave the field empty.
another question is the best way to load such a complex structure? one hql like these, or load part of the structure into one hql, then another part with another hql? because in sql you always load them one by one, first it is a fund, then a period, then a weight, then hold, then a position, etc. weight should be sorted based on retention order.
select f from Fund f inner join fetch f.account a inner join fetch f.period p inner join fetch p.periodWeight w inner join fetch w.holding h left join fetch h.positions po with po.account= a and :date=po.date where f.name=:name and :date between p.start_date and p.end_date
something is really close, but it gives me an error,
org.hibernate.hql.ast.QuerySyntaxException: with-clause not allowed on fetched associations; use filters