How to use string_agg with PostgreSQL in @Query annotation without nativeQuery flag?

Question

I need to remove the nativeQuery flag from the @Query annotation.
The table structure may change in the future, and code without nativeQuery will make it easier to maintain later.

I have a class Parent that is linked to class Child with the @ManyToMany annotation. Class Child has a field pseudonym that is a value of the String type.

The result of the query needs to be sorted by the String value from class Child, which I have to sort and then concatenate into one String value.

The query without the nativeQuery flag in the @Query annotation works if I do not add an additional sort in the string_agg function:
order by string_agg(c.pseudonym, ',')

If I add additional required sorting as below, an exception occurs

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);

}

If you want to use native functionalities which are not supported by HQL/JPQL, you'll have to write native queries. If you would want to limit the result set, you would also end up with native query as LIMIT is not supported in HQL/JPQL. — Stefan Golubović
– Stefan Golubović, Commented Jun 14, 2021 at 19:53
The current native query visible in the @Query annotation works fine, and in the console (using the p6spy tool) at the end of the query you can see an added entry limiting the number of results ... group by (p.id) order by string_agg (c.pseudonym, ',' order by c.pseudonym) limit 20. — Grzegorz Kawalec
– Grzegorz Kawalec, Commented Jun 15, 2021 at 4:43

Andronicus · Accepted Answer · 2021-06-20 14:39:48Z

1

Please try with nested query:

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

or:

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

edited Jun 20, 2021 at 14:39

answered Jun 20, 2021 at 14:05

Andronicus

26.2k18 gold badges55 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Grzegorz Kawalec Over a year ago

The above queries will not help to remove the nativeQuery flag from the @Query annotation.

Andronicus Over a year ago

@GrzegorzKawalec of course it won't... You're using postgres specific functionality...

Collectives™ on Stack Overflow

How to use string_agg with PostgreSQL in @Query annotation without nativeQuery flag?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related