I'm trying to get a list of entities with their collection order by datetime, but I don't get it. This is my query:
trades = session.createCriteria(Trade.class) .createAlias("operations", "operations", CriteriaSpecification.INNER_JOIN) .add(Restrictions.between("operations.datetime", start, end)) .addOrder(Order.asc("operations.datetime")) .setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY) .list(); According to hibernate these are the queries:
select this_.MUREX_ID as MUREX1_0_1_, this_.PORTFOLIO as PORTFOLIO0_1_, this_.TYPE as TYPE0_1_, operations1_.ID as ID1_0_, operations1_.COMENT as COMENT1_0_, operations1_.DATETIME as DATETIME1_0_, operations1_.GBO_ID as GBO4_1_0_, operations1_.OPERATION as OPERATION1_0_, operations1_.OPERATION_ID as OPERATION6_1_0_, operations1_.OPICS_ID as OPICS7_1_0_, operations1_.STATUS_GBO as STATUS8_1_0_, operations1_.STATUS_MUREX as STATUS9_1_0_, operations1_.MUREX_ID as MUREX10_1_0_ from PGT_NY.T_PGT_NY_BLOTTER_TRADES_S this_, PGT_NY.T_PGT_NY_BLOTTER_OPERATIONS_S operations1_ where this_.MUREX_ID=operations1_.MUREX_ID and operations1_.DATETIME between ? and ? order by operations1_.DATETIME asc And:
select operations0_.MUREX_ID as MUREX10_1_, operations0_.ID as ID1_, operations0_.ID as ID1_0_, operations0_.COMENT as COMENT1_0_, operations0_.DATETIME as DATETIME1_0_, operations0_.GBO_ID as GBO4_1_0_, operations0_.OPERATION as OPERATION1_0_, operations0_.OPERATION_ID as OPERATION6_1_0_, operations0_.OPICS_ID as OPICS7_1_0_, operations0_.STATUS_GBO as STATUS8_1_0_, operations0_.STATUS_MUREX as STATUS9_1_0_, operations0_.MUREX_ID as MUREX10_1_0_ from PGT_NY.T_PGT_NY_BLOTTER_OPERATIONS_S operations0_ where operations0_.MUREX_ID in ( select this_.MUREX_ID from PGT_NY.T_PGT_NY_BLOTTER_TRADES_S this_, PGT_NY.T_PGT_NY_BLOTTER_OPERATIONS_S operations1_ where this_.MUREX_ID=operations1_.MUREX_ID and operations1_.DATETIME between ? and ? ) Any help would be really useful.
Thanks in advance!