java - QueryDSL filtering with a label table

Question

I have a table which is queried for a grid view using labels as filters.

Schema:

project: id, col_a

label: id, name, type

label_project: id, label_id, project_id

The problem I have is that I want to get all project records with the labels the user is using but for some labels an OR needs to be done,

Here is a working example of what the query needs to do:

SELECT  DISTINCT gd.*
    FROM  project p
    JOIN  label_project lp1  ON lp1.label_id=306
    JOIN  label_project lp2  ON lp2.label_id=135
    JOIN  label_project lp3  ON lp3.label_id=285
    JOIN  label_project lp4  ON lp4.label_id=173
    WHERE  (      lp1.project_id=p.id
              OR  lp2.project_id=p.id
           ) -- labels of lp1  and  lp2 have the same type
      AND  lp3.project_id=p.id
      AND  lp4.project_id=p.id;
            -- labels of (lp1, lp2), lp3 and lp4 have different types

Lets say there are 6 label "types" and for labels of the same type an OR needs to be done between them(see first where clause in query) for the rest use AND (see rest of where clause)

The problem with the example query is that it is extremely show in QueryDSL ~10 seconds for a single query. I read this is mainly because the query uses distinct.

Would anyone know a way to write this query in QueryDSL with better performance? Or in SQL for that matter

Query before label filtering is added:

        query.distinct().from(PROJECT)
                        .leftJoin(FAVORITE_PROJECT)
                        .on(PROJECT.eq(FAVORITE_PROJECT.project).and(FAVORITE_PROJECT.employee.eq(employee)))
                        .where(ProjectService.restrictedProjectWhereClause(context.getEmployee()));
    }

    /**
     * Returns a predicate that filters out results of restricted projects where the employee has no rights for
     * @param employee The logged in employee
     * @return The predicate
     */
    public static Predicate restrictedProjectWhereClause(Employee employee) {
        return PROJECT.restricted.isFalse()
                .or(PROJECT.restricted.isTrue()
                        .and(PROJECT.employee.eq(employee)
                                .or(PROJECT.leaderEmployee.eq(employee)
                                        .or(PROJECT.managerEmployee.eq(employee)
                                                .or(hasRestrictedRoleAccess(employee).exists())))));
    }

    private static JPQLQuery<Integer> hasRestrictedRoleAccess(Employee employee) {
        return JPAExpressions.selectFrom(USER_SECURITY_ROLE)
                .join(USER)
                .on(USER_SECURITY_ROLE.user.eq(USER))
                .join(EMPLOYEE)
                .on(USER_SECURITY_ROLE.user.eq(EMPLOYEE.user))
                .where(USER_SECURITY_ROLE.securityRole.in(ESecurityRole.RESTRICTED_SECURITY_ROLES)
                        .and(EMPLOYEE.eq(employee)))
                .select(USER_SECURITY_ROLE.id);
    }

How I add the label filtering to the query in QueryDSL:

        // First add necessary joins
        for (int i = 0; i < labels.size(); i++) {
            QLabelProject lp = new QLabelProject(String.format("lp%d", i));
            labelMap.computeIfAbsent(labels.get(i).getSystemLabelType(), k -> new HashMap<>());
            labelMap.get(labels.get(i).getSystemLabelType()).put(labels.get(i), lp);

            query = query.join(lp)
                    .on(lp.project.eq(qProject));
        }

        // Decide where clause
        BooleanExpression expression = null;
        for (Map.Entry<ESystemLabelType, Map<Label, QLabelProject>> entry : labelMap.entrySet()) {
            BooleanExpression subExpression = null;
            for (Map.Entry<Label, QLabelProject> lp : entry.getValue().entrySet()) {
                if (entry.getKey() == null) {
                    subExpression = subExpression == null ? lp.getValue().label.id.eq(lp.getKey().getId()) :
                            subExpression.and(lp.getValue().label.id.eq(lp.getKey().getId()));
                } else {
                    subExpression = subExpression == null ? lp.getValue().label.id.eq(lp.getKey().getId()) :
                            subExpression.or(lp.getValue().label.id.eq(lp.getKey().getId()));
                }
            }
            expression = expression == null ? (BooleanExpression)new BooleanBuilder().and(subExpression).getValue() :
                    expression.and(subExpression);
        }

Answer 1

I don't really understand what you are trying to achieve, but see if something like this would work:

SELECT gd.*
    FROM  grid_data gd
    JOIN label_grid_data AS lgd ON lgd.grid_data_id = gd.id
    WHERE lgd.label_id IN (285, 173, 306, 135)

There WHERE clause may need to be more complex, but I suspect you don't really need all those subqueries.

Another approach:

( SELECT grid_data_id FROM label_grid_data
      WHERE label_id IN (285, 173)   -- "OR"
)
UNION ALL
( SELECT grid_data_id FROM label_grid_data
      WHERE label_id IN (306, 135)
      HAVING COUNT(*) = 2            -- kludge to achieve "AND"
)

Then

SELECT gd.*
    FROM ( the above union ) AS lgd
    JOIN grid_data gd  ON gd.id = lgd.grid_data_id

That will give you the rows that have either 285 or 277 or both 406 and 135.

Then, please provide SHOW CREATE TABLE so we can advise on the optimal INDEXes to have.