I'm a little bit stuck with a problem.
There is a Setting table holding specified settings and a UserSetting that holds the value for each setting and user.
public class Setting{
@Column(name = "setting_identifier")
private String identifier;
@Column(name = "setting_default_value")
private String defaultValue;
@OneToMany(mappedBy = "setting", fetch = FetchType.LAZY)
private Collection<UserSetting> userSettings = new ArrayList<>();
}
public class UserSetting {
@Column(name = "setting_value")
private String value;
@Delegate
@ManyToOne(fetch = FetchType.EAGER)
private Setting setting;
}
I want to load all user settings, which are basically led by all settings entries and optionally joined with everything the user already has in his UserSettings entries.
Using JPA with Panache I get something like this, but then I have the problem of not being able to return both tables s and us
public Collection<UserSetting> userSettings(User user) {
return find("select ... from Setting s left join UserSetting us on s.id = us.setting.id and us.authUser = :user",
Parameters.with("user", user)).list();
}
What do I have to do to bring the joined tables into the form of UserSetting objects with their related Setting object?
I've solved it using a custom constructor within my UserSetting object, that initializes the Settings object together with it.
public UserSetting(String identifier, LocalDateTime createdAt, LocalDateTime updatedAt, String value, Long settingsId) {
this.value = value;
this.setSetting(Setting.of(settingsId));
this.setCreatedAt(createdAt);
this.setUpdatedAt(updatedAt);
this.setIdentifier(identifier);
}
and modified the query to reflect the signature of the constructor
public Collection<UserSetting> userSettings(User user) {
return find("select s.identifier as identifier,
us.createdAt as createdAt, us.updatedAt as updatedAt,
coalesce(us.value, s.defaultValue) as value,
s.id as settingsId from Setting s
left join UserSetting us
on s.id = us.setting.id
and us.user.id = :user",
Parameters.with("user", user.id))
.project(UserSetting.class)
.list();
}