在没有nativeQuery标志的@Query注释中，如何将string\u agg与PostgreSQL一起使用？

Question

我需要从@Query注释中删除nativaQuery标志
表结构将来可能会发生变化，没有nativeQuery的代码将更容易在以后进行维护。

我有一个类父，它使用@ManyTo多国注释链接到类儿童。类儿童有一个字段假名，它是字符串类型的值。

查询的结果需要按classChild中的字符串值排序，我必须对其进行排序，然后将其连接成一个字符串值。

如果我不在string\u agg函数中添加额外的排序，则@query注释中没有nativeQuery标志的查询可以工作：
order by string\u agg（c.pseudoname，，'）

如果我添加额外的所需排序如下，发生异常

• order bystring_agg（c.pseudonym , ',' order byc.pseudonym）
• org.hibernate.hql.internal.ast.查询语法异常：期待关闭，在第1行，列附近找到'order'...

@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, ',')