title: How to select parent object and order by child's properties
body: <p>Disclaimer: our DB is legacy and not exactly ideal. I'm trying to solve a JPA mapping problem to deal with the non-ideal DB which is beyond my work's scope to fix. Please no DB-design comments...</p>
  
  <p>I have to do 2 things. I have to fetch a parent and its children, given a match on one of the child's attributes and I have to order the returned objects by the child's attribute.</p>
  
  <p>I'm wondering if I can use a 'fetch join' to do this instead of a regular join.</p>
  
  <p>The classes/mappings basically look like this:</p>
  
  <pre><code>@Entity
  @Table(name = "profiles")
  public class Profile  {
      @Id
      private Long id;
  
      @OneToMany(mappedBy = "profile", fetch = FetchType.LAZY)
      // @Where(clause = "deleted is null or deleted = 'N'")     (1)
      private List&lt;Account&gt; accounts;
  
      &lt;...snip...&gt;
  }
  
  @Entity
  @Table(name = "accounts")
  public class Account {
      @Id
      private Long id;
  
      @ManyToOne
      @JoinColumn(name = "profile_id", nullable = false, insertable = true, updatable = false)
      private Profile profile;
  
      private String name;
  
      &lt;...snip...&gt;
  }
  </code></pre>
  
  <p>Basically the SQL I want is something like:</p>
  
  <pre><code>select p.*, a.*
  from profiles p, accounts a
  where a.profile_id = p.id and lower(a.name) like '%some_search_text%'
  order by lower(a.name);
  </code></pre>
  
  <p>The problem is building a JPA query like:</p>
  
  <pre><code>String searchText = "%foobar%";
  
  CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
  CriteriaQuery&lt;Profile&gt; criteriaQuery = criteriaBuilder.createQuery(Profile.class);
  Root&lt;Profile&gt; profile = criteriaQuery.from(Profile.class);
  Join&lt;Profile, Account&gt; profileAccountJoin = profile.join(Profile_.accounts);
  
  criteriaQuery.where(
      criteriaBuilder.like(
          criteriaBuilder.lower(profileAccountJoin.get(column).as(String.class)), searchText);
  
  criteriaQuery.orderBy(profileAccountJoin.get(Account_.name)); // ??? this maybe works
  
  TypedQuery&lt;Profile&gt; query = entityManager.createQuery(criteriaQuery);
  
  List&lt;Profile&gt; results = query.getResultList();
  </code></pre>
  
  <p>I'll get a list of profiles whose children match. But later when converting to DTO and doing a profile.getAccounts() I'll get all the accounts the match on the profile_id, not filtered by name.</p>
  
  <p>How can this behavior, specifically the example SQL query above be achieved in JPA?</p>
  
  <p>If I could or should do this using a 'fetch join' how would I go about it?</p>