title: HQL query and SELECT N+1 issue when using IN
body: <p>I have some "SELECT n+1" issues with an HSQL query using Hibernate and Spring Data using the IN clause.</p>
  
  <p>We have some organizations that published applications thought a PublishedApplication object.
  The current model is (and it cannot be changed easily):</p>
  
  <pre><code>public class Application {
      Organization organization;
      ...
  }
  
  public class Organization {
      ...
  }
  
  
  public class PublishedApplication extends ... {
  
      @OneToOne(cascade = CascadeType.ALL, orphanRemoval = true)
      private Application application;
  
      @ManyToOne(cascade = CascadeType.REFRESH)
      @JoinColumn(name="publishingorganization_uuid")
      protected Organization publishingOrganization;
  }
  </code></pre>
  
  <p>I want to list all apps that are published by one of my organizations.</p>
  
  <p>So I'v write this query:</p>
  
  <pre><code>@Query(value = "SELECT DISTINCT p.application 
                  FROM PublishedApplication p 
                  WHERE p.publishingOrganization IN ?1")
   List&lt;Application&gt; findAllByPublishedOrganizationIn(List&lt;Organization&gt; organizations);
  </code></pre>
  
  <p>This works well but when I enable SQL log, I see:</p>
  
  <ul>
  <li>1 SELECT to get "publishedapplication.application": ok it's the main request.</li>
  <li>3 SELECT to get "organization": I have access to 10 organizations but only 3 have published an application</li>
  </ul>
  
  <p>Here is the code call:</p>
  
  <pre><code>log.debug("Before");
  List&lt;Application&gt; applications = applicationRepository.findAllByPublishedOrganizationIn(organizations);
  log.debug("After");
  </code></pre>
  
  <p>And here is the Log:</p>
  
  <pre><code>ApplicationService       : Before
  
  Hibernate: select distinct applicatio1_.uuid as uuid1_0_, applicatio1_.version as version2_0_, applicatio1_.description as descript3_0_, applicatio1_.lastversiondate as lastvers4_0_, applicatio1_.name as name5_0_, applicatio1_.organization_uuid as organiza6_0_ from publishedapplication publisheda0_ inner join applications applicatio1_ on publisheda0_.application_uuid=applicatio1_.uuid where publisheda0_.publishingorganization_uuid in (? , ? , ? , ? , ? , ? , ? , ? , ? , ?)
  Hibernate: select organizati0_.uuid as uuid1_8_0_, organizati0_.version as version2_8_0_, organizati0_.description as descript3_8_0_, organizati0_.name as name4_8_0_ from organizations organizati0_ where organizati0_.uuid=?
  Hibernate: select organizati0_.uuid as uuid1_8_0_, organizati0_.version as version2_8_0_, organizati0_.description as descript3_8_0_, organizati0_.name as name4_8_0_ from organizations organizati0_ where organizati0_.uuid=?
  Hibernate: select organizati0_.uuid as uuid1_8_0_, organizati0_.version as version2_8_0_, organizati0_.description as descript3_8_0_, organizati0_.name as name4_8_0_ from organizations organizati0_ where organizati0_.uuid=?
  
  ApplicationService       : After
  </code></pre>
  
  <p>I suppose that the 3 'SELECT organization' are triggered by doing this "p.publishingOrganization IN".
  Is it a way to resolve this problem using a single SELECT?
  Something like this in SQL:</p>
  
  <pre><code>List&lt;Organization&gt; orgs = ...
  List&lt;String&gt; orgsIds = ...
  doSQL("SELECT * 
         FROM publishedApplication p 
         WHERE p.publishingOrganizationId IN (" + orgsIds.join(",") + ")");
  </code></pre>
  
  <p>Thanks</p>
  
  <p>EDIT: I resolve this using:</p>
  
  <pre><code>SELECT DISTINCT p.application FROM PublishedApplication p JOIN FETCH p.application.organization WHERE p.publishingOrganization IN ?1
  </code></pre>