JPA Criteria API filter subentities

Question

The code example:

    @Entity
    public class Event {

    @Id
    @GeneratedValue
    private Long id;
    private String name;
    @OneToMany(mappedBy="event")
    private List<Actions> actions;

    }


    @Entity
    public class Action {

    @Id
    @GeneratedValue
    private Long id;
    private String name;
    private Date date;
    @ManyToOne
    @JoinColumn(name = "event_id")
    private Event event;

    }

    public class EventSpecification {

    public static Specification<Event> findByCriteria(EventSearchCriteria criteria) {

        return (root, criteriaQuery, criteriaBuilder) -> {
            List<Predicate> predicates = new ArrayList<>();

            criteria.getDate().ifPresent(date -> {
                Join<Event, Action> join = root.join(Event_.actions);
                predicates.add(criteriaBuilder.equal(join.get(Action_.date), criteria.getDate().get()));
            });

            return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
        };
    }
    }
...

eventRepository.findAll(EventSpecification.findByCriteria(searchCriteria))

In my database I have the next:

Event Table
id, name
1, 'event_1'
2, 'event_2'

Action Table
id, name, date, event_id
1, 'action_event_1_1', '01-01-2018', 1
2, 'action_event_1_2', '01-01-2018', 1
3, 'action_event_1_3', '01-02-2018', 1

4, 'action_event_2_1', '01-03-2018', 2

Using my Specification code with data='01-01-2018', as a result I get a list of identical Event objects and the list's size is count of joined objects, that fulfill the claus Action.date = criteria.date:

[{id=1, name=event_1, actions=[all 3 actions]},
{id=1, name=event_1, actions=[all 3 actions]}]

I need to get the next result:

{id=1, name=event_1, actions=[only two actions with date '01-01-2018']}

I tried to add

criteriaQuery.groupBy(root.get(Event.id));

It fixes resulted list size -

{id=1, name=event_1, actions=[all 3 actions]}

but I still get all 3 actions of event 1.

The question is:

Is it possible to get only Events, that contain Actions with a required date and so that each Event contains only a list with Actions with the requested date?

Answer 1

I found the next solution:

public static Specification<Event> findByCriteria(EventSearchCriteriacriteria) {

        return (root, criteriaQuery, criteriaBuilder) -> {
            List<Predicate> predicates = new ArrayList<>();

            criteria.getDate().ifPresent(date -> {
                criteriaQuery.distinct(true);
                Join<Event, Action> join = (Join<Event, Action>)root.fetch(Event_.actions);
                predicates.add(criteriaBuilder.equal(join.get(Action_.date), criteria.getDate().get()));
            });

            return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
        };
    }

Probably Join<Event, Action> join = (Join<Event, Action>)root.fetch(Event_.actions); isn't the best solution, because my IDE warns me regarding the casting, but this is the only working solution I found.

By the way, it works perfect if not to use Criteria API:

@EntityGraph(value = "Event.actions", type = EntityGraph.EntityGraphType.LOAD)
@Query("select distinct e from Event e join e.actions a where a.date = (:date)")
List<Event> findAllWithActionsByDate(@Param("date") Date date);

But the avoiding of Criteria API is unfit in my case.

Answer 2

Try this JPQL query:

select e from Events e join Actions a with a.date=:date
group by e