I want to use String_agg() postrges DB function with order By Clause using Criterial APIs

Question

I have a postgres database with two tables: Film and Actor. Each Film can have multiple Actors and each actor can work in multiple films(i.e MANY to MANY relationship),

I want return a list of actor’s names for each film from the film table. Also actors First name should be present in ascending order in the list

I am able to write native query for that like below

SELECT
    f.title,
    STRING_AGG (
    a.first_name,
        ','
       ORDER BY
        a.first_name,
    ) actor
FROM
    film f
INNER JOIN film_actor fa USING (film_id)
INNER JOIN actor a USING (actor_id)
GROUP BY
    f.title;

Below are my sample entities

@Entity
class Film{

@Id
int film_id;

String title;

@ManyToMany(targetEntity = Actor.class,)
    @JoinTable(name = "film_actor"
            joinColumns = { @JoinColumn(name = "film_id") }, inverseJoinColumns = { @JoinColumn(name = "actor_id") })
List<Actors> actors

}


@Entity
class Actors{

@Id
int actor_id;

String first_name;

String last_name;


}

I tried to write the query Criteria Builder like below

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery();

        Root<Film> filmRoot = criteriaQuery.from(Film.class);
        Join<Film, Actor> filmActorJoin = filmRoot.join("actors", JoinType.LEFT);

        Expression<String> filmActorExpression = criteriaBuilder.function("string_agg", String.class,
                filmActorJoin.get("first_name"), criteriaBuilder.literal(", ") 
                
        );
        
        criteriaQuery.groupBy(filmRoot.get("film_id")).orderBy(criteriaBuilder.asc(filmActorExpression));
        

But above criteria query is not aggregating the strings of first_name in ASC order . Can anyone help me on this

Answer 1

In Hibernate 6, the HibernateCriteriaBuilder interface declares the listagg() function. Of course, you'll need to typecast to get it, i.e. (HibernateCriteriaBuilder) entityManager.getCriteriaBuilder().

https://docs.jboss.org/hibernate/orm/6.2/javadocs/org/hibernate/query/criteria/HibernateCriteriaBuilder.html#listagg(org.hibernate.query.criteria.JpaOrder,jakarta.persistence.criteria.Expression,jakarta.persistence.criteria.Expression)

Note that you can find a list of the supported aggregate functions here:

https://docs.jboss.org/hibernate/orm/6.2/userguide/html_single/Hibernate_User_Guide.html#hql-aggregate-functions-orderedset

Answer 2

If you are able to define new functions in DB, I would think about something like:

PostgreSQL part:

CREATE OR REPLACE FUNCTION sort_asc(ar text[]) returns text[]
AS
$$
SELECT array((SELECT unnest(ar) ORDER BY 1))
$$
    LANGUAGE SQL
    STABLE;

Hibernate part:

cb.function("array_to_string", Object.class,
  cb.function("sort_asc", Object.class,
    cb.function("array_agg", Object.class,
      filmActorJoin.get("first_name")
    )
  ),
  cb.literal(", ")
).as(String.class)

Another "option" (from my perspective that looks more like trick), is to register sql function in Hibernate, in that case the solution would look like:

public class StringAggFunction extends SQLFunctionTemplate {

    public static final String FUNCTION_NAME = "string_agg";

    public StringAggFunction() {
        super(StringType.INSTANCE, "string_agg(?1, ?2 order by ?3)");
    }

}

cb.function("string_agg", Object.class,
        filmActorJoin.get("first_name"),
        cb.literal(", "),
        filmActorJoin.get("first_name")
).as(String.class)