Access to a related entity in a query with a mapped view

Hello, I’m trying to use a query to which I assign a view. In this view I have fields that are related entities (Many to one), how I can access these entities without using joins and also filter and sort by them ?. I have understood that when using a view which included these entities are related then there is no need to use joins, but if no joins How do I access these fields ?.
example:

List result = null;
try (Transaction tx = persistence.createTransaction ()) {
EntityManager em = persistence.getEntityManager ();
TypedQuery query = em.createQuery (
“Select f connectoctopus from $ FicheroFtp Where grupoFtps.descripcion = : descripcionGrupo order by grupFtps” FicheroFtp.class) .setViewName ( “ficheroFtpConGrupoConNodo-view”);
query.setParameter ( “descripcionGrupo” descripcionGrupo);
result = query.getResultList ();
tx.commit ();
}

  1. grupoFtps is a related entity (Many to One)
  2. The view ficheroFtpConGrupoConNodo-view has including the field grupoFtps
    Greetings. and thanks.

Something like this:


select f from namespace$FicheroFtp f
where f.grupoFtps.descripcion = :descripcionGrupo 
order by f.grupFtps.descripcion

And you don’t have to specify a view for this. A view defines what object graph should be fetched in the result list, it does not affect filtering and ordering on the database level.

Hello, I tried it before writing but does not return anything. Perhaps I did not explain well:
I have a “FicheroFtp” entity that has a related entity “grupoFtps” in the form Many to One, also have another related entity “ftpconf” in the form Many to One, after making the request call a service with result list returned. In the attempt to access service entities related as follows:
void tratar (List ficheroftp) {
GrupoFtps elGrupo = ficheroftp[0].getGrupoFtps ();
FtpConf laConfiguracion = ficheroftp[0] .getFtpconf ();

By accessing these entities throws an exception saying that ficheroftp this detached, for this reason I wanted to use the view created with the graph in the query, of these entities to access them from another service, but I want the query to filter and sort by these entities related and had read that applying the view with the graph was not necessary to establish the joins.
Thank you

If you specify a correct view for query selecting FicheroFtp, the related entities will be fetched together with FicheroFtp and accessing them will not throw exceptions.

Please provide your code, view and exceptions stack trace. The query in your first code fragment seems completely wrong.

It is true, the code is wrong intentionally to show my idea. Using this code is:

@Override
    public void inicio(String descripcionGrupo) {
        List<FicheroFtp> resultado = null;
        try (Transaction tx = persistence.createTransaction()) {
            EntityManager em = persistence.getEntityManager();
            TypedQuery<FicheroFtp> query = em.createQuery(
                    "select f from connectoctopus$FicheroFtp f join f.grupoFtps g where g.descripcion = :descripcionGrupo", FicheroFtp.class).setViewName("ficheroFtpConGrupoConNodo-view");
            query.setParameter("descripcionGrupo", descripcionGrupo);
            resultado = query.getResultList();
            tx.commit();
        }
        if (resultado != null){
            procesarComunicacionFtp.inicio(resultado, "Prueba");
        }
    }

With this query I get FicheroFtp entities and their relationships (the whole graph) and when I get the results of this consultation and then sent them to a service within the service agree to entities perfectly.
The idea that I wanted to try is to attach a view to the query to retrieve me the graph of whole objects (this is done right), without the need to specify joins in the same query (as to why I use the view) I have read this in another thread, but not how to filter or sort the query by related entities without specifying the joins because when I do I throw an error that does not recognize the related entity.

OK, but I’m sure your code should work without joins in the query:


select f from connectoctopus$FicheroFtp f where f.grupoFtps.descripcion = :descripcionGrupo

if you have specified the related attributes in the view.
Could you check this?

JPQLException:
Exception Description: Problem compiling [select f from connectoctopus$FicheroFtp f where f.grupoFtps.descripcion = :descripcionGrupo].
[48, 71] The state field path ‘f.grupoFtps.descripcion’ cannot be resolved to a valid type.
I’ll keep trying and trying in different ways.
Greetings.

Oh, I see. Perhaps your grupoFtps is a collection (OneToMany).
Then to filter by an attribute of the collection you really have to specify JOIN in your query.