Elide is not limiting the columns based on GraphQL selection set

[chirdeeptomar]

I have a JPA model with n number of fields but when querying via Graphql I am only interested in some of the fields not all based on the client query. Elide is not considering the graphql selection set and querying all the fields of the model instead.

Expected Behavior

I would expect Elide to create select with specific columns not a select * from.

Current Behavior

SELECT * FROM table;

Steps to Reproduce (for bugs)

query all{    
  employee { 
    pageInfo{
      totalRecords     
    }
    edges {
      node {
        id
        department
         
      }
    }
  }
}

2. Hibernate: select employee0_.id as id1_1_, employee0_.department as departme2_1_, employee0_.email as email3_1_, employee0_.name as name4_1_ from employees employee0_ where employee0_.department in (?) limit ?

Your Environment

• Elide version used: 6.1.4
• Environment name and version (Java 1.8.0_152): Adopt Open Java: 11
• Operating System and version: Windows 10

[aklish]

The JPA DataStore in Elide use JPQL to query the database. While JPQL does let you query the individual fields of a table, it returns the individual field objects rather than hydrate the model itself.

It is important to let the ORM hydrate the models because for performance (N+1 problem), you want to get back proxies to the objects rather than the objects themselves.

In practice, it seems unlikely querying all the attributes would cause performance issues unless some of them are large (BLOB, CLOB) etc. JPA does have a mechanism to lazily fetch those attributes (they would only be queried if referenced in code):

https://vladmihalcea.com/the-best-way-to-lazy-load-entity-attributes-using-jpa-and-hibernate/

I have not tested this to make sure Elide doesn't reference the fields that were not requested (in which case the ORM would fetch them). However, this seems like the way to go for a data store like the JPA Store.

Other Elide stores (Aggregation Store) where we can better control the generated SQL only fetch what was asked for. Hope that helps.

[chirdeeptomar]

Wouldn't just eager loading be a better approach with Hibernate than lazy loading as you already know what the query is and user definietly wants those nested fields back? Apologies, if thats a silly question, I don't fully understand Elide's architecture.

[aklish]

Hibernate is eager by default. There are a few different ways to query the database with Hibernate (JPQL - which is what Elide uses. We can't specify individual fields here. It is more or less all or nothing to my knowledge). There is a criteria API which may support specific fields but lacks other features that make it a non-starter. The third way is to annotate your models for what fields should always be fetched and which fields should be lazily fetched (on access only).

[ts-ram]

You will to strike a balance between if only basic attributes are being fetched / transient attributes are being fetched. transients can potentially refer to attributes which you are not explicitly fetching. we do something similar in one of our proprietary framework to see if transients are there, then resort to entity and if transients are not there then use scalar as part of the projection.. @aklish projection in jpql would allow individual attributes to be specified, but the above needs to considered with regards to transient... https://thorben-janssen.com/jpql/

[chirdeeptomar]

@aklish Hibernate is lazy by default. JPQL supports selection of given fields as per https://thorben-janssen.com/jpql/ (please check Scalar Value section).

Sending all columns from the database to API is not performant specially when your tables are quite wide, costing unnecssary network traffic and costing money when running on the cloud. Elide's JSON-API and GraphQL are already ommiting the unnecessary fields, it just the persistence layer that needs tweaking.

[aklish]

When using JPQL to select scalar fields, the result is just that - scalar fields. Elide needs the ORM to hydrate a hibernate proxy that wraps the entity. The proxy allows subsequent SQL calls when property functions (getters and setters) are called on the object.

The problem is that JPQL is limited when querying entities (as opposed to scalars). The reason the proxies are critical is that Hibernate can do all kinds of clever things when fetching relationships (see https://elide.io/pages/guide/v6/16-performance.html for more information on N+1 problem).

I don't see a good way to coerce Hibernate to fetch selective fields when a proxy is needed. That said, if someone can point to the mechanism, would be happy to see support for this in Elide. I agree it is more performant.