Hi Paul,
sorry for the delay in replying (have been on vacation).
What's happening here is that the default representation of the Solicitud
class:
public class Solicitud implements Comparable<Solicitud> {
...
private Sucursal sucursal;
private NivelEscalamiento nivelEscalamiento;
private Compania compania;
private Canal canal;
}
needs to resolve all four of those properties, because it returns the title
of each of those associated objects. Your initial query results in both
the Solicitud entity and its associated Sucursal entity to be obtained in a
single query, but for the remaining 3 properties (NivelEscalamiento,
Compania and Canal) the act of generating the representation means that DN
will load those properties via separate queries.
To fix this, you have some choices:
1. live with it
2. extend your initial query to also eagerly load those additional
properties
3. create a view model [1] that more precisely returns just the data that
you need, and return this instead.
4. consider using the simplified representation [2] provided by the RO
viewer
5. provide a custom ContentMappingService [3] and your own media types to
take control of how the representations are generated
6. provide a custom ContentNegotiatorService [4] or RepresentationService
[5] for even more control
HTH
Dan
[1]
http://isis.apache.org/guides/ugbtb.html#_ugbtb_view-models_use-cases_dtos
[2]
http://isis.apache.org/guides/ugvro.html#_ugvro_simplified-representations
[3]
http://isis.apache.org/guides/rgsvc.html#_rgsvc_spi_ContentMappingService
[4]
http://isis.apache.org/guides/rgfis.html#_rgfis_spi_ContentNegotiationService
[5]
http://isis.apache.org/guides/rgfis.html#_rgfis_spi_RepresentationService
On 11 August 2016 at 21:58, Paul Escobar <[email protected]>
wrote:
> 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
>
