Skip to content

Incorrect query to calculate a project's monthly cost #15

Description

@lukaseder

Thanks for doing this comparison.

I've stumbled upon this query here:

CommonTableExpression<Record4<Integer, String, BigDecimal, String>> project_info =
name("project_info")
.fields("project_pid", "project_name", "monthly_cost", "company_name")
.as(select(PROJECT.PID, PROJECT.NAME, EMPLOYEE.SALARY, COMPANY.NAME)
.from(PROJECT)
.join(PROJECTEMPLOYEE).on(PROJECT.PID.eq(PROJECTEMPLOYEE.PROJECT_PID))
.join(EMPLOYEE).on(PROJECTEMPLOYEE.EMPLOYEE_PID.eq(EMPLOYEE.PID))
.leftJoin(DEPARTMENT).on(EMPLOYEE.DEPARTMENT_PID.eq(DEPARTMENT.PID))
.leftJoin(COMPANY).on(DEPARTMENT.COMPANY_PID.eq(COMPANY.PID))
);
Field<BigDecimal> monthly_cost = project_info.field("monthly_cost", BigDecimal.class);
Field<?> project_name = project_info.field("project_name");
Field<?> company_name = project_info.field("company_name");
CommonTableExpression<Record2<Integer, BigDecimal>> project_cost =
name("project_cost")
.fields("project_pid", "total_cost")
.as(select(project_info.field("project_pid", Integer.class), sum(monthly_cost))
.from(project_info)
.groupBy(project_info.field("project_pid"))
);
Field<BigDecimal> total_cost = project_cost.field("total_cost", BigDecimal.class);
SelectSeekStep1<? extends Record4<?, BigDecimal, ?, BigDecimal>, ?> query = create
.with(project_info)
.with(project_cost)
.select(project_name, total_cost, company_name, sum(monthly_cost).as("company_cost"))
.from(project_info)
.join(project_cost).using(project_info.field("project_pid"))
.where(total_cost.greaterThan(new BigDecimal(totalCostBoundary)))
.groupBy(project_name, total_cost, company_name)
.orderBy(company_name);
return query.fetchInto(ProjectsWithCostsGreaterThanOutput.class);

I'm sure there's an easier way to write this with jOOQ (or with all other solutions, as the query does contain a bit of redundancy), but first, I'd like to point out that your using a many to many relationship between employee and project seems to hint at the query being wrong as an employee who is assigned to several projects will produce more than their actual costs. I suspect a weighted average should be used instead.

Once this has been done, I guess that by using window functions, you will achieve a much more straightforward (and faster!) solution in all frameworks.

I hope this helps

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions