通用querydsl orderBy使用左连接生成动态路径 [英] Generic querydsl orderBy dynamic path generation with left joins

问题描述

我在使用带有Querydsl和Hibernate的JPA进行数据存储管理时遇到了问题。示例模型如下：

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;    
}

在我的Spring MVC webapp中，我有一个带有用户参数和orderBy选择的搜索表单。 orderBy select可以是User属性或Category属性。 orderBy参数存储为Map（fe {login：adm，{firstName：John}。搜索功能接收搜索参数（作为字符串）和上面带有订单规范的地图。简化代码订购如下：

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());
}

当我想按类别参数引入排序时问题就出现了，比如{category.acronym ，desc}。如此处所述，ab ove代码将使querydsl使用与Category表的交叉连接并省略没有类别的用户，这不是预期的行为。

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.

我知道，我必须介绍左连接使用类别并使用别名进行排序以使其正常工作，我正在寻找有效的方法来动态地进行排序。剥离每个字符串寻找类别或任何其他实体（如user.category.subcategory.propetry）将引入许多丑陋的代码，我宁愿不这样做。

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.

推荐答案

我现在添加了一个原型的实现Querydsl的测试方 https://github.com/mysema/querydsl/issues/582

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

如果这是一个常见的用例，我会考虑直接集成到Querydsl

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());
        }
    }
}

}

这篇关于通用querydsl orderBy使用左连接生成动态路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！