title: HQL: How to query certain fields when one of those fields is a one-to-many List?
body: <p>I am having difficulty writing a HQL query to select ONLY the <code>caseid</code>, <code>title</code>, and <code>caseStatus</code> fields from my <code>Cases</code> entity. The cases returned have to be distinct based on caseid. I do not want the <code>name</code> and <code>userid</code> fields to be included. I also do not want to use Lazy fetching for <code>caseid</code>, <code>title</code>, and <code>caseStatus</code> fields. Note that the <code>caseStatus</code> field is a one-to-many List. Below are the entities. The getters/setters are omitted to save space. </p>
  
  <pre><code>@Entity
  @Table(name = "Cases")
  public class Cases {
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      @Column(name = "caseid", nullable = false)
      private Integer caseid;
      private Integer userid;
      private String name;
      private String title;
      @OrderBy("caseStatusId DESC")
      @OneToMany(mappedBy = "cases", fetch = FetchType.EAGER)
      private List&lt;CaseStatus&gt; caseStatus;
  }
  
  @Entity
  @Table(name = "CaseStatus")
  public class CaseStatus {
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      @Column(name = "caseStatusId", nullable = false)
      private Integer caseStatusId;
      private String info;
      @ManyToOne(fetch = FetchType.LAZY)
      @JoinColumn(name = "caseid")
      private Cases cases;
  }
  </code></pre>
  
  <p>My goal is to retrieve a distinct <code>List&lt;Cases&gt;</code> or <code>List&lt;Object[]&gt;</code> of the Cases entity containing only <code>caseid</code>, <code>title</code>, and a <code>List&lt;CaseStatus&gt;</code>. The <code>List&lt;CaseStatus&gt;</code> will contain <code>CaseStatus</code> objects with all of its fields populated.</p>
  
  <pre><code>public List&lt;Object[]&gt; getCases(String title) {
      TypedQuery&lt;Object[]&gt; q = em.createQuery("select distinct c.caseid, c.title, cs "
          + "FROM Cases c join c.caseStatus cs "
          + "where c.title like :title", Object[].class);
      q.setParameter("title", "%" + title + "%");
      List&lt;Object[]&gt; results = q.getResultList();
      return results;
  }
  </code></pre>
  
  <p>The above method is close, but not correct because rather than returning a <code>List&lt;CaseStatus&gt;</code> in one of the indexes, it is only returning a single <code>CaseStatus</code> entity.</p>
  
  <p>For example, if my DB contains a single <code>Case</code> with a <code>List&lt;CaseStatus&gt;</code> having a size of n for example, the results will be similar to the example below:</p>
  
  <p>Example of results I'm getting now. Not correct:</p>
  
  <pre><code>        List&lt;Object[]&gt; index 0:
              Contains an Object[] where:
                  Object[0] = {some caseid}
                  Object[1] = {some title}
                  Object[2] = {1st CaseStatus}
          List&lt;Object[]&gt; index 1:
              Contains an Object[] where:
                  Object[0] = {same caseid as the one found in index 0 above}
                  Object[1] = {same title as the one found in index 0 above}
                  Object[2] = {2nd CaseStatus}
          ...
          List&lt;Object[]&gt; index n-1:
              Contains an Object[] where:
                  Object[0] = {same caseid as all the previous}
                  Object[1] = {same title as all the previous}
                  Object[2] = {nth CaseStatus}
  </code></pre>
  
  <p>Example of results I hope to achieve:</p>
  
  <pre><code>        List&lt;Object[]&gt; index 0:
              Contains an Object[] where:
                  Object[0] = {unique caseid}
                  Object[1] = {some title}
                  Object[2] = List&lt;CaseStatus&gt; with size of n
  </code></pre>
  
  <p>Updated the question. Instead of <code>name</code>, <code>title</code>, and <code>List&lt;CaseStatus&gt;</code>, the fields I want to retrieve are <code>caseid</code>, <code>title</code>, and <code>List&lt;CaseStatus&gt;</code>. <code>caseid</code> is the primary key of Cases.</p>
  
  <p>I found various threads <a href="https://forum.hibernate.org/viewtopic.php?f=1&amp;t=991742" rel="nofollow noreferrer">Select Collections with HQL - hibernate forum</a> and <a href="https://stackoverflow.com/questions/39491812/select-collections-with-hql">Select collections with HQL - stackoverflow</a>. It's pretty much the problem I ran into. Looks like no one found a solution in these threads.</p>