Generic querydsl orderBy dynamic path generation with left joins

Question

I've run into a problem while using JPA with Querydsl and Hibernate for data storage management. The sample model is as follows:

@Entity
public class User {
    ....

    @ManyToOne
    @JoinColumn(name = "CATEGORY_ID")
    private Category category;
}

@Entity
public class Category {
    ..
    private String acronym;

    @OneToMany(mappedBy = "category")    
    List<User> userList;    
}

In my Spring MVC webapp I have a search form with User parameters and orderBy select. The orderBy select can be either User property or Category property. The orderBy parameters are stored as Map (f.e. {"login" : "adm", {"firstName" : "John"}. The search function receives the search parameters (as string) and the map above with order specification. The simplified code for ordering is as follows:

Map<String, String> orderByMap = new HashMap<String, String>();
orderByMap.put("firstName", "asc");
orderByMap.put("unit.acronym", "desc");

PathBuilder<User> pbu = new PathBuilder<User>(User.class, "user");

....

for (Map.Entry<String, String> order : orderByMap.entrySet())
{
    // for simplicity I've omitted asc/desc chooser
    query.orderBy(pbu.getString(order.getKey()).asc());
}

The problem starts when I want to introduce sorting by Category's parameter, like {"category.acronym", "desc"}. As explained here, the above code will make querydsl to use cross join with Category table and omitt the Users without Categories, which is not expected behavior.

I know, I have to introduce the left join with Categories and use the alias for the sorting to make it work, hovewer I'm looking for efficient way to do it dynamically. Stripping each String looking for category or any other entity (like "user.category.subcategory.propetry") will introduce a lot of ugly code and I'd rather not do that.

I'd appreciate the help with some more elegant solution.

I don't yet have a good alternative, but I will try to sketch something. — Timo Westkämper

Timo Westkämper Timo Westkämper · Accepted Answer · 2013-12-04T15:49:13

I added now a protoype of the implementation to the test side of Querydsl https://github.com/mysema/querydsl/issues/582

I will consider a direct integration into Querydsl if this a common use case

public class OrderHelper {

private static final Pattern DOT = Pattern.compile("\\.");

public static PathBuilder<?> join(JPACommonQuery<?> query, PathBuilder<?> builder, Map<String, PathBuilder<?>> joins, String path) {
    PathBuilder<?> rv = joins.get(path);
    if (rv == null) {
        if (path.contains(".")) {
            String[] tokens = DOT.split(path);
            String[] parent = new String[tokens.length - 1];
            System.arraycopy(tokens, 0, parent, 0, tokens.length - 1);
            String parentKey = StringUtils.join(parent, ".");
            builder = join(query, builder, joins, parentKey);
            rv = new PathBuilder(Object.class, StringUtils.join(tokens, "_"));
            query.leftJoin((EntityPath)builder.get(tokens[tokens.length - 1]), rv);
        } else {
            rv = new PathBuilder(Object.class, path);
            query.leftJoin((EntityPath)builder.get(path), rv);
        }
        joins.put(path, rv);
    }
    return rv;
}

public static void orderBy(JPACommonQuery<?> query, EntityPath<?> entity, List<String> order) {
    PathBuilder<?> builder = new PathBuilder(entity.getType(), entity.getMetadata());
    Map<String, PathBuilder<?>> joins = Maps.newHashMap();

    for (String entry : order) {
        String[] tokens = DOT.split(entry);
        if (tokens.length > 1) {
            String[] parent = new String[tokens.length - 1];
            System.arraycopy(tokens, 0, parent, 0, tokens.length - 1);
            PathBuilder<?> parentAlias = join(query, builder, joins, StringUtils.join(parent, "."));
            query.orderBy(parentAlias.getString(tokens[tokens.length - 1]).asc());
        } else {
            query.orderBy(builder.getString(tokens[0]).asc());
        }
    }
}

}

Generic querydsl orderBy dynamic path generation with left joins

1 Answers