a real production scenario. background: 6 tables: Fund, Account, period, periodweight, holding, 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:fake fund, which has a target holding for IBM (30%),Oracle(20%), GE(50%) for the period(2000-01-01 to 2000-12-31), the actural position for the 2000-01-01 is 10%,10%, %80% and on 2000-01-02 is 20%,20%,60% will be represents as these records in table
I want to have a query: Based on the date (2000-01-01) and the fund name(fake fund). I want to build a Fund object, which contains the account and period(2000-01-01 to 2000-12-31), and period contains the periodWeight, and periodWeight contains holding, and holding contains postions for (2000-01-01). when there is no such position, for example, I query 2000-01-03 and fake fund, I want to have the structure, just the position is an empty set in the holding.
this hql can load the structure correctly if there is data.
the problem is when there is no data at the position table for that day, it return null. I need a sql to give me the structure when there is no data, it can load everything except the position, just leave the position set empty.
another question is what is the better way to load such an complex structure? one hql like these or load part of the structure by one hql then other part by another hql? beause in sql, you alway load them one by one, first is the fund, then period, then weight,then holding, then position etc.. the weight need to be sort based on the holding order.
is something really close, but this give me error,