我需要从数据库中选择一些实体，并将其翻译为特定语言
这是我的数据库模式

这是我试图生成的查询

SELECT identifier,
       (SELECT tv.value FROM translations_values AS tv WHERE tv.language = :lang AND tv.key = title) AS title_translated,
       (SELECT tv.value FROM translations_values AS tv WHERE tv.language = :lang AND tv.key = content) AS content_translated
  FROM posts

这是我的Map实体

@Entity
@Data
@Table(title = "posts")
@NamedQuery(title = "Post.findAll", query = "SELECT i FROM Post i")
public class Post implements Serializable {

    private static final long serialVersionUID = 1L;

    @Column(title = "identifier")
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer identifier;

    @Column(title = "title", insertable = false, updatable = false)
    private Integer titleIdentifier;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(title = "title")
    public TranslationKey title;

    @Formula(value = "(SELECT tv.value FROM translations_values AS tv WHERE tv.language = {lang} AND tv.key = title)")
    public String titleTranslated;

    @Column(title = "content", insertable = false, updatable = false)
    private Integer contentIdentifier;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(title = "content")
    private TranslationKey content;
    
    @Formula(value = "(SELECT tv.value FROM translations_values AS tv WHERE tv.language = {lang} AND tv.key = content)")
    public String contentTranslated;

    // Getter & Setter

}

这个过程的最后一步是Hibernate Interceptor，它用请求的语言id替换{lang}。@Formula允许我创建where条件，这样我就可以按标题和内容搜索帖子。我还可以在存储库中创建“findByTitleContaining”之类的方法，Hibernate会自动创建正确的where条件。
我的问题是有没有更好的方法来做这件事？我试图在EAGER模式下获取翻译，但我遇到了SELECT N+1问题