CODEWITHSUNDEEP
postgresqlhibernatejpaspring-data-jpa
Grzegorz Kawalec
Grzegorz Kawalec

Reputation: 325

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

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

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

}

Upvotes: 4

Views: 1290

Answers (1)

Andronicus
Andronicus

Reputation: 26066

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

Upvotes: 1

Related Questions