Hi guys I have a similar problem exposed by Paul. Anyone have an answer to this?
---------- Forwarded message ---------- From: Paul Escobar <[email protected]> Date: 2016-08-11 15:58 GMT-05:00 Subject: N+1 problem in restful object viewer To: [email protected] 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 -- [image: Logo] José Alejandro Manaure *Project Manager* Tel: (57) 1 703 17 77 Cel: (57) 312 5476188 E-mail: [email protected] Calle 93 # 19b - 66 Ofc 202 Bogotá D.C., Colombia www.ticxar.com [image: facebook] <https://www.facebook.com/pages/Ticxar/446503822192581> [image: twitter] <http://twitter.com/ticxar> [image: linkedIn] <https://www.linkedin.com/company/ticxar>
