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

Answer 3

if you want to get last book for each author , you can add transient field to get it :

@Entity
    @Table(name = "author")
    public class Author {
    .......
    @Transient 
    private Book lastBook;

   @PostLoad
   private void setLastBook() {
  if(books==null || books,isEmpty())
     this.lastBook=null;
  }else{
    this.lastBook=books.get(0);
  }
}

or make it one to one and save it in db by same method annotated with @PostPersist and @PostUpdate. it will save in db automatically last book

Answer 4

the answer by @Seldo97 is correct, but he missed the space between "book" and "where" in the Select query, which will throw an error.

so basically it should be

@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;

• author_id -> basically refers to foreign key column name in the child. entity

• date_published -> this refers to the column by which we want to sort(in this case the date column name).

so, the above query will take the record with the latest date and put it in the list object:

List<book> book;