带有Criteria的Grails，或者一种按预定顺序排序的快速方法 [英] Grails withCriteria fetch, or a fast way to sort in predetermined order
问题描述
一个数据库函数返回一个特定半径内所有事件的ID，并按其距离排序。
A database function returns id's of all events within a certain radius, ordered by their distance.
然后，为了保持性能，我急切地在 withCriteria 像这样：
Afterwards, to preserve performance I'm eagerly loading necessary collections in withCriteria like this:
def events = Event.withCriteria {
'in'('id', ids)
fetchMode("someRelation", FetchMode.JOIN)
// a few more joins
setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)
}
但是，这会弄乱顺序。我注意到，此条件查询的结果返回了按ID排序的所有事件。这样做确实有些道理，因为 in 不能保证任何特殊的排序方式（也没有任何应有的意义）。但是，这带来了一个问题，因为我希望对列表进行排序。
However this messes up the ordering. I've noticed that the result of this criteria query returns all events sorted by id. Which does somewhat make sense since in doesn't guarantee any sort of special ordering (nor does it make any sense that it should). However this poses a bit of a problem, since I want this list to be ordered.
所以我要做的是：
List<Event> temp = [];
ids.each { id -> temp << events.find { it.id == id } }
events = temp;
但是，当列表包含〜2400个元素时，这段代码将使总执行时间增加大约1秒，是我希望尽可能降低的东西。
However when the list contains ~2400 elements this piece of code adds around 1 second to total execution time which is something I wish to lower as much as possible.
还有其他方法可以加快此过程吗？
Is there any other way of doing this which could speed up the process?
推荐答案
我认为至少有三种方法可以解决您的问题（我已经进行了其他研究和考虑）：
I think that there are at least three way to solve your problem (I've done additional researches and considerations):
根据此要点，排序字段也可以与Postgres一起使用，不仅在mysql中，而且由于没有直接支持，因此有点棘手。正如您在要点讨论中所读到的，有不同的方法，但是我认为更简洁更简单：添加自定义 order by 子句！
According to this gist, ordering by field is possibile also with Postgres and not only in mysql, but it's a little bit tricky since it's not directly supported. As you can read in the gist discussion there are different approaches, but I think that the cleaner and simpler is the last: adding a custom order by clause!
ORDER BY (ID=10, ID=2, ID=56, ID=40) DESC
要在Grails中创建自定义SQL查询以返回特定类型的对象，可以遵循本教程
To create a custom SQL query in Grails that returns objects with a specific type, you can follow this tutorial
// only four id, for the sake of simplicity
def ids = [10, 2, 56, 40];
// get your events with your criteria
def events = Event.withCriteria {
'in'('id', ids)
fetchMode("someRelation", FetchMode.JOIN)
// a few more joins
setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)
}
// create an array that maps event.id -> sortedEventIndex
def indexToIdArray = [];
// event with id 2 is at index 1
// event with id 10 is at index 0
// ...
// [null, null, 1 , null, null, null, null, null, null, null, 0, ...]
ids.eachWithIndex{ num, idx -> indexToIdArray[$num] = $idx }
def sortedEvents = [];
events.each { ev -> sortedEvents[indexToIdArray[ev.id]] = ev }
这样，您已将 O （n），消耗一些额外的内存。
我不知道此解决方案是否真的比您的解决方案更好，但是您应该尝试一下。
In this way you have sorted in O(n), consuming some additional memory. I don't know if this solution really performs better than your but you should give it a try.
另请参见这篇有趣的文章有关如何在常规代码中查找性能问题。
See also this interesting article on how to find performance issue in your groovy code.
使用排序数据将未排序的事件列表返回给客户端，然后在客户端中对它们进行排序。如果您的应用程序客户端可以通过更多方式对事件列表进行排序，那么我认为该解决方案可能会很有用（每个排序操作仅在客户端进行）。
Return the unsorted list of events to the client with sorting data, then sort them in the client. If a client of your application can sort the events list in more ways, I think that this solution could be useful (every sorting action is done only client side).
由于这也是一个性能问题，因此还有其他可能的解决方案（取决于您的域/环境）：
Since this is a also a performance problem, there are other possible solutions (depends on your domain/environment):
- 急切地缓存所有内容，甚至包括有序结果
- 使用固定的点列表进行排序（如果我在p1附近，使用p1进行排序）
- 您的想法
我希望这会有所帮助。
这篇关于带有Criteria的Grails，或者一种按预定顺序排序的快速方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
