This is the generated SQL query:
select
t...
# questionsp
pablo.pazos
05/16/2025, 2:36 AMThis is the generated SQL query:
select
this_.id as id1_63_2_,
this_.version as version2_63_2_,
this_.byte_size as byte_siz3_63_2_,
this_.uid as uid4_63_2_,
this_.file_type as file_typ5_63_2_,
this_.file_location as file_loc6_63_2_,
this_.lifecycle_state as lifecycl7_63_2_,
this_.commit_audit_id as commit_a8_63_2_,
this_.preceding_version_uid as precedin9_63_2_,
this_.contribution_id as contrib10_63_2_,
this_.hash as hash11_63_2_,
this_.data_id as data_id12_63_2_,
data_alias1_.id as id1_42_0_,
data_alias1_.version as version2_42_0_,
data_alias1_.time_committed as time_com3_42_0_,
data_alias1_.last_updated as last_upd4_42_0_,
data_alias1_.uid as uid5_42_0_,
data_alias1_.data_indexed as data_ind6_42_0_,
data_alias1_.template_id as template7_42_0_,
data_alias1_.time_created as time_cre8_42_0_,
data_alias1_.archetype_id as archetyp9_42_0_,
data_alias1_.versioning_id as version10_42_0_,
data_alias1_.lname_id as lname_i11_42_0_,
data_alias1_.ehr_uid as ehr_uid12_42_0_,
data_alias1_.sync_master as sync_ma13_42_0_,
data_alias1_2_.end_time as end_time2_9_0_,
data_alias1_2_.location as location3_9_0_,
data_alias1_2_.subject_id as subject_4_9_0_,
data_alias1_2_.start_time as start_ti5_9_0_,
data_alias1_2_.category as category6_9_0_,
data_alias1_2_.composer_id as composer7_9_0_,
data_alias1_3_.source_id as source_i2_54_0_,
data_alias1_3_.target_id as target_i3_54_0_,
data_alias1_4_.performer_id as performe2_57_0_,
data_alias1_5_.actor_type as actor_ty2_1_0_,
data_alias1_6_.is_queryable as is_query2_35_0_,
data_alias1_6_.is_modifiable as is_modif3_35_0_,
data_alias1_6_.subject_id as subject_4_35_0_,
data_alias1_7_.parent_id as parent_i2_36_0_,
case
when data_alias1_1_.id is not null then 1
when data_alias1_2_.id is not null then 2
when data_alias1_3_.id is not null then 3
when data_alias1_4_.id is not null then 4
when data_alias1_5_.id is not null then 5
when data_alias1_6_.id is not null then 6
when data_alias1_7_.id is not null then 7
when data_alias1_.id is not null then 0
end as clazz_0_,
versioning2_.id as id1_43_1_,
versioning2_.version as version2_43_1_,
versioning2_.last_version as last_ver3_43_1_,
versioning2_.valid_until as valid_un4_43_1_
from
version this_
inner join
locatable_index data_alias1_
on this_.data_id=data_alias1_.id
left outer join
party_identity_index data_alias1_1_
on data_alias1_.id=data_alias1_1_.id
left outer join
composition_index data_alias1_2_
on data_alias1_.id=data_alias1_2_.id
left outer join
party_relationship_index data_alias1_3_
on data_alias1_.id=data_alias1_3_.id
left outer join
role_index data_alias1_4_
on data_alias1_.id=data_alias1_4_.id
left outer join
actor_index data_alias1_5_
on data_alias1_.id=data_alias1_5_.id
left outer join
ehr_status_index data_alias1_6_
on data_alias1_.id=data_alias1_6_.id
left outer join
folder_index data_alias1_7_
on data_alias1_.id=data_alias1_7_.id
inner join
locatable_index_versioning versioning2_
on data_alias1_.versioning_id=versioning2_.id
where
(
this_.data_id is null
or (
this_.data_id is not null
and (
(
versioning2_.valid_until<?
)
)
)
)
s
Steve Osguthorpe
05/16/2025, 9:49 AMMy guess is that the inner join created here...
Copy code
...
data {
versioning {
...❤️ 2
Steve Osguthorpe
05/16/2025, 10:03 AMFrom memory and untested, as it's been a while since I've used the criteria API. But something like the following might work.
Copy code
Version.withCriteria {
createAlias('data', 'versionData', JoinType.LEFT_OUTER_JOIN)
createAlias('versionData.versioning', 'versioningData', JoinType.LEFT_OUTER_JOIN)
or {
isNull('versionData.id')
lt('versioningData.validUntil', date_version_at_time)
}
}Steve Osguthorpe
05/16/2025, 10:13 AMI also think you might be able to alter the join type inline to match what you had before without creating aliases upfront.
So should work.
Copy code
Version.withCriteria {
data (JoinType.LEFT_OUTER_JOIN) {
versioning (JoinType.LEFT_OUTER_JOIN) {
or {
isNull ('validUntil')
lt('validUntil', date_version_at_time)
}
}
}
}p
Pablo Pazos
05/16/2025, 2:12 PMThanks Steve, I'll try it
👍 1
p
pablo.pazos
05/16/2025, 6:43 PMI have completed the query with your suggestions and so far it's retrieving the right data, thanks a lot @Steve Osguthorpe
👌 1
pablo.pazos
05/16/2025, 6:44 PMUntitled
pablo.pazos
05/16/2025, 6:44 PMI'll also try the inline flavor, it looks nicer 🙂
👍 1
s
Steve Osguthorpe
05/17/2025, 10:01 AMNo problem. Glad you got it working.
❤️ 1