I need to make this Query in criteria Builder. At this momento I�´m making it like this:
Root<Setting> settingRoot = cr.from(Setting.class); Join<Setting, SettingValue> settingValueRoot = settingRoot.join("SettingValues", JoinType.LEFT); if (filter.settingLevel == SettingData.SETTING_LEVEL_USER) { Join<SettingValue, User> userRoot = settingValueRoot.join("User", JoinType.LEFT); setCriteriaQuery(cb, cr, filter, settingRoot, settingValueRoot, userRoot);
And the query I get is the next one:
select setting0_.code as col_0_0_, setting0_.setting_name as col_1_0_, setting0_.setting_level as col_2_0_, settingsgr3_.setting_group_name as col_3_0_, setting0_.setting_order as col_4_0_, settingval1_.setting_value as col_5_0_, user2_.name as col_6_0_, user2_.id as col_7_0_, settingsgr3_.group_order as col_8_0_, setting0_.settings_group_id as col_9_0_, setting0_.data_type as col_10_0_, setting0_.multiple as col_11_0_, setting0_.edit_mode as col_12_0_, setting0_.super_edit_mode as col_13_0_, setting0_.action_required as col_14_0_, setting0_.setting_configuration as col_15_0_, settingdef5_.default_value as col_16_0_ from currentds.icm_setting setting0_ left outer join currentds.icm_setting_value settingval1_ on setting0_.code=settingval1_.setting_code left outer join currentds.icm_user user2_ on settingval1_.user_id=user2_.id, currentds.icm_settings_group settingsgr3_, currentds.icm_setting_default_value settingdef5_ where setting0_.settings_group_id=settingsgr3_.id and setting0_.code=settingdef5_.setting_code and setting0_.setting_level=1 and (settingval1_.user_id=1 or settingval1_.user_id is null)
But the query I need is this
SELECT icm_user.id,* FROM ICM_setting LEFT JOIN ( icm_setting_value INNER JOIN icm_user ON icm_setting_value.user_id = icm_user.id ) ON ICM_setting.code = icm_setting_value.setting_code AND icm_setting_value.user_ID=1 WHERE ICM_SETTING.SETTING_LEVEL=1
Any help?
Ads