使用关联分页的Grails中的Hibernate查询的独特结果 [英] distinct result in Hibernate Query in grails with pagination using association

问题描述

最初，我建立了没有分页的查询，所有的搜索结果都很好。
但现在我需要分页，并且不想打扰我最初的查询模式。有什么办法可以得到不同的结果。

  Class Department {
 int id; 
字符串名称; 
 static hasMany = [courses：Courses] 
} 
 
课程课程{
 String courseName; 
 String courseCode; 
 static hasMany = [student：Student] 
 static belongsTo = [department：Department] 
} 
 
班级学生{
 String studentName; 
字符串地址; 
 static belongsTo = [courses：Courses] 
} 
 
 
 //控制器
 def list = Department.createCriteria.listDistinct {
 if （params.id）{
和{eq {id，params.id}} 
} 
和{
 courses {
 if（params.courseName） {
和{eq（courseName，params.courseName）} 
} 
} 
和{
学生{
 if（params.studentName） {
和{eq（studentName，params.studentName）} 
} 
} 
} 
} 
} 
  

我无法给您实际的表格和域名，但与上面的关系几乎相同。它对于简单的结果非常有效，但不能分页。我已经尝试了一些解决方案，但它返回错误。直到现在我还没有记录任何错误。
我已经知道listDistinct不能用于分页参数。和列表没有提供明确的参数。

我尝试了投影，但无法像以前一样检索所有属性。有没有解决办法。因为我需要从所有可能的属性中搜索三个表中的任何一个。我需要将我的所有查询切换到另一个方法吗？

解决方案

标准，分页和不同的协同工作，解决方案是：
1.使用list（）而不是listDistinct（）
2.使用maxResults和firstResult进行分页
3.使用预测{不同的'id'}获得不同的结果
4.在获得id列表之后，使用getAll（）方法检索实际对象

它会是：

  def ids = Department.createCriteria（）。list（）{
 projections {
不同的'id'
} 
 maxResults params.max 
 firstResult params.offset 
 
 if（params.id）{
 and {eq { id，params.id}} 
} 
和{
 courses {
 if（params.courseName）{
和{eq（courseName，params。 courseName）} 
} 
 } 
和{
学生{
 if（params.studentName）{
和{eq（studentName，params.studentName）} 
} 
 } 
} 
} 
} 
 return Department.getAll（id）
  

（代码现在没有测试过）

I have three class interlinked with each other.

Initially I build query without pagination and all search results were fine. But now I need pagination and don't want to disturb my initial query pattern. Is there any way I could get distinct results.

Class Department{
    int id;
    String name;
    static hasMany = [courses:Courses]
}

Class Courses{
    String courseName;
    String courseCode;
    static hasMany = [student:Student]
    static belongsTo = [department:Department]
}

Class Student{
    String studentName;
    String address;
    static belongsTo = [courses:Courses]
}


//controller
def list = Department.createCriteria.listDistinct{
    if(params.id){
         and{eq{"id",params.id}}
    }
    and{
        courses{
            if(params.courseName){
                  and{eq("courseName",params.courseName)}
            }
        }
        and{
            student{
                 if(params.studentName){
                         and{eq("studentName",params.studentName)}
                 }
            }
        }
    }
}

I could not gave you the actual tables and domains, but the relation is pretty much the same as above. It worked really fine for dintinct results but couldnot paginate. I have tried a number of solution but it returns error. I have not recorded any errors till now. I have came to know listDistinct can not be used for pagination parameters. and list doesnot provide distinct parameters.

I tried projection but couldnot retrieve all the attributes as before. Is there any solutions to it. As I need to search from all possible attributes with all the realtion from any of the three tables. Do I need to switch all my query to another method?

解决方案

I had a hard time with similar task some time ago - getting criteria, pagination and distinct to work together, and the solutions is: 1. use list() instead of listDistinct() 2. use maxResults and firstResult for pagination 3. use projections { distinct 'id' } for getting distinct results 4. and after getting the list of ids, use getAll() method to retrieve actual objects

so joining it it would be:

def ids = Department.createCriteria().list() {
    projections {
        distinct 'id'
    }
    maxResults params.max
    firstResult params.offset

    if(params.id){
         and{eq{"id",params.id}}
    }
    and{
        courses{
            if(params.courseName){
                  and{eq("courseName",params.courseName)}
            }
        }
        and{
            student{
                 if(params.studentName){
                         and{eq("studentName",params.studentName)}
                 }
            }
        }
    }
}
return Department.getAll(ids)

(code not tested now)

这篇关于使用关联分页的Grails中的Hibernate查询的独特结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！