在IndexedDB中，是否有一种方法可以进行排序的复合查询？

问题描述

说一张桌子有，名字，身份证，年龄，性别，教育等。身份证是关键，表格也是姓名，年龄和性别的索引。我需要所有年龄大于25岁的男学生，按照他们的名字排序。

这在mySQL中很容易：

  SELECT * FROM table WHERE age> 25 AND sex =MORDER BY name 
  

IndexDB允许创建一个索引并对查询进行排序在那个指数上。但它不允许多个查询，如年龄和性别。我发现了一个名为queryIndexedDB（https://github.com/philikon/queryIndexedDB）的小型库，它允许复合查询但不提供排序结果。

一种使用IndexedDB进行排序的复合查询的方法？

解决方案

复合查询一词在此答案中使用的是指在其WHERE子句中涉及多个条件的SQL SELECT语句。尽管索引数据库规范中没有提到这样的查询，但可以通过使用 keypath 创建索引来近似复合查询的行为，该索引由一组属性名称组成。

这与创建索引时使用多条记录标志完全无关。多项标志调整indexedDB如何通过单个数组属性创建索引。我们正在索引一组对象属性，而不是对象的单个数组属性的值。

创建索引

在此示例中，'name'，'gender'和'age'对应于存储在学生对象库中的学生对象的属性名称。

  //学生商店中的学生对象示例
 var foo = {
'name'： 'bar'，
'age'：15，
'gender'：'M'
}; 
 
函数myOnUpgradeNeeded（event）{
 var db = event.target.result; 
 var students = db.createObjectStore（'students'）; 
 var name ='males25'; 
 var keyPath = ['name'，'gender'，'age']; 
 students.createIndex（name，keyPath）; 
 
  

在索引上打开游标

然后你可以在索引上打开一个光标：

  var students = transaction.objectStore（'students'） ; 
 var index = students.index（'males25'）; 
 var lowerBound = ['AAAAA'，'male'，26]; 
 var upperBound = ['ZZZZZ'，'male'，200]; 
 var range = IDBKeyRange.bound（lowerBound，upperBound）; 
 var request = index.openCursor（range）; 
  

然而，由于我要解释的原因，这不会总是工作。

除此之外：使用范围参数openCursor或get是可选的。如果你没有指定范围，那么隐式地使用 IDBKeyRange.only 。换句话说，对于有界游标，您只需要使用 IDBKeyRange 。

基本索引概念

指数就像对象存储但不是直接可变的。相反，您在引用的对象库上使用CRUD（创建读取更新删除）操作，然后indexedDB自动级联对索引的更新。

理解排序是理解索引的基础。索引基本上只是一个特殊排序的对象集合。从技术上讲，它也被过滤掉了，但我马上就会谈到这一点。通常，当您在索引上打开游标时，您正按照索引的顺序进行迭代。这个顺序可能是，也可能与被引用的对象存储中的对象的顺序不同。顺序很重要，因为这样可以提高迭代的效率，并且允许自定义的下限和上限在索引特定顺序的上下文中才有意义。

索引中的对象在商店发生更改时进行排序。当您将对象添加到商店时，它将被添加到索引中的适当位置。排序归结为一个比较函数，类似于Array.prototype.sort，比较两个项目并返回一个对象是否小于另一个，大于另一个或相等。所以我们可以通过深入比较函数的细节来更好地理解排序行为。例如，这意味着'Z'小于'a'，并且字符串'10'大于字符串'020'。

使用规格定义的顺序比较不同类型的值例如，规范指定字符串类型值在日期类型值之前或之后。这些值包含什么并不重要，只是类型。

IndexedDB不会为您强制类型。你可以在这里射击自己。您通常不希望比较不同的类型。

具有未定义属性的对象不会出现在其keypath由一个或多个这些属性组成的索引中

正如我所提到的，索引可能并不总是包含引用对象存储中的所有对象。将对象放入对象存储中时，如果索引所基于的属性缺少值，则该对象不会出现在索引中。例如，如果我们有一个学生，我们不知道年龄，并将其插入学生商店，则该特定学生将不会出现在males25索引中。

还要注意null和空字符串之间的细微区别。一个空字符串不是缺少的值。具有空属性字符串的对象仍然可以出现在基于该属性的索引中，但如果该属性存在但未定义或不存在，则该索引不会出现在索引中。如果它不在索引中，则在将光标迭代到索引上时将看不到它。

创建时，必须指定数组键路径的每个属性。 IDBKeyRange

创建一个在范围内使用的下限或上限以便在该范围内打开光标时，必须为数组keypath中的每个属性指定一个有效值。否则，你会得到一些类型的Javascript错误（因浏览器而异）。例如，你不能创建一个范围，比如 IDBKeyRange.only（[undefined，'male'，25]），因为name属性是未定义的。

令人困惑的是，如果您指定错误的类型的值，例如 IDBKeyRange.only（['male'，25]），其中name未定义，你不会在上述意义上得到错误，但你会得到无意义的结果。

这是一个例外一般规则：您可以比较不同长度的数组。因此，从技术上讲，您可以省略范围内的属性，前提是您必须从数组的 end 中执行此操作，并且适当地截断数组。例如，您可以使用 IDBKeyRange.only（['josh'，'male']）。

-circuited array sorting

提供了一个显式排序数组的方法：

类型Array的值与Array的其他值进行比较，如下所示：

1. 设A为第一个Array值，B为第二个Array值。


2. 让长度为A的长度和B的长度中的较小者。如果A的第i个值小于B的第i个值，那么A就是小于
   的元素。
比B.略过其余的步骤。

3. 如果A的第i个值大于B的第i个值，那么A大于B.跳过其余的步骤。


4. 将i增加1.


5. 如果我不等于长度，请返回步骤4。否则继续下一步。如果A的长度小于B的长度，则A小于B.如果A的长度大于B的长度，则A大于B，否则A和B相等。

该步骤在第4步和第5步：跳过其余步骤。这基本上意味着如果我们比较两个数组的顺序，如[1，'Z']和[0，'A']，该方法只考虑第一个元素，因为在那个点1> 0。由于评估的短路（规范中的步骤4和5），从来没有绕过检查Z vs A.

所以，前面的例子不起作用。它实际上更像下面这样：

  WHERE（students.name> ='AAAAA'&&& amp; students.name < ='ZZZZZ'）|| 
（students.name> ='AAAAA'&& student.name< ='ZZZZZ'&& 
 students.gender> ='男性'&&性别< ='男性'）|| 
（students.name> ='AAAAA'&& student.name< ='ZZZZZ'&& 
 students.gender> ='男性'&&性别< ='男性&& 
 students.age> = 26&&& students.age< = 200）
  

如果您对SQL或一般编程中的此类布尔子句有任何经验，那么您应该已经认识到不一定涉及完整的条件集。这意味着你不会得到你想要的对象列表，这就是为什么你不能真正得到与SQL复合查询相同的行为。

处理短路

在当前实现中，您无法轻松避免这种短路行为。在最糟糕的情况下，您必须将存储/索引中的所有对象加载到内存中，然后使用您自己的自定义排序功能对集合进行排序。

有些方法可以最小化或避免一些短路问题：例如，如果您使用的是index.get（array）或index.openCursor（array），那么不会有短路 - 关心的问题。有整场比赛或不是整场比赛。在这种情况下，比较函数仅评估两个值是否相同，而不是一个是大于还是小于另一个。

其他要考虑的技巧：

• 重新排列keypath的元素从最窄到最宽。基本上在范围上提供早期钳位，以便切断一些不希望的短路结果。


• 将包装对象存储在使用专门定制属性的存储中，以便可以使用非数组keypath（非复合索引），或者可以使用不受短路行为影响的复合索引。
  
• 使用多个索引。这导致。注意这个链接是关于另一个非sql数据库的，但是相同的概念和解释适用于indexedDB，并且链接是一个合理的（冗长和复杂的）解释，所以我不在这里重复。


• indexedDB的创建者之一（规范和Chrome实现）最近建议使用cursor.continue：提供了一种快速而简单的方法来检查如何排序作品。例如：
  
  

    var a = ['Hello'，1]; 
   var b = ['World'，2]; 
   alert（indexedDB.cmp（a，b））; 
    

  indexedDB.cmp函数的一个很好的属性是它的签名与函数参数和。您可以轻松测试控制台中的值，而无需处理连接/模式/索引等等。此外，indexedDB.cmp是同步的，因此您的测试代码不需要涉及异步回调/承诺。

  
  

  Say a table has, name, ID, age, sex, education, etc. ID is the key and the table is also indexed for name, age and sex. I need all male students, older than 25, sorted by their names.

  This is easy in mySQL:

      SELECT * FROM table WHERE age > 25 AND sex = "M" ORDER BY name
  

  IndexDB allows creation of an index and orders the query based on that index. But it doesn't allow multiple queries like age and sex. I found a small library called queryIndexedDB (https://github.com/philikon/queryIndexedDB) which allows compound queries but doesn't provide sorted results.

  So is there a way to make a sorted compound query, while using IndexedDB?

  解决方案

  The term compound query as used in this answer refers to an SQL SELECT statement involving more than one condition in its WHERE clause. Although such queries are not mentioned in the indexedDB specification, you can approximate the behavior of a compound query by creating an index with a keypath that consists of an array of property names.

  This is completely unrelated to using the multi-entry flag when creating an index. The multi-entry flag adjusts how indexedDB creates an index over a single array property. We are indexing an array of object properties, not the values of a single array property of an object.

  Creating the index

  In this example, 'name', 'gender', and 'age' correspond to property names of student objects stored within the students object store.

  // An example student object in the students store
  var foo = {
    'name': 'bar',
    'age': 15,
    'gender': 'M'
  };
  
  function myOnUpgradeNeeded(event) {
    var db = event.target.result;
    var students = db.createObjectStore('students');
    var name = 'males25';
    var keyPath = ['name', 'gender', 'age'];
    students.createIndex(name, keyPath);
  }
  

  Opening a cursor on the index

  You can then open a cursor on the index:

  var students = transaction.objectStore('students');
  var index = students.index('males25');
  var lowerBound = ['AAAAA','male',26];
  var upperBound = ['ZZZZZ','male',200];
  var range = IDBKeyRange.bound(lowerBound, upperBound);
  var request = index.openCursor(range);
  

  However, for reasons I am about to explain, this won't always work.

  Aside: using a range parameter to openCursor or get is optional. If you do not specify a range, then IDBKeyRange.only is implicitly used for you. In other words, you only need to use IDBKeyRange for bounded cursors.

  Fundamental index concepts

  Indices are like object stores but are not directly mutable. Instead, you use CRUD (create read update delete) operations on the referenced object store, and then indexedDB automatically cascades updates to the index.

  Understanding sorting is fundamental to understanding indices. An index is basically just a specially sorted collection of objects. Technically, it is also filtered, but I'll touch on that in a moment. Generally, when you open a cursor on an index, you are iterating according to the index's order. This order could be, and probably is, different than the order of the objects in the referenced object store. The order is important because this allows iteration to be more efficient, and allows a custom lower and upper bound that only makes sense in the context of an index-specific order.

  The objects in the index are sorted at the time changes to the store occur. When you add an object to the store, it is added to the proper position in the index. Sorting boils down to a comparison function, similar to Array.prototype.sort, that compares two items and returns whether one object is less than the other one, greater than the other one, or equal. So we can understand sorting behavior better by diving into more details on comparison functions.

  Strings are compared lexicographically

  This means, for example, that 'Z' is less than 'a' and that the string '10' is greater than the string '020'.

  Values of different types are compared using a specification-defined order

  For example, the specification specifies how a string-type value comes before or after a date-type value. It does not matter what the values contain, just the types.

  IndexedDB does not coerce types for you. You can shoot yourself in the foot here. You generally never want to be comparing different types.

  Objects with undefined properties do not appear in indices whose keypath is comprised of one or more of those properties

  As I mentioned, indices may not always include all objects from the referenced object store. When you put an object into an object store, the object will not appear in the index if it has missing values for the properties upon which the index is based. For example, if we have a student where we don't know the age, and we insert this into the students store, the particular student will not appear in the males25 index.

  Remember this when you wonder why an object doesn't appear when iterating a cursor on the index.

  Also note the subtle difference between null and an empty string. An empty string is not a missing value. An object with an empty string for a property could still appear in an index based on that property, but will not appear in the index if the property is present but undefined or not present. And if it is not in the index, you won't see it when iterating a cursor over the index.

  You must specify each property of an array keypath when creating an IDBKeyRange

  You must specify a valid value for each property in the array keypath when creating a lower or upper bound to use in a range for when opening a cursor over that range. Otherwise, you will get some type of Javascript error (varies by browser). For example, you cannot create a range such as IDBKeyRange.only([undefined, 'male', 25]) because the name property is undefined.

  Confusingly, if you specify the wrong type of value, such as IDBKeyRange.only(['male', 25]), where name is undefined, you won't get an error in the above sense, but you will get nonsensical results.

  There is an exception to this general rule: you can compare arrays of different lengths. Therefore, you technically can omit properties from the range, provided that you do so from the end of the array, and that you appropriately truncate the array. For example, you could use IDBKeyRange.only(['josh','male']).

  Short-circuited array sorting

  The indexedDB specification provides an explicit method for sorting arrays:

  Values of type Array are compared to other values of type Array as follows:

  1. Let A be the first Array value and B be the second Array value.
  2. Let length be the lesser of A's length and B's length.
  3. Let i be 0.
  4. If the ith value of A is less than the ith value of B, then A is lessthan B. Skip the remaining steps.
  5. If the ith value of A is greater than the ith value of B, then A is greater than B. Skip the remaining steps.
  6. Increase i by 1.
  7. If i is not equal to length, go back to step 4. Otherwise continue to next step.
  8. If A's length is less than B's length, then A is less than B. If A's length is greater than B's length, then A is greater than B. Otherwise A and B are equal.

  The catch is in steps 4 and 5: Skip the remaining steps. What this basically means is that if we are comparing two arrays for order, such as [1,'Z'] and [0,'A'], the method only considers the first element because at that point 1 is > 0. It never gets around to checking Z vs A because of short-circuited evaluation (steps 4 and 5 in the spec).

  So, the earlier example is not going to work. It actually works more like the following:

  WHERE (students.name >= 'AAAAA' && students.name <= 'ZZZZZ') ||
  (students.name >= 'AAAAA' && students.name <= 'ZZZZZ' &&
  students.gender >= 'male' && students.gender <= 'male') ||
  (students.name >= 'AAAAA' && students.name <= 'ZZZZZ' &&
  students.gender >= 'male' && students.gender <= 'male' &&
  students.age >= 26 && students.age <= 200)
  

  If you have any experience with such Boolean clauses in SQL or in general programming, then you already should recognize how the full set of conditions are not necessarily involved. That means you will not get the list of objects you want, and this is why you cannot truly get the same behavior as SQL compound queries.

  Dealing with short-circuiting

  You cannot easily avoid this short-circuiting behavior in the current implementation. In the worst case you have to load all objects from the store/index into memory and then sort the collection using your own custom sorting function.

  There are ways to minimize or avoid some of the short-circuiting issues:

  For example, if you are using index.get(array) or index.openCursor(array), then there is no short-circuiting concern. There is either an entire match or not an entire match. In this case, the comparison function is only evaluating whether two values are the same, not whether one is greater than or less than the other.

  Other techniques to consider:

  • Rearrange the elements of the keypath from narrowest to widest. Basically provide early clamps on ranges that cut off some of the unwanted results of short-circuiting.
  • Store a wrapped object in a store that uses specially customized properties so that it can be sorted using a non-array keypath (a non-compound index), or, can make use of a compound index that is not affected by the short-circuiting behavior.
  • Use multiple indices. This leads to the exploding index problem. Note this link is about another no-sql database, but the same concepts and explanation applies to indexedDB, and the link is a reasonable (and lengthy and complicated) explanation so I am not repeating it here.
  • One of the creators of indexedDB (the spec, and the Chrome implementation) recently suggested using cursor.continue: https://gist.github.com/inexorabletash/704e9688f99ac12dd336

  Testing with indexedDB.cmp

  The cmp function provides a quick and simple way to examine how sorting works. For example:

  var a = ['Hello',1];
  var b = ['World',2];
  alert(indexedDB.cmp(a,b));
  

  One nice property of the indexedDB.cmp function is that its signature is the same as the function parameter to Array.prototype.filter and Array.prototype.sort. You can easily test values from the console without dealing with connections/schemas/indices and all that. Furthermore, indexedDB.cmp is synchronous, so your test code does not need to involve async callbacks/promises.

  这篇关于在IndexedDB中，是否有一种方法可以进行排序的复合查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！