Hi y'all

I've got a @OneToOne relationship of these tables: Lacre(table name is SVE_LACRE ), Setor(table name = SVE_SETOR), both are @Audited and Revisoes(table name=SVE_LACRE_REVISOES) as my custom @RevisionEntity. I was trying to do a query per date range and Setor code.

The first thing i did was to do this query only per date range as showed below, it worked fine but i checked the sql on the output and I didn't understand much. Below follows the query and the output:

[[Query]]

AuditReader reader = AuditReaderFactory.get(hibernateSession);

AuditQuery query = reader.createQuery().forRevisionsOfEntity(Lacre.class, false, false);

query.add(AuditEntity.revisionProperty("data").between(dataInicialTmp.getTime(), dataFinalTmp.getTime()));

and the output:

[[Output]]

select

        lacre_aud0_.OID_COD_LACRE as OID1_33_0_,

        lacre_aud0_.VERSAO_REVISAO as VERSAO2_33_0_,

        revisoes1_.OID_REVISAO as OID1_22_1_,

        lacre_aud0_.REVTYPE as REVTYPE33_0_,

        lacre_aud0_.DES_CANCELAMENTO as DES4_33_0_,

        lacre_aud0_.DES_MOTIVO_SUBSTITUICAO as DES5_33_0_,

        lacre_aud0_.DES_OPERADOR as DES6_33_0_,

        lacre_aud0_.VE_PLACA as VE7_33_0_,

        lacre_aud0_.DES_SERVICO as DES8_33_0_,

        lacre_aud0_.DES_SESSAO as DES9_33_0_,

        lacre_aud0_.IDT_SITUACAO as IDT10_33_0_,

        lacre_aud0_.VE_UF as VE11_33_0_,

        lacre_aud0_.OID_COD_REMESSA as OID12_33_0_,

        lacre_aud0_.COD_ORGAO as COD13_33_0_,

        revisoes1_.DTA_REVISAO as DTA2_22_1_

    from

        SVE_LACRE_AUD lacre_aud0_,

        SVE_LACRE_REVISOES revisoes1_,

        SVE_LACRE_REVISOES revisoes2_

    where

        lacre_aud0_.VERSAO_REVISAO=revisoes2_.OID_REVISAO

        and lacre_aud0_.REVTYPE<>?

        and revisoes2_.DTA_REVISAO>=?

        and revisoes2_.DTA_REVISAO<=?

        and lacre_aud0_.VERSAO_REVISAO=revisoes1_.OID_REVISAO

    order by

        lacre_aud0_.VERSAO_REVISAO asc

the question is: Why not just to use the "between" to compare these dates and use only one revision table instead of two(revisoes1_, revisoes2_)?

Second thing is, What if i wanted to add the Setor restriction? I tryid by adding query.add(AuditEntity.property("setor.codigo").eq("1231")); but it does not recognize this property "setor.codigo".

Is there any trick to do it?

Thanks in advance