One-to-many queries join the same tables twice

[molexx]

The StarwarsQueryExecutorTests.queryOneToManyJoinByID test looks like this:

query { 
  Humans(where:{id:{EQ: "1000"}}) {
    select {
      name, homePlanet, friends { name } 
    }
  }
}

and executes the following SQL:

    select
        human0_.id as id2_7_0_,
        character2_.id as id2_7_1_,
        character4_.id as id2_7_2_,
        human0_.name as name3_7_0_,
        human0_.favorite_droid_id as favorite6_7_0_,
        human0_.gender_code_id as gender_c7_7_0_,
        human0_.home_planet as home_pla4_7_0_,
        character2_.name as name3_7_1_,
        character2_.primary_function as primary_5_7_1_,
        character2_.favorite_droid_id as favorite6_7_1_,
        character2_.gender_code_id as gender_c7_7_1_,
        character2_.home_planet as home_pla4_7_1_,
        character2_.dtype as dtype1_7_1_,
        friends1_.source_id as source_i1_9_0__,
        friends1_.friend_id as friend_i2_9_0__,
        character4_.name as name3_7_2_,
        character4_.primary_function as primary_5_7_2_,
        character4_.favorite_droid_id as favorite6_7_2_,
        character4_.gender_code_id as gender_c7_7_2_,
        character4_.home_planet as home_pla4_7_2_,
        character4_.dtype as dtype1_7_2_,
        friends3_.source_id as source_i1_9_1__,
        friends3_.friend_id as friend_i2_9_1__ 
    from
        character human0_ 
    left outer join
        character_friends friends1_ 
            on human0_.id=friends1_.source_id 
    left outer join
        character character2_ 
            on friends1_.friend_id=character2_.id 
    left outer join
        character_friends friends3_ 
            on human0_.id=friends3_.source_id 
    left outer join
        character character4_ 
            on friends3_.friend_id=character4_.id 
    where
        human0_.dtype='Human' 
        and human0_.id=? 
    order by
        human0_.id asc,
        character2_.name asc,
        character4_.name asc

It seems that the joins to friends3_ and character4_ duplicate the joins to friends1_ and character2_ and are unnecessary?

This is tested using commit 0151eec from master which includes the fix for #198 .

Removing the where id=1000 clause makes no difference.

[igdianov]

I know about this. The query builder actually creates a single join fetch, but Hibernate adds another fetch for many to many associations. It does not happen for one to many associations. I will investigate it further to double check.

[molexx]

Thanks.

I'm investigating adding hooks to allow user-defined entity-specific predicates to be added when an entity is added to the query. In the example above it seems that

graphql-jpa-query/graphql-jpa-query-schema/src/main/java/com/introproventures/graphql/jpa/query/schema/impl/QraphQLJpaBaseDataFetcher.java

Line 233 in 5d99c3b

fetch = reuseFetch(from, selectedField.getName(), isOptional);

adds the join aliased character2_ but the data in the GraphQL response is coming from character4_.name3_7_2_ where character4_ seems to be the mysteriously-generated join that I can't see where to add predicates to.

[igdianov]

@molexx This is the JPQL query generated before execution at

graphql-jpa-query/graphql-jpa-query-schema/src/main/java/com/introproventures/graphql/jpa/query/schema/impl/GraphQLJpaQueryDataFetcher.java

Line 139 in 0151eec

List<?> resultList = query.getResultList();

select distinct human from Human as human left join fetch human.friends as generatedAlias0 where human.id=:param0 order by human.id asc

There is no extra fetch join generated by the library. The Hibernate adds this extra join for some reason only if it is a many-to-many association. I remember I have spent considerable amount of time trying to debug Hibernate code to understand the logic behind this, but I was not successful since I ran out of time. It looks like a feature in Hibernate.

There is more, the Hibernate also executes two more queries for CodeList entity with parent-child relationship hierarchy which is part of Human. It should not do that because all of the relationships are marked with fetch=FetchType.LAZY, but it does...

What are you trying to do with custom predicates? It should be easy to add support for a hook registry to be able to mutate query search criterias by adding custom predicates.

[molexx]

Thanks very much, will take a look!

Custom predicates is a generic solution to restricting rows that are returned using server-side rules, defined per-entity. My immediate use-case is by relation to the currently logged in user but let's design a generic system for others.

I'm working on a PR for this with a bunch of tests, I should be able to continue with the one-to-many ones now.

I'm adding a call to a few places in getFieldPredicates() which looks for an annotated method on the current Entity which returns predicates to be added.

[molexx]

RE the Hibernate CodeList queries ignoring the Lazy, I wonder if that is similar to #114 , which I think is because of this Hibernate bug: https://hibernate.atlassian.net/browse/HHH-8776

[igdianov]

RE the Hibernate CodeList queries ignoring the Lazy, I wonder if that is similar to #114 , which I think is because of this Hibernate bug: https://hibernate.atlassian.net/browse/HHH-8776

There is not much can be done about it at the moment. I have observed that CodeList hierarchy get loaded when the entity manager is cold, i.e. during the first query. After that, there are no extra queries generated for the second query, so it looks like it is cached by Hibernate.

[igdianov]

I'm working on a PR for this with a bunch of tests, I should be able to continue with the one-to-many ones now.

Would you be able to start a new issue that describes the use case in detail? I have some ideas how to make it pluggable with annotation processing that use SpEL similar to https://spring.io/blog/2014/07/15/spel-support-in-spring-data-jpa-query-definitions