Hi Everyone,
I have a N+1 query problem using a method in a Domain Service. The method
(invoked from postman):
*public List<Solicitud> listarSolicitudesTramite(final
@ParameterLayout(named="Tramite") String tramite) {*
* PersistenceManager persistenceManager =
isisJdoSupport.getJdoPersistenceManager();*
* persistenceManager.getFetchPlan().addGroup("sucursales");*
* final Query query = persistenceManager.newQuery(Solicitud.class);*
* query.setFilter( inTramites( tramite ) );*
* query.setOrdering("fechaCierre DESC");*
* List<Solicitud> solicitudesFiltradas = (List<Solicitud>) query.execute();*
* return solicitudesFiltradas;*
* }*
The Solicitud Class:
*@FetchGroup(name="sucursales", members={@Persistent(name="sucursal")})*
*@DatastoreIdentity(strategy=IdGeneratorStrategy.IDENTITY,column="idSucursal")*
*public class Solicitud implements Comparable<Solicitud> {*
*.*
*.*
*.*
* private Sucursal sucursal;*
* private NivelEscalamiento nivelEscalamiento;*
* private Compania compania;*
* private Canal canal;*
*...*
*}*
The line: List<Solicitud> solicitudesFiltradas = (List<Solicitud>)
query.execute(); execute this query:
*SELECT *
*DISTINCT 'domainapp.dom.mesaAyudaPrestadores.Solicitud' AS NUCLEUS_TYPE,*
*A0.correoContacto,*
*A0.descripcion,*
*A0.extension,*
*A0.fechaCierre,*
*A0.fechaCreacion,*
*A0.fechaInicioCreacion,*
*A0.nombreBeneficiario,*
*A0.nombreSolicitante,*
*A0.numDocBeneficiario,*
*A0.numDocSolicitante,*
*C0.descSucursal,*
*C0.idSucursal,*
*A0.telefonoContacto,*
*A0.usuario,*
*A0.usuarioAsignado*
*,A0.idSolicitud,*
*A0.fechaCierre AS NUCORDER0 *
*FROM TBL_SOLICITUD A0 *
*LEFT OUTER JOIN TBL_SUCURSAL B0 ON A0.sucursal_idSucursal_OID =
B0.idSucursal *
*LEFT OUTER JOIN TBL_SUCURSAL C0 ON A0.sucursal_idSucursal_OID =
C0.idSucursal *
*WHERE *
*B0.descSucursal = 'Principal Antioquia' ORDER BY NUCORDER0 DESC*
It is fine!, because I need just Solicitud data plus Sucursal data (just
this one related entity, not the other relationships), but after method
return this other queries are run to fill the another relationships (N+1
problem):
*SELECT B0.descNivel,B0.idNivelEscalamiento FROM TBL_SOLICITUD A0 LEFT
OUTER JOIN TBL_NIVEL_ESCALAMIENTO B0 ON
A0.nivelEscalamiento_idNivelEscalamiento_OID = B0.idNivelEscalamiento WHERE
A0.idSolicitud = <1>*
*SELECT B0.descCanal,B0.idCanal FROM TBL_SOLICITUD A0 LEFT OUTER JOIN
TBL_CANAL B0 ON A0.canal_idCanal_OID = B0.idCanal WHERE A0.idSolicitud =
<1>*
*SELECT B0.descCompania,B0.idCompania FROM TBL_SOLICITUD A0 LEFT OUTER JOIN
TBL_COMPANIA B0 ON A0.compania_idCompania_OID = B0.idCompania WHERE*
* A0.idSolicitud = <1>*
How can I avoid this N+1 problem? I thought that using FetchPlan and Groups
was enough to do it.
PD: isis.viewer.restfulobjects.honorUiHints is false and no
x-ro-follow-links query parameter is used.
Thanks,
--
Paul Escobar Mossos
skype: paulescom
telefono: +57 1 3006815404
