Hibernate /JPA and Spring Data statistics join 2 tables. Result from talbe 1 daily values

[Sergej Smoljanov]

I want to get result as
List of @Entity of study_material which will contain List<stydy_material_history> with entities - the max values from study_material_history per day (using rep_date) (for each study_material respectively)

I have 2 tables in mysql
create table study_material(    id  int NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'ID MATERIAL',    book_name varchar(100) not null COMMENT 'Material name (book name, video name ect.)',    total_page int not null default 0 COMMENT 'Number of pages in book' ) create table stydy_material_history(    id int not null AUTO_INCREMENT primary key comment 'id historical',    id_study_material int not null comment 'ID MATERIAL from study_material table',    current_page int not null  comment 'Page that was when result was saving',    rep_date DATETIME comment 'Date when result was saved' )

I created entities classes:

@Entity
@Table(name = "study_material")
public class StudyMaterial implements Serializable {
    Long id;
    String book_name;
    int total_page;
    private List<StudyMaterialHistoryCurrentState> historyCurentState = new  ArrayList<>();

    @OneToMany (fetch = FetchType.LAZY, cascade = CascadeType.ALL, mappedBy = "id")
    public List<StudyMaterialHistoryCurrentState> getHistory() {
        return historyCurentState;
	}

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY) 
    @Column(name = "id")
    public Long getId() {

        return id;
    }

    @Column(name = "book_name" )
    public String getBook_name() {

        return book_name;
    }

    @Column(name = "total_page")
    public int getTotal_page() {

        return total_page;
    }
    //Setters omitted
}

@Entity
@Table(name="stydy_material_history")
public class StudyMaterialHistoryCurrentState implements Serializable{
private Long id;
private Long id_study_material;
private Integer current_page;
private Date rep_date;
StudyMaterial material;

    @ManyToOne
    @JoinColumn(name = "id_study_material")
    public StudyMaterial getMaterial() {

        return material;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    public Long getId() {

        return id;
    }

    @Column(name ="current_page")
    public Integer getCurrent_page() {
        return current_page;
    }

    @Column(name ="rep_date", columnDefinition="DATETIME")
    @Temporal(TemporalType.TIMESTAMP)
    public Date getRep_date() {
        return rep_date;
    }
    //setters ommited
}

for using spring data i create:

public interface StudyMaterialRepository extends CrudRepository<StudyMaterial, Long> {
}

@Service("StudyMaterialService")
@Repository
@Transactional
public class StudyMaterialDaoImpl{
    @Resource
    StudyMaterialRepository repository;
	
    @PersistenceContext
    private EntityManager entityManager;

    @Transactional(readOnly = true)
    @Override
    public List<StudyMaterial> findAll() {
        return Lists.newArrayList(repository.findAll());
    }
}

to reiterate question
how can i get via findAll()
List of StudyMaterial that will contain List of StudyMaterialHistoryCurrentState than will contains  items that is maximum rep_date per day.
I can`t understand where and how i must changes to get such result
assuming that both tables are filled with data next is sql variant i want express
SELECT *  FROM study_material AS t1  JOIN stydy_material_history t2  ON t1.id = t2.id_study_material  JOIN (       SELECT id_study_material,          max( rep_date ) AS max_rep_date       FROM stydy_material_history       GROUP BY DATE( rep_date ) , id_study_material  ) AS t3  ON t2.id_study_material = t3.id_study_material AND t2.rep_date = t3.max_rep_date

[Sergej Smoljanov]

Sorry one correct

mappedBy = "material"

instead

mappedBy = "id"

.
but question still actual