Replace Stream API operation with JPQL

Question

I have a service with following method:

public Collection<List<GaUtm>> getGaUtmGroupedByAccountLogin(List<String> loginsOfArchivedGaAccounts) {
   return gaUtmRepository.findAll()
           .stream()
           .filter(gaUtm -> !loginsOfArchivedGaAccounts.contains(gaUtm.getGaUtmId().getAccountLogin()))
           .collect(Collectors.groupingBy(gaUtm -> gaUtm.getGaUtmId().getAccountLogin())).values(); 

    }

I want to replace it with Spring Data + JPQL:

@Repository
public interface GaUtmRepository extends JpaRepository<GaUtm, GaUtmId> {

 @Query(...)
 Collection<List<GaUtm>> findGaUtmGroupedByAccountLogin(List<String> loginsOfArchivedGaAccounts);

}

I'm stuck with grouping by and what should i select:

SELECT ... FROM GaUtm utm.. WHERE utm.gaUtmId.accountLogin NOT IN :loginsOfArchivedGaAccounts

Entity:

public class GaUtm {

    @EmbeddedId
    private GaUtmId csGaUtmId;

}

@Embeddable
public class GaUtmId implements Serializable {

    @Column(name = "account_login")
    private String accountLogin;
}

Answer 1

You can't use SQL grouping for what you want to achieve.

Collectors.groupingBy () partitions your dataset into groups, producing a list of values for each key, whereas the SQL GROUP BY operator do aggregation (sum, average, count...), producing a single row for each key.

You can increase your service performance by asking the DB to do the filtering, but the grouping operation must be done in the service.