title: Using arbitrary query as projection in spring data rest project
body: <p>How it is possible to use arbitrary sql query (I mean native sql query) in some repository? My actual problem is this:</p>
  
  <pre><code>@Data //lombok thing
  @Entity
  public class A extends AuditModel {
    private long id;
    private String name;
  
    @OneToMany(mappedBy="a") //Comments.a is owning side of association, i.e. comments table does have column called a_id as foreign key
    @ToString.Exclude
    private Set&lt;Comments&gt; comments = new HashSet();
  
    @OneToMany(mappedBy="a") //SimpleFile.a is owning side of association
    private Set&lt;SimpleFile&gt; comments = new HashSet();
  }
  </code></pre>
  
  <p>Than I have my repository, which exposes nice crud interface using HAL+json representation. I am trying to enrich it with some projection/view particularly due to web UI to load one page data in single request. I am aware of excerps and projections, but they seems not to be enough powerful.</p>
  
  <pre><code>@Repository
  @RepositoryRestResource
  @Transactional(readOnly = true)
  public interface ARepository extends PagingAndSortingRepository&lt;A, Long&gt; {
    Page&lt;A&gt; findByNameContaining(String namePart, Pageable pageable);
    @Query(
      value = "SELECT a.name,\n" +
        "(SELECT CAST(count(ac.id) AS int) FROM COMMENTS ac WHERE ac.a_id = a.id),\n" +
        "(SELECT listagg(asf.id) FROM SIMPLE_FILES asf WHERE asf.a_id = a.id)\n" +
        "FROM AS a\n" +
        "WHERE a.id = :id",
      nativeQuery = true
    )
    Optional&lt;ACustomPage42DTO&gt; getByIdProjectedForScreen42(Long id);
  }
  </code></pre>
  
  <p>I have also tried to use JPQL, but there I had problem with fetch join (as I am not familiar with JPQL). My last evaluation query was something like this:</p>
  
  <pre><code>@Query("SELECT new sk.qpp.qqq.documents.projections.ACustomPage42DTO(" +
    "a " +
    "(SELECT CAST(count(ac) AS int) FROM COMMENTS ac WHERE ac.a = a)" +
    ")\n" +
    "FROM A a\n" +
    "LEFT JOIN FETCH a.simpleFiles\n" +
    "WHERE a.id = :id"
  )
  </code></pre>
  
  <p>I would like to get some general advice about what approach is best to implement custom and complex query to be returned in DTO (ideally with some specific links to actions when needed).</p>
  
  <p>PS: Implementing interface and returning simple (primitive) data works. Also using JPQL to create custom DAO instance works (with simple types and with single instance of type <code>A</code> for example). Method for using given query method does appear in search methods of given entity endpoint. I would like to have something more reasonable, so I would like to have <a href="https://docs.spring.io/spring-data/rest/docs/current/reference/html/#projections-excerpts" rel="nofollow noreferrer">projection as defined in spring data rest</a> project.</p>
  
  <p>I have my DTO object fully under my control. I prefer it to use <code>@Value</code> or <code>@Data</code> annotation from project lombok, but it is not a need. I have tried also these versions of DTO definition (using interface works for simple data and similarly class works for simple data).</p>
  
  <pre><code>interface ACustomPage42DTO {
      String getName();
      long getCommentsCount();
      Object getAsdf();
  }
  </code></pre>
  
  <p>Or using equivalent class with some bonus, like custom toString() method possible, or some custom getter for computed data:</p>
  
  <pre><code>@Value //lombok thing, imutable "POJO"
  public class ACustomPage42DTO {
      String name;
      long commentsCount;
      Set&lt;SimpleFile&gt; simpleFiles;
      public ACustomPage42DTO(A a, long count) {
          // constructor used by JPQL, if it works
          name = a.getName();
          this.commentsCount = count;
          this.simpleFiles = a.getSimpleFiles(); // should be already fetched, due to fetch join in JPQL
      }
  }
  </code></pre>
  
  <p>Both working approaches can be called using "search" url, instead of projection. I see my method <code>getByIdProjectedForScreen42</code> on url <a href="http://localhost:9091/api/a/search" rel="nofollow noreferrer">http://localhost:9091/api/a/search</a> listing. I would like to use it like (I think that is the "right" way) <a href="http://localhost:8080/api/a?projection=ACustomPage42DTOProjection" rel="nofollow noreferrer">http://localhost:8080/api/a?projection=ACustomPage42DTOProjection</a> .</p>