title: Spring Data JPA - Join unrelated entities by defining projections
body: <p>I have two entities which are not related by foreign key.</p>
  
  <p><strong>ComplaintEntry</strong></p>
  
  <pre><code>@Entity
  @Table(name = "complaint_entry")
  public class ComplaintEntry implements Serializable {
  
      private static final long serialVersionUID = 1L;
  
      @Id
      @GeneratedValue
      private UUID id;
  
      @Column(name = "note")
      private String note;       
  
      @Enumerated(EnumType.STRING)
      @Column(name = "entry_type")
      private EntryType entryType;
  
      @Column(name = "entry_date")
      private ZonedDateTime entryDate;
  
      @Column(name = "internal_entry")
      private Boolean internalEntry;
  
      // getter/setter
  }
  </code></pre>
  
  <p><strong>Attachment</strong></p>
  
  <pre><code>@Entity
  @Table(name = "attachment")
  public class Attachment implements Serializable {
  
      private static final long serialVersionUID = 1L;
  
      @Id
      @GeneratedValue
      private UUID id;
  
      @Column(name = "name")
      private String name;
  
      @Column(name = "ref_id")
      private UUID refId;
  
      @Column(name = "url")
      private String url;
  
      // getter/setter
  }
  </code></pre>
  
  <p>I have created the Projection</p>
  
  <pre><code>public interface ComplaintEntryProjection {
  
      String getNote();
  
      EntryType getEntryType();
  
      ZonedDateTime getEntryDate();
  
      Boolean getInternalEntry();
  
      List&lt;Attachment&gt; getAttachments();
  }
  </code></pre>
  
  <p>and the Repository</p>
  
  <pre><code>@Repository
  public interface ComplaintEntryRepository extends JpaRepository&lt;ComplaintEntry,UUID&gt; {
  
      @Query("select complaintEntry, attachment from ComplaintEntry complaintEntry left join Attachment attachment " +
              "on complaintEntry.id = attachment.refId where complaintEntry.internalEntry = true")
      List&lt;ComplaintEntryProjection&gt; findAllByInternalEntryTrueForProjection();
  }
  </code></pre>
  
  <p>but when I execute my code it's not working as expected.</p>
  
  <p>the stacktrace of this is</p>
  
  <pre><code>org.springframework.dao.InvalidDataAccessApiUsageException: No aliases found in result tuple! Make sure your query defines aliases!; nested exception is java.lang.IllegalStateException: No aliases found in result tuple! Make sure your query defines aliases!
      at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:381)
      at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:246)
      at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:488)
      at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
      at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
      at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
      at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
      at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
      at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
      at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
      at com.sun.proxy.$Proxy167.findAllByInternalEntryTrueForProjection(Unknown Source)
      at com.yoursafehub.web.controller.TimelineController.getComplaintPage(TimelineController.java:71)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
      at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
      at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
      at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
      at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
      at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
      at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
      at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
      at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
      at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)
      at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
      at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:85)
      at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129)
      at com.codahale.metrics.servlet.AbstractInstrumentedFilter.doFilter(AbstractInstrumentedFilter.java:111)
      at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
      at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
      at org.springframework.boot.web.filter.ApplicationContextHeaderFilter.doFilterInternal(ApplicationContextHeaderFilter.java:55)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
      at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
      at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
      at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:96)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
      at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
      at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
      at org.springframework.boot.actuate.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:110)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
      at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
      at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
      at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
      at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
      at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
      at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
      at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
      at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
      at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
      at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
      at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
      at org.springframework.security.web.csrf.CsrfFilter.doFilterInternal(CsrfFilter.java:100)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
      at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:64)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
      at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
      at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
      at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
      at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
      at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
      at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
      at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
      at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
      at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
      at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
      at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
      at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
      at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:105)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
      at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
      at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
      at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
      at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
      at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
      at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
      at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
      at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
      at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)
      at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
      at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
      at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:131)
      at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)
      at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
      at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)
      at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)
      at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)
      at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)
      at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)
      at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
      at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
      at io.undertow.servlet.handlers.SessionRestoringHandler.handleRequest(SessionRestoringHandler.java:143)
      at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:292)
      at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:81)
      at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:138)
      at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:135)
      at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)
      at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
      at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:272)
      at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:81)
      at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:104)
      at io.undertow.server.Connectors.executeRootHandler(Connectors.java:211)
      at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:809)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
      at java.lang.Thread.run(Thread.java:745)
  Caused by: java.lang.IllegalStateException: No aliases found in result tuple! Make sure your query defines aliases!
      at org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter.convert(AbstractJpaQuery.java:282)
      at org.springframework.data.repository.query.ResultProcessor$ChainingConverter.convert(ResultProcessor.java:212)
      at org.springframework.data.repository.query.ResultProcessor$ChainingConverter$1.convert(ResultProcessor.java:200)
      at org.springframework.data.repository.query.ResultProcessor$ChainingConverter.convert(ResultProcessor.java:212)
      at org.springframework.data.repository.query.ResultProcessor.processResult(ResultProcessor.java:149)
      at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:121)
      at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106)
      at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:483)
      at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:461)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
      at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:56)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
      at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
      at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
      at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
      at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
      ... 123 common frames omitted
  </code></pre>
  
  <p>I want to fetch data of these two entity into a single projection.
  Is it the right way of doing or is there any other way.</p>
  
  <p><strong>Edit 1:</strong></p>
  
  <ol>
  <li>I don't want to define relationship between these entity(data should be match against id of ComplaintEntry and refId of Attachment).</li>
  <li>One ComplaintEntry can have many Attachment.</li>
  <li>I want a projection like it contains fields of ComplaintEntry and a list of Attachment (like I defined above).</li>
  </ol>
  
  <p><strong>Edit 2:</strong></p>
  
  <p>As @Cepr0 suggested in his <a href="https://stackoverflow.com/a/45344081/4387470">answer</a>, I have to set alias. After setting alias now there is no exception, but the result is not what I am expecting. 
  Now I updated the query to :</p>
  
  <pre><code>@Repository
  public interface ComplaintEntryRepository extends JpaRepository&lt;ComplaintEntry,UUID&gt; {
  
      @Query("select ce.id as id, ce.note as note, ce.entryDate as entryDate, ce.internalEntry as internalEntry, " +
          "a as attachments from ComplaintEntry ce left join Attachment a on ce.id = a.refId where ce.internalEntry = true")
      List&lt;ComplaintEntryProjection&gt; findAllByInternalEntryTrueForProjection();
  }
  </code></pre>
  
  <p>and the result of this is :</p>
  
  <pre><code>[ {
    "note" : "complaint entry one",
    "entryType" : "COMMENT",
    "entryDate" : "2017-07-30T13:26:34+05:30",
    "internalEntry" : true,
    "attachments" : [ {
      "id" : 1,
      "name" : "attachment one",
      "refId" : 1,
      "url" : "url1"
    } ]
  }, {
    "note" : "complaint entry one",
    "entryType" : "COMMENT",
    "entryDate" : "2017-07-30T13:26:34+05:30",
    "internalEntry" : true,
    "attachments" : [ {
      "id" : 2,
      "name" : "attachment two",
      "refId" : 1,
      "url" : "url2"
    } ]
  } ]
  </code></pre>
  
  <p>what I need is Single complaintEntry contain multiple attachment, something like this : </p>
  
  <pre><code>[ {
    "note" : "complaint entry one",
    "entryType" : "COMMENT",
    "entryDate" : "2017-07-30T13:26:34+05:30",
    "internalEntry" : true,
    "attachments" : [ {
      "id" : 1,
      "name" : "attachment one",
      "refId" : 1,
      "url" : "url1"
    },
    {
      "id" : 2,
      "name" : "attachment two",
      "refId" : 1,
      "url" : "url2"
    } ]
  } ]
  </code></pre>
  
  <p>Is it possible to fetch data like this.</p>