Filter joined table using Criteria Builder

Viewed 425

I am having problem filtering my second table using predicates.

so far I have right now:

Public class Account {
private long id;
private String name;
@OneToMany(mappedBy = "account", orphanRemoval = true, cascade = CascadeType.ALL)
private Post<List> post;
}

Public class Post {
private long id;
private Date datePost;
private String message;
@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "ACCOUNT_ID")
private Account account;
}

my dao class looks like:

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<ExceptionDeltaEvent> cq = cb.createQuery(Account.class);

        Root<Account> accountRoot = cq.from(Account.class);

        List<Predicate> whereRestrictions = new ArrayList<>();
        Join<Account, Post> accPost =
            accountRoot.join("post", JoinType.INNER);
                                                              //2021-03-16 to 2021-03-20
        whereRestrictions.add(cb.between(accPost.get("datePost"), fromDate, toDate));

        cq.where(whereRestrictions.stream()
            .toArray(Predicate[]::new))
            .distinct(true);

        TypedQuery<Account> query = em.createQuery(cq);

        return query.getResultList();

Here's my ACCOUNT table:

id name
1 bob
2 ron

POST table:

id datepost message account_id
1 2021-03-17 First 1
2 2021-03-19 Second 1
3 2021-03-28 Hello 1
4 2021-03-18 Second 2

and the return data should be acc id= 1 and 2 with post id of 1,2 and 4

but somehow it returns the all of the POST record

1 Answers

for this one, the only plausible solution is

  1. to create 2 separate methods on retrieving the Account and Post entities.
  2. thru selection, construct a custom entity or use Tuple.
  3. filter the POST table in the service layer.

note that the predicate and the joining of table that i made is 100% working

Related