3

With Criteria Builder / Criteria Query is it possible to use the selected fields in the where statement?

My end goal is to be able to let the UI apply filters to the DTO and I'm thinking the easiest way would be to be apply those filters on the selected fields.

So if for example, my query returned a DTO with firstName (user table), lastName (user table), and bookmarked (sub query across another table), could I perform my default query and then apply another where clause looking at the firstName, lastName and bookmarked?

something like 

select 
   u.firstName as first,
   u.lastName as last, 
   (select CAST(1 AS BIT) from user_bookmark b where b.user_id=u.id as bookmarked)
from user u
where first = 'john' and bookmarked = true; //Dynamic part from UI

I have all the query working except I'm not sure how to filter the results based on the selected fields. 

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<OfficerDTO> cq = cb.createQuery(OfficerDTO.class);
    Root<OfficerEntity> root = cq.from(OfficerEntity.class);

    Subquery<Long> subquery = null;
    subquery = cq.subquery(Long.class);

    Root<OfficerBookmarkEntity> subRoot = subquery.from(OfficerBookmarkEntity.class);

    System.out.println("************ adding query");
    subquery.select(subRoot.get("user").get("id"));
    List<Predicate> predicates = new ArrayList<>();

    predicates.add(cb.equal(subRoot.get("officer").get("id"), root.get("id")));
    predicates.add(cb.equal(subRoot.get("user").get("id"), 123456789L));
    subquery.where(cb.and(predicates.toArray(new Predicate[]{})));

    cq.multiselect(
                root.get("firstName").alias("firstName"),
                root.get("lastName").alias("lastName"),
                subquery.getSelection().isNotNull().alias("bookmarked")
    );


    TypedQuery<OfficerDTO> q = em.createQuery(cq);
    return q.getResultList();

I know I could add cq.where(cb.equal(root.get("firstName"), "Jane")); but that's not very dynamic and I can't figure out a way to add a where on the bookmarked part, something like cq.where(cb.equal(root.get("firstName"), "Jane"), cb.equal(subquery.getSelection().isNotNull(), true) ); doesn't work, because the subquery isn't executed there.

snieguu
  • 2,073
  • 2
  • 20
  • 39
John
  • 1,808
  • 7
  • 28
  • 57
  • Couldn't you use a LEFT JOIN instead of that subselect? This way all the fields could be used on the where predicates – areus Feb 26 '20 at 16:27
  • How would that look exactly? I'm not sure how to do that I guess – John Feb 26 '20 at 17:54
  • If you have two tables, A and B, a left outer join selects the rows on A that are referenced by B (intersection) plus the rows in A that aren't referenced by B. In that case, B fields on select clause would be null. – areus Feb 26 '20 at 18:08
  • Sorry, I know what a left join is, i meant how would I do that with criteria query and I still don't see how that affects the original question though – John Feb 26 '20 at 18:19

0 Answers0