title: Specification API/Criteria API - Group by data returned by existing specification object and findAll(specification,pageable)
body: <p>Below are my entities:</p>
  <p>Product</p>
  <pre><code>@Entity
  @Table(name = &quot;Product&quot;)
  public class Product extends ReusableFields
  {
  
      private static final long serialVersionUID = 1L;
  
      @Id
      @GeneratedValue(strategy = GenerationType.SEQUENCE)
      Long productId;
  
      @NonNull
      @Column(name = &quot;product_name&quot;)
      String productName;
      String measurementUnit;
      //more fields and getters setters
  }
  </code></pre>
  <p>Inward Outward List related to product:</p>
  <pre><code>@Entity
  @Table(name = &quot;inward_outward_entries&quot;)
  public class InwardOutwardList extends ReusableFields
  {
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      Long entryid;
  
      @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
      @JoinColumn(name = &quot;productId&quot;, nullable = false)
      @JsonIgnoreProperties(
      { &quot;hibernateLazyInitializer&quot;, &quot;handler&quot; })
      Product product;
      
      @JsonSerialize(using = DoubleTwoDigitDecimalSerializer.class)
      Double quantity;
      //more fields
  }
  </code></pre>
  <p>Inward Inventory:</p>
  <pre><code>@Entity
  @Table(name = &quot;inward_inventory&quot;)
  public class InwardInventory extends ReusableFields implements Cloneable
  {
  
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      @Column(name = &quot;inwardid&quot;)
      Long inwardid;
  
      @ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
      @JoinTable(name = &quot;inwardinventory_entry&quot;, joinColumns =
      { @JoinColumn(name = &quot;inwardid&quot;, referencedColumnName = &quot;inwardid&quot;) }, inverseJoinColumns =
      { @JoinColumn(name = &quot;entryId&quot;, referencedColumnName = &quot;entryId&quot;) })
      Set&lt;InwardOutwardList&gt; inwardOutwardList = new HashSet&lt;&gt;();
  
      //more fields
  
  }
  </code></pre>
  <p>Inward Inventory Repo:</p>
  <pre><code>@Repository
  public interface InwardInventoryRepo extends extends JpaRepository&lt;InwardInventory, Long&gt;, JpaSpecificationExecutor&lt;InwardInventory&gt; ,PagingAndSortingRepository&lt;InwardInventory, Long&gt;
  {}
  </code></pre>
  <p>Previously the requirement was only to filter data and show as pages based on filters selected by user. So I have a working code to create specification dynamically based on inputs. It is working fine. After creating the specification, I am using:</p>
  <pre><code>Page&lt;T&gt; findAll(@Nullable Specification&lt;T&gt; spec, Pageable pageable);
  </code></pre>
  <p>to generate required list of records.</p>
  <p>But, now a new requirement has been added to show sum of quantities grouped on product name and measurement unit. i.e. whatever data is returned after filter should be grouped by. Since the filtration logic is already working fine, I do not want to touch it.</p>
  <p>Can somehow help how to reuse existing specification object and group the data returned by findall(specification,pageable) method.</p>
  <p>What I already tried.
  Since specification directly do not support group by, I autowired entity manager and created own criteria query. But this is not giving correct results as all the tables are getting joined twice. Might be because they are joined first during specification object and again during grouping by:</p>
  <pre><code>@Service
  @Transactional
  public class GroupBySpecification {
      @Autowired
      EntityManager entityManager;
  
      Logger log = LoggerFactory.getLogger(GroupBySpecification.class);
  
      public List&lt;ProductGroupedDAO&gt; findDataByConfiguration(Specification&lt;InwardInventory&gt; spec) {
          CriteriaBuilder builder = entityManager.getCriteriaBuilder();
          CriteriaQuery&lt;ProductGroupedDAO&gt; query = builder.createQuery(ProductGroupedDAO.class);
          Root&lt;T&gt; root = query.from(InwardInventory.class);
  
          Predicate p = spec.toPredicate(root, query, builder);
          query.where(p);
          Join&lt; InwardInventory, InwardOutwardList&gt; ioList = root.join(InwardInventory_.INWARD_OUTWARD_LIST);
          Join&lt;InwardOutwardList, Product&gt; productList = ioList.join(InwardOutwardList_.PRODUCT);
          query.multiselect(productList.get(Product_.PRODUCT_NAME), productList.get(Product_.MEASUREMENT_UNIT),
                  builder.sum(ioList.get(InwardOutwardList_.QUANTITY)));
          query.groupBy(productList.get(Product_.PRODUCT_NAME), productList.get(Product_.MEASUREMENT_UNIT));
  
          List&lt;ProductGroupedDAO&gt; groupedData = fetchData(query);
          return groupedData;
      }
  </code></pre>
  <p>Generated SQL - all the tables joined twice</p>
  <pre><code>SELECT DISTINCT product7_.product_name      AS col_0_0_,
                  product10_.measurementunit  AS col_1_0_,
                  Sum(inwardoutw12_.quantity) AS col_2_0_
  FROM   inward_inventory inwardinve0_
         INNER JOIN inwardinventory_entry inwardoutw1_
                 ON inwardinve0_.inwardid = inwardoutw1_.inwardid
         INNER JOIN inward_outward_entries inwardoutw2_
                 ON inwardoutw1_.entryid = inwardoutw2_.entryid
                    AND ( inwardoutw2_.is_deleted = 'false' )
         INNER JOIN product product3_
                 ON inwardoutw2_.productid = product3_.productid
         INNER JOIN warehouse warehouse4_
                 ON inwardinve0_.warehouse_id = warehouse4_.warehouse_id
         INNER JOIN inwardinventory_entry inwardoutw5_
                 ON inwardinve0_.inwardid = inwardoutw5_.inwardid
         INNER JOIN inward_outward_entries inwardoutw6_
                 ON inwardoutw5_.entryid = inwardoutw6_.entryid
                    AND ( inwardoutw6_.is_deleted = 'false' )
         INNER JOIN product product7_
                 ON inwardoutw6_.productid = product7_.productid
         INNER JOIN inwardinventory_entry inwardoutw8_
                 ON inwardinve0_.inwardid = inwardoutw8_.inwardid
         INNER JOIN inward_outward_entries inwardoutw9_
                 ON inwardoutw8_.entryid = inwardoutw9_.entryid
                    AND ( inwardoutw9_.is_deleted = 'false' )
         INNER JOIN product product10_
                 ON inwardoutw9_.productid = product10_.productid
         INNER JOIN inwardinventory_entry inwardoutw11_
                 ON inwardinve0_.inwardid = inwardoutw11_.inwardid
         INNER JOIN inward_outward_entries inwardoutw12_
                 ON inwardoutw11_.entryid = inwardoutw12_.entryid
                    AND ( inwardoutw12_.is_deleted = 'false' )
  WHERE  ( inwardinve0_.is_deleted = 'false' )
         AND ( warehouse4_.warehousename LIKE ? )
         AND ( product3_.product_name IN ( ?, ?, ?, ? ) )
  GROUP  BY product7_.product_name,
            product10_.measurementunit 
  </code></pre>