sql-server - 在 grails 3.3.9 中提供默认的辅助排序列

Question

我有一个问题，当使用createCriteria().list(params)每页 10 条记录来翻阅一组 12 条记录时，第 2 页上显示的 2 条记录出现在第 1 页上。在域映射中指定了默认的“排序”，但指定的列包含重复项，因此我假设排序列中具有重复项的子集的顺序是不确定的。

这也使用可排序的列标题，就像 grails 在您生成全部时一样。这是否意味着任何时候选定的排序列包含重复的顺序可能是不确定的？有没有办法（除了蛮力）传递一个默认的辅助排序列（可能是 ID），除了选择的列来保证顺序吗？

这是在 SQL Server 上，这些是生成的查询：

第 1 页：

select TOP(?) 
    this_.procedure_occurrence_id as procedur1_22_1_, 
    this_.provenance_id as provenan2_22_1_, 
    this_.procedure_date as procedur3_22_1_, 
    this_.procedure_type_concept_id as procedur4_22_1_, 
    this_.procedure_source_value as procedur5_22_1_, 
    this_.associated_provider_id as associat6_22_1_, 
    this_.relevant_condition_concept_id as relevant7_22_1_, 
    this_.visit_occurrence_id as visit_oc8_22_1_, 
    this_.person_id as person_i9_22_1_, 
    this_.procedure_concept_id as procedu10_22_1_, 
    person_ali1_.person_id as person_i1_20_0_, 
    person_ali1_.person_source_value as person_s2_20_0_, 
    person_ali1_.provenance_id as provenan3_20_0_, 
    person_ali1_.location_id as location4_20_0_, 
    person_ali1_.gender_concept_id as gender_c5_20_0_, 
    person_ali1_.care_site_id as care_sit6_20_0_, 
    person_ali1_.year_of_birth as year_of_7_20_0_, 
    person_ali1_.provider_id as provider8_20_0_, 
    person_ali1_.race_concept_id as race_con9_20_0_, 
    person_ali1_.month_of_birth as month_o10_20_0_, 
    person_ali1_.ethnicity_concept_id as ethnici11_20_0_, 
    person_ali1_.ethnicity_source_value as ethnici12_20_0_, 
    person_ali1_.race_source_value as race_so13_20_0_, 
    person_ali1_.gender_source_value as gender_14_20_0_, 
    person_ali1_.day_of_birth as day_of_15_20_0_ 
from procedure_occurrence this_ 
inner join person person_ali1_ on this_.person_id=person_ali1_.person_id 
where (person_ali1_.person_id=?) 
and this_.provenance_id=? 
and this_.procedure_concept_id in (?, ?, ?, ?) 
order by this_.procedure_date desc

第2页：

WITH query AS (
    SELECT inner_query.*, ROW_NUMBER() 
    OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__ 
    FROM ( 
        select TOP(?) this_.procedure_occurrence_id as procedur1_22_1_, 
            this_.provenance_id as provenan2_22_1_, 
            this_.procedure_date as procedur3_22_1_, 
            this_.procedure_type_concept_id as procedur4_22_1_, 
            this_.procedure_source_value as procedur5_22_1_, 
            this_.associated_provider_id as associat6_22_1_, 
            this_.relevant_condition_concept_id as relevant7_22_1_, 
            this_.visit_occurrence_id as visit_oc8_22_1_, 
            this_.person_id as person_i9_22_1_, 
            this_.procedure_concept_id as procedu10_22_1_, 
            person_ali1_.person_id as person_i1_20_0_, 
            person_ali1_.person_source_value as person_s2_20_0_, 
            person_ali1_.provenance_id as provenan3_20_0_, 
            person_ali1_.location_id as location4_20_0_, 
            person_ali1_.gender_concept_id as gender_c5_20_0_, 
            person_ali1_.care_site_id as care_sit6_20_0_, 
            person_ali1_.year_of_birth as year_of_7_20_0_, 
            person_ali1_.provider_id as provider8_20_0_, 
            person_ali1_.race_concept_id as race_con9_20_0_, 
            person_ali1_.month_of_birth as month_o10_20_0_, 
            person_ali1_.ethnicity_concept_id as ethnici11_20_0_, 
            person_ali1_.ethnicity_source_value as ethnici12_20_0_, 
            person_ali1_.race_source_value as race_so13_20_0_, 
            person_ali1_.gender_source_value as gender_14_20_0_, 
            person_ali1_.day_of_birth as day_of_15_20_0_ 
        from procedure_occurrence this_ 
        inner join person person_ali1_ on this_.person_id=person_ali1_.person_id 
        where (person_ali1_.person_id=?) 
        and this_.provenance_id=? 
        and this_.procedure_concept_id in (?, ?, ?, ?) 
        order by this_.procedure_date desc ) inner_query ) 
    SELECT 
        procedur1_22_1_, 
        provenan2_22_1_, 
        procedur3_22_1_, 
        procedur4_22_1_, 
        procedur5_22_1_, 
        associat6_22_1_, 
        relevant7_22_1_, 
        visit_oc8_22_1_, 
        person_i9_22_1_, 
        procedu10_22_1_, 
        person_i1_20_0_, 
        person_s2_20_0_, 
        provenan3_20_0_, 
        location4_20_0_, 
        gender_c5_20_0_, 
        care_sit6_20_0_, 
        year_of_7_20_0_, 
        provider8_20_0_, 
        race_con9_20_0_, 
        month_o10_20_0_, 
        ethnici11_20_0_, 
        ethnici12_20_0_, 
        race_so13_20_0_, 
        gender_14_20_0_, 
        day_of_15_20_0_ 
    FROM query 
    WHERE __hibernate_row_nr__ >= ? 
    AND __hibernate_row_nr__ < ?

标签： sql-serverhibernategrails

Answer 1

I think it's possible to define more than one sort cloumn in domain class:

    static mapping = { 
          sort(["property1" : "asc",
                "property2" : "desc"] ) 
    }