postgresql - 如何在没有 nativeQuery 标志的@Query 注释中将 string_agg 与 PostgreSQL 一起使用？

Question

我需要从 @Query 注释中删除 nativaQuery 标志。
表结构以后可能会发生变化，没有nativeQuery的代码会更容易后期维护。
我有一个类Parent链接到类 Child使用@ManyToMany 注释。
类(class)Child有一个字段 pseudonym那是一个 String 类型的值。
查询的结果需要按类 Child 中的 String 值排序。 ，我必须对其进行排序，然后将其连接为一个 String 值。
如果我不在 string_agg 函数中添加额外的排序，则 @Query 注释中没有 nativeQuery 标志的查询有效:order by string_agg(c.pseudonym, ',')如果我如下添加额外的必需排序，则会发生异常

order by string_agg(c.pseudonym, ',' order by c.pseudonym)

org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found 'order' near line 1, column ...

@Entity
@Getter
@Setter
@Table(name = "parent")
public class Parent {

    @Id
    private Long id;

    private String name;

    @ManyToMany
    @JoinTable(
            name = "parent_child_link",
            joinColumns = {@JoinColumn(name = "parent_id")},
            inverseJoinColumns = {@JoinColumn(name = "child_id")}
    )
    @OrderBy("pseudonym ASC")
    private List<Child> childs = new ArrayList<>();

}

@Entity
@Getter
@Setter
@Table(name = "child")
public class Child {

    @Id
    private Long id;

    private String pseudonym;

    @ManyToMany(mappedBy = "childs")
    private Set<Parent> parents = new HashSet<>();

}

public interface ParentRepository extends JpaRepository<Parent, Long> {

        @Query(nativeQuery = true, value =
            "select p.*" +
                    " from parent p" +
                    " left join parent_child_link link on p.id = link.parent_id" +
                    " left join child c on link.child_id = c.id" +
                    " where p.name = :name" +
                    " group by (p.id)" +
                    " order by string_agg(c.pseudonym, ',' order by c.pseudonym)")
    Page<Parent> find(@Param("name") String name, Pageable pageable);

}

Answer 1

请尝试使用嵌套查询:

select p.*
from (
    select p, string_agg(c.pseudonym, ',' order by c.pseudonym) ord
    from parent p
    left join parent_child_link link on p.id = link.parent_id
    left join child c on link.child_id = c.id
    where p.name = :name
    group by (p.id)
) inn(p, ord)
order by ord

或者:

select p.*
from(
    select p, c.pseudonym
    from parent p
    left join parent_child_link link on p.id = link.parent_id
    left join child c on link.child_id = c.id
    where p.name = :name
    order by p, pseudonym
) inn(p, pseudonym)
group by p.id
order by string_agg(pseudonym, ',')