JPA @OneToMany get latest record by date from Join

Question

Getting stuck trying to fetch the latest record from a Join I have the following classes

Author

    @Entity
    @Table(name = "author")
    public class Author {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    @Column(name = "name")
    private String name;
    @OneToMany
    @JoinColumn(name = "author_id", referencedColumnName = "id")
    @OrderBy("id Desc")
    private List<Book> books;
    public int getId() {
       return id;
    }
    public void setId(int id) {
       this.id = id;
    }
    public String getName() {
       return name;
    }
    public void setName(String name) {
       this.name = name;
    }
    public List<Book> getBooks() {
       return books;
    }
    public void setBooks(List<Book> books) {
       this.books = books;
     }
    }

Book

    @Entity
    @Table(name = "book")
    public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    @Column(name = "author_id")
    private Integer authorId;
    @Column(name = "date_published")
    private Date datePublished;
    public int getId() {
        return id;
    }
    public void setId(int id) {
       this.id = id;
    }
    public Integer getAuthorId() {
       return authorId;
    }
    public void setAuthorId(Integer authorId) {
       this.authorId = authorId;
    }
    public Date getDatePublished() {
       return datePublished;
    }
    public void setDatePublished(Date datePublished) {
       this.datePublished = datePublished;
     }
    }

Repository

    @Repository
    public interface AuthorRepository extends 
    JpaRepository<Author, Long> {

         public Page<Author> findALL(int id, Pageable pageable);

    }

Current results

    {
      "id": 1,
      "name": "James",
      "books":[
      {
         "id": 1,
         "name": "book1",
         "datePublished": '12/12/2012'

      },
      {
         "id": 1,
         "name": "book2",
         "datePublished": '01/02/2013'
      }]
    },

    {
      "id": 2,
      "name": "Tim",
      "books":[
      {
         "id": 5,
         "name": "book5",
         "datePublished": '12/12/2014'

      },{
          "id": 6,
          "name": "book6",
          "datePublished": '01/02/2015'

       }]
    }

Expected Result

    {
       "id": 1,
       "name": "James",
       "books":[
       {
         "id": 1,
         "name": "book2",
         "datePublished": '01/02/2013'
       }]
   },
   {
     "id": 2,
     "name": "Tim",
     "books":[
     {
        "id": 6,
        "name": "book6",
        "datePublished": '01/02/2015'

     }]
   }

From this a list of Authors are being returned with all their respective books.

Question is how can JPA assist me to pick only the latest book from the collection based on date published.

Answer 1

If you are using hibernate you can achieve this using @JoinFormula to map the latest record by date. Something like:

@ManyToOne(fetch = FetchType.LAZY)
@JoinFormula("(" +
    "SELECT b.id " +
    "FROM book b " +
    "WHERE b.author_id = id " +
    "ORDER BY b.date_published DESC " +
    "LIMIT 1" +
")")
private Book latestBook;

Answer 2

I had similar problem. The other solution is with @Where annotation:

@OneToMany
@JoinColumn(name = "author_id", referencedColumnName = "id")
@Where(clause = "date_published = (SELECT MAX(book.date_published) " +
                                   "FROM book" +
                                   "where author_id = book.author_id)")
@OrderBy("datePublished Desc")
private List<Book> books;

My post on stack: Get applications with their latest status only