0

I'm trying to get my Usuarios (users) + the number of the item Ejemplar they have created from my postgreSQL db. I can do this no problem in PGAdmin, with this query:

SELECT u.id, tmp.c FROM comun.usuario u LEFT JOIN (SELECT cbdrs.ejemplar.id_creador, COUNT(*) AS c FROM cbdrs.ejemplar

GROUP BY cbdrs.ejemplar.id_creador) tmp ON tmp.id_creador = u.id

But, when I try to do the same in my Spring application, with this query:

SELECT new es.enxenio.cbdrs.controller.admin.custom.UsuarioEjemplaresDTO(u.id, tmp.c) FROM es.enxenio.cbdrs.model.usuario.Usuario u LEFT JOIN (SELECT e.id_creador, COUNT(*) AS c FROM es.enxenio.cbdrs.model.ejemplar.Ejemplar e GROUP BY e.id_creador) tmp ON tmp.id_creador = u.id WHERE u.locked = false

It fails as I try to run the application, with the following error:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 143 [SELECT new es.enxenio.cbdrs.controller.admin.custom.UsuarioEjemplaresDTO(u.id, tmp.c) FROM es.enxenio.cbdrs.model.usuario.Usuario u LEFT JOIN (SELECT e.id_creador, COUNT(*) AS c FROM es.enxenio.cbdrs.model.ejemplar.Ejemplar e GROUP BY e.id_creador) tmp ON tmp.id_creador = u.id WHERE u.locked = false]

That parentheses is the one here:

LEFT JOIN *(*SELECT

I don't understand why I'm getting this error. My DB is obviously not the problem cause I can execute the query in PGAdmin no problem, and I know for a fact my classes Usuario and Ejemplar are working cause I use them in other queries.

Does anybody have any idea how to fix this?

David Antelo
  • 503
  • 6
  • 19
  • 1
    HQL doesn't support subqueries in the `FROM` clause. If you need that, make it a *native* query. – Andreas Dec 26 '19 at 08:14
  • Thanks, gonna test it and report back – David Antelo Dec 26 '19 at 08:14
  • Unfortunately I can't use native queries cause I'm using pagination, I get this error: org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException: Cannot use native queries with dynamic sorting and/or pagination in method public abstract org.springframework.data.domain.Page es.enxenio.cbdrs.model.usuario.UsuarioRepository.findValidUsers(org.springframework.data.domain.Pageable) – David Antelo Dec 26 '19 at 08:20

0 Answers0