Skip to content

Latest commit

 

History

History
385 lines (297 loc) · 9.66 KB

queries.md

File metadata and controls

385 lines (297 loc) · 9.66 KB
Raw

Queries

You can either query the database at a given time or listen to changes for display update or data manipulation.

Let's consider the following data

// Store some objects
int key1, key2, key3;
await db.transaction((txn) async {
  key1 = await store.add(txn, {'name': 'fish'});
  key2 = await store.add(txn, {'name': 'cat'});
  key3 = await store.add(txn, {'name': 'dog'});
});

Get by key

You can get a record by key

// Read by key
expect(await store.record(key1).get(db), {'name': 'fish'});

// Read 2 records by key
var records = await store.records([key2, key3]).get(db);
expect(records[0], {'name': 'cat'});
expect(records[1], {'name': 'dog'});

Modify a read result

Records you get are immutable/read-only. When using map, it you want to use the resulting map as a base for a new object for modification and creation, you should first clone the value:

import 'package:sembast/utils/value_utils.dart';

 // Read by key
var value = await store.record(key1).get(db);

// read values are immutable/read-only. If you want to modify it you
// should clone it first

// the following will throw an exception
value['name'] = 'nice fish'; // Will throw!

// clone the resulting map for modification
var map = cloneMap(value);
map['name'] = 'nice fish';

// map is ready to be stored

Filtering and sorting

Filtering and sorting can be done on any field

// Look for any animal "greater than" (alphabetically) 'cat'
// ordered by name
var finder = Finder(
  filter: Filter.greaterThan('name', 'cat'),
  sortOrders: [SortOrder('name')]);
var records = await store.find(db, finder: finder);

expect(records.length, 2);
expect(records[0]['name'], 'dog');
expect(records[1]['name'], 'fish');

Simple filter

You can use the Filter class to filter records. Simple filter are available:

  • Filter.equals
  • Filter.notEquals
  • Filter.isNull
  • Filter.noNull
  • Filter.lessThan
  • Filter.lessThanOrEquals
  • Filter.greaterThan
  • Filter.greaterThanOrEquals
  • Filter.inList
  • Filter.matches
  • Filter.matchesRegExp
  • Filter.custom (Custom filter, use with caution and do not modify record data as it provides a raw access to the record internal value for efficiency.)

Composite filters

  • Filter.and
  • Filter.or
  • Filter.not

You can combine multiple filters using the operators & and |:

var filterAnd = Filter.greaterThan(Field.value, "hi") &
    Filter.lessThan(Field.value, "hum");
var filterOr = Filter.lessThan(Field.value, "hi") |
    Filter.greaterThan(Field.value, "hum");

If you have more than two filters, you can also use Filter.or and Filter.and:

var filter = Filter.and([
  Filter.greaterThan(Field.value, "hi"),
  Filter.lessThan(Field.value, "hum"),
  Filter.notEquals(Field.value, "ho"),
]);

Example

Let says that you want to find a given searchText in either of the fields clientName or companyName AND the date must be in 2023, and sort the result by date, you could write a filter like this:

var startTimestamp = Timestamp.fromDateTime(DateTime(2023));
var endTimestamp = Timestamp.fromDateTime(DateTime(2024));
var regExp = RegExp(searchText, caseSensitive: false);
var filter = Filter.and([
  Filter.or([
    Filter.matchesRegExp(
      'clientName',
      regExp,
    ),
    Filter.matchesRegExp(
      'companyName',
      regExp,
    ),
  ]),
  Filter.greaterThanOrEquals('date', startTimestamp),
  Filter.lessThan('date', endTimestamp),
]);
var finder = Finder(filter: filter, sortOrders: [SortOrder('date')]);

Finding first

You can limit your query to the first element found

// Find the first record matching the finder
var record = await store.findFirst(db, finder: finder);
// Get the record id
var recordId = record.key;
// Get the record value
var recordValue = record.value;

Modify a record found

Records you get are immutable/read-only. When using map, it you want to use the resulting map as a base for a new object for modification and creation, you should first clone the value:

import 'package:sembast/utils/value_utils.dart';

// find a record
var record = await store.findFirst(db, finder: finder);
          
// record snapshot are read-only. 
// If you want to modify it you should clone it
var map = cloneMap(record.value);
map['name'] = 'nice dog';

// map is ready to be stored

Sorting by key

Records can be sorted by key using the special Field.key field:

// Look for the last created record
var finder = Finder(sortOrders: [SortOrder(Field.key, false)]);
var record = await store.findFirst(db, finder: finder);

expect(record['name'], 'dog');

Sorting/querying on nested field

Assuming you have the following record

{
  "name": "Silver",
  "product": {
    "code": "1F8"
  }
}

You can order on product/code

var finder = Finder(sortOrders: [SortOrder('product.code')]);

Or query on it

var finder = Finder(filter: Filter.equals('product.code', 'AF8'));

Using boundaries for paging

start and end can specify a start and end boundary, similar to firestore

// Look for the one after `cat`
var finder = Finder(
  sortOrders: [SortOrder('name', true)],
  start: Boundary(values: ['cat']));
var record = await store.findFirst(db, finder: finder);
expect(record['name'], 'dog');

The boundary can be a record. If values is used, the number of values should match the number of sort orders

// Our shop store
var store = intMapStoreFactory.store('shop');

await db.transaction((txn) async {
  await store.add(txn, {'name': 'Lamp', 'price': 10});
  await store.add(txn, {'name': 'Chair', 'price': 10});
  await store.add(txn, {'name': 'Deco', 'price': 5});
  await store.add(txn, {'name': 'Table', 'price': 35});
});

// Look for object after Chair 10 (ordered by price then name) so
// should the the Lamp 10
var finder = Finder(
  sortOrders: [SortOrder('price'), SortOrder('name')],
  start: Boundary(values: [10, 'Chair']));
var record = await store.findFirst(db, finder: finder);
expect(record['name'], 'Lamp');

// You can also specify to look after a given record
finder = Finder(
  sortOrders: [SortOrder('price'), SortOrder('name')],
  start: Boundary(record: record));
record = await store.findFirst(db, finder: finder);
// After the lamp the more expensive one is the Table
expect(record['name'], 'Table');

Filtering using RegExp patten

Records can be filtered using regular expressions

// Look for any name stating with f (i.e. fish, frog...)
var finder = Finder(filter: Filter.matches('name', '^f'));
var record = await store.findFirst(db, finder: finder);

expect(record['name'], 'fish');
// Look for any name ending with og (i.e. dog, frog...)
var finder = Finder(filter: Filter.matches('name', r'og$'));
var record = await store.findFirst(db, finder: finder);

expect(record['name'], 'dog');
// Look for any name containing 'is' (fish matches)
var finder = Finder(filter: Filter.matches('name', 'is'));
var record = await store.findFirst(db, finder: finder);

expect(record['name'], 'fish');

Filtering list items

Filter.equals and Filter.matches can also look for list items if a field is a list using the anyInList option. It will match if one item in the list matches the criteria.

// Look for record with at least one category stating with f (i.e. food...), 
// if `categories` field is a list with text elements
var finder = Finder(filter: Filter.matches('categories', '^f', anyInList: true));
var record = await store.findFirst(db, finder: finder);

You can also access fields using index.

{
  "name": "Silver",
  "tag": ['good', 'heavy'],
}

You can order on a given index tag (here 0 for the first item)

var finder = Finder(sortOrders: [SortOrder('tag.0')]);

For Filter.equals and Filter.matches, you can also specify to look for any item in any list.

Filter.equals('tag.@', 'heavy');

is equivalent to

Filter.equals('tag', 'heavy', anyInList: true);

You imagine more complex example, Let's consider the following data:

[
  {
    'name': 'Lamp',
    'attributes': [
      {'tag': 'furniture'},
      {'tag': 'plastic'}
    ]
  },
  {
    'name': 'Chair',
    'attributes': [
      {'tag': 'wood'},
      {'tag': 'furniture'}
    ]
  },
]

You can filter for any records that has the furniture attribute using the following filder:

Filter.equals('attributes.@.tag', 'furniture');

Aggregate functions

There is no now convenient sum or average functions. However, data being in memory getting all the records and performing operation on the results does not cost much. example below to compute the sum and average of the fied age:

var store = intMapStoreFactory.store();
await store.addAll(db, [
  {'name': 'cat', 'age': 13},
  {'name': 'dog', 'age': 12},
  {'name': 'rabbit', 'age': 8}
]);
var ages = (await store.find(db)).map((snapshot) => snapshot['age']);

// Compute sum
var sum = ages.reduce((value, element) => value + element);
// Compute average
var average = sum / ages.length;

Synchronous API

In general, I don't recommend using the synchronous API as complex queries can block the UI.

As of 3.4.8-1, get by keys can now be synchronous (since the implementation is basically reading from a map).

  • RecordRef.getSync()
  • RecordRef.getSnapshotSync()
  • RecordRef.existsSync()
  • RecordsRef.getSnapshotsSync()
  • RecordsRef.getSync()

As of 3.4.9-1, All read access is allowed synchronously:

  • StoreRef.countSync();
  • StoreRef.findKeysSync();
  • StoreRef.findKeySync();
  • StoreRef.findSync();
  • StoreRef.findFirstSync();
  • QueryRef.countSync();
  • QueryRef.getSnapshotsSync();
  • QueryRef.getSnapshotSync();