返回

mongodb-Spring MongoRepository 获取聚合计数

发布时间:2022-08-23 01:33:08 425
# sql

我使用的是spring data mongodb 2.0.8版本,我需要获得特定公司某个部门的员工数量。下面是在MySQL中查询的等效查询。

MySQL

select 
    departmentId, count(employeeId)
from employees
where companyCode = 'ACME'
group by departmentId

MongoDb

db.employees.aggregate([
    {
      $match: {
        "companyCode": "ACME"
      }
    },
    {
        "$group" : {
            _id:"$departmentId", 
            count:{$sum:1}
        }
    }
])

我的问题是,如何将其转换为类似JPA中的MongoRepository代码。请参见以下代码:

public interface EmployeeDao extends MongoRepository<Employee, String> {

    Employee findFirstByName(String name);
    Employee findFirstByDepartmentId(String departmentId);
    List findEmployeesByCompanyCodeAndDepartmentId(String companyCode, String departmentId);
    List findEmployeesByDepartmentId(String departmentId);
    Long countByCompanyCode(String companyCode);
    Long countByDepartmentId(String departmentId);

    // FIXME:
    /*  How will I convert this into MongoRepository code, like above?

        MySQL:
        select 
            departmentId, count(employeeId)
        from employees
        where companyCode = 'ACME'
        group by departmentId

        MongoDb:
        db.employees.aggregate([
            {
              $match: {
                "companyCode": "ACME"
              }
            },
            {
                "$group" : {
                    _id:"$departmentId", 
                    count:{$sum:1}
                }
            }
        ])
    */

}

更新(适用于我的案例):

我发现以下代码适用于我的案例,但我不确定这是否可以接受,或者是否有更好的方法(根据最佳实践)。

...
        String companyCode =  ACME";
        MatchOperation filterCompany = match(new Criteria("companyCode").is(companyCode));
        GroupOperation groupCountByDepartmentId = group("departmentId").count().as("total");
        SortOperation sortByCountDesc = sort(new Sort(Sort.Direction.DESC, "total"));
        ProjectionOperation projectToMatchModel = project()
                .andExpression("_id").as("departmentId")
                .andExpression("total").as("count");
        Aggregation aggregation = Aggregation
                .newAggregation(filterCompany, groupCountByDepartmentId, sortByCountDesc, projectToMatchModel);
        AggregationResults groupResults =
                m_mongoTemplate.aggregate(aggregation, Employee.class, DepartmentCount.class);
        List result = groupResults.getMappedResults();
...

部门计数:

public class DepartmentCount {

    private String departmentId;
    private Integer count;

    // getters and setters

}

谢谢

特别声明:以上内容(图片及文字)均为互联网收集或者用户上传发布,本站仅提供信息存储服务!如有侵权或有涉及法律问题请联系我们。
举报
评论区(2)
按点赞数排序
用户头像
下一篇
javascript-使用变量代替数组 2022-08-22 23:52:17