The variant with join now works, but I want to get rid of group by in request. How to make a query with a subquery quantity in select?

SELECT t.id, t.title, (SELECT COUNT(t2.t2_id) FROM table2 t2 WHERE (t2.t2_id = t.id)) FROM table1 t
  • I want to get rid of group by request First, why? secondly, in your request there is no GROUP BY ... And in general - what’s the point in one data set to receive data from different levels of grouping? - Akina
  • For table t add a field (for example, t2_count) in which you store the number of records by a foreign key from table t2 . When adding records or deleting from table t2 update the corresponding field in table t . This way you get rid of the subquery. - MrFylypenko

3 answers 3

option with join and group by - in my opinion - the best. if there is no specific need to get rid of join'a and group by - then leave!

    // Not understood when creating a question, I am the author

    This is the current used query with groupBy, trimmed. There is also a request for the amount of Project for page navigation. With join, an unequal number of records is obtained.

      CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Project> cq = cb.createQuery(Project.class); Root<Project> root = cq.from(Project.class); Join atts = root.join("attachments", JoinType.LEFT); cq.select( cb.construct( Project.class, root.get("id"), root.get("regDate"), // другие поля cb.count(atts) ) ) .groupBy(root); // ============== groupBy TypedQuery<Project> typedQuery = em.createQuery(cq);

      The question is removed. Error in the code. It turned out that the left join root.join (the "attachments" which created the error is not equal to the number of projects in the sample request. The request is no longer needed. Thanks anyway.