title: Filtering entities on a version field with JPA Criteria-API
body: <p>I have an entity with an embedded id which contain an id and version field.</p>
  
  <pre><code>@Entity
  @Table(name = "MyEntity")
  public class MyEntity {
      @EmbeddedId
      private MyEntityEmbeddedId compositeId;
  
      @Column
      private DateTime begin;
  
      @Column
      private DateTime end;
  
  }
  
  @Embeddable
  public class MyEntityEmbeddedId {
      @Column(name = "ID", nullable = false, updatable = false)
      private Long id;
  
      @Column(name = "VERSION", nullable = false, updatable = false)
      private Long version;
  
      public MyEntityEmbeddedId () {}
  
  }
  </code></pre>
  
  <p>I need to query the database with some criteria as filter for the entity. For example, I will request with a date and a list of id.</p>
  
  <pre><code>List&lt;MyEntity&gt; listEntities = getMyEntities(List&lt;Long&gt; ids, DateTime date);
  </code></pre>
  
  <p>I succeed to create a query which retreives all entities for which their id is in the ids list and that the provided date is between the "begin" and "end" date properties of MyEntity.</p>
  
  <p>But as this entity has a composite id, there are many MyEntity with the same "id" that may correspond to the request parameters. I want to add another filter to retreive the MyEntity which have the highest "version" number if there are many with the same "id".</p>
  
  <p>Here is an example of what might be in the database:</p>
  
  <pre><code>+------+-----------+--------------------+
  | id   | version   | began    |   end   |
  +------+-----------+--------------------+
  | 1    | 1         | ...      |   ...   |
  | 1    | 2         | ...      |   ...   |
  | 1    | 3         | ...      |   ...   |
  | 2    | 1         | ...      |   ...   |
  | 2    | 2         | ...      |   ...   |
  +------+-----------+--------------------+
  </code></pre>
  
  <p>If all the previous database records correspond to the filtering parameters, I need to get only these one because they are those with the highest version number for their corresponding id:</p>
  
  <pre><code>+------+-----------+--------------------+
  | id   | version   | began    |   end   |
  +------+-----------+--------------------+ 
  | 1    | 3         | ...      |   ...   |
  | 2    | 2         | ...      |   ...   |
  +------+-----------+--------------------+
  </code></pre>
  
  <p>Currently, I do the request but I filter the version within a method inside my service but I would prefer it if the version was already filtered in the database request. I don't need to retreive records that will be discarded after the version was filtered.</p>
  
  <p>I'm searching a way to make self-join with JPA Criteria-API. </p>
  
  <p>In the following link, it seems that the accepted answer will resolve my problem by I'm wondering how I would translate the suggested SQL in JPA Criteria-API.</p>
  
  <p><a href="https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column">SQL Select only rows with Max Value on a Column</a></p>
  
  <pre><code>select yt1.*
  from yourtable yt1
  left outer join yourtable yt2
  on (yt1.id = yt2.id and yt1.rev &lt; yt2.rev)
  where yt2.id is null;
  </code></pre>
  
  <p>My issue is when I'm trying to create the join. You need to provide the property from the first entity which link to the second entity in the join. As I want to make a self-join, there is no property inside my entity that point to itself.</p>
  
  <pre><code>Join&lt;MyEntity, MyEntity&gt; selfJoin = root.join(MyEntity_.???);
  </code></pre>
  
  <p>As you see, I'm using a static metamodel of MyEntity. It there a way to make the self-join with JPA Criteria-API? </p>
  
  <p>Or maybe there is another way to build my request than with a self left-join as suggested in the other stackoverflow question. </p>
  
  <p>I'm using Spring Data JPA Specification to build my query with Predicate. The findAll(Specification) is then used. I allready have two others Specification methods to generate predicate for the withIds() and withDate(). This let me create queries with the "undetermined number" of parameters provided by the user.</p>
  
  <pre><code>public static Specification&lt;MyEntity&gt; withIdAndDate(final List&lt;Long&gt; ids, 
          final DateTime date) {
      return new Specification&lt;MyEntity&gt;() {
  
          @Override
          public Predicate toPredicate(Root&lt;MyEntity&gt; root, CriteriaQuery&lt;?&gt; query, 
                  CriteriaBuilder cb){
              Join&lt;MyEntity, MyEntity&gt; selfJoin = root.join();
              Predicate pIds = withIds(ids).toPredicate(root, query, cb);
              Predicate pDate = withDate(date).toPredicate(root, query, cb);
  
              return cb.and(pIds, pDate);
          }
  
      };
  }
  </code></pre>
  
  <p>Thanks a lot for your help and suggestion!</p>