Query questions

[clibu]

I see I can query() using like which is case-insensitive however I can't see a way to perform a case-insensitive sort.

Is it possible to use our own custom query filter function. Something like filter(key, operator, compare) where operator would be a function we'd provide and you'd pass it key and compare. In nanoSQL you can add query functions.

Finally what is the best way to iterate all nodes in a path, one at a time which is performant and doesn't uses lots of memory (doesn't cache). ex. Like db.query('songs') to get every node, one at a time.

[appy-one]

I can't see a way to perform a case-insensitive sort.

Good point, this is currently not possible. I'll investigate if this can be added, perhaps also using collation specific sorting options.

Is it possible to use our own custom query filter function.

This was implemented in the early versions of AceBase, but I decided to remove it because of these reasons:

• it requires loading all child data for each node to run the callback, which can become very slow and memory intensive.
• it's not possible to do when using an acebase-client connected to an acebase-server because this would either require server roundtrips for each value (causing long lasting read locks), or server-side execution of the callback code (dangerous).

However, this was all before AceBase was able to run in the browser, so I will take a look at enabling this option again for standalone db's.

Finally what is the best way to iterate all nodes in a path, one at a time which is performant and doesn't uses lots of memory

You can use the reflection API to get all children and iterate yourself:

const collectionRef = db.ref('books');
const childInfo = await collectionRef.reflect('children', { limit: 0, skip: 0 }); // Gets ALL child keys

for (let i = 0; i < childInfo.list.length; i++) {
    const key = childInfo.list[i].key;
    
    // Get child data, but only include their title and description properties
    const snapshot = await collectionRef.child(key).get({ include: ['title', 'description'] });

    // Do something with it
    const book = snapshot.val();
    console.log(`Got book "${book.title}": "${book.description}"`);
}

Above solution also allows you to only load the data you are interested in, and you can stop iterating at will.

[clibu]

Thanks, all sounds good. I was surprised that there was no case insensitive sort.

nanoSQL has streaming queries which I use to iterate it's tables. ex.

nSQL("users").query("select").stream((row) => {
    // row received from stream
}, () => {
    // query complete
}, () => {
    // query error
})`

Another useful nanoSql feature is the ability to use its query engine with arrays of objects. ex.

nSQL().query("select").from([
    {id: 1, name: "bill"},
    {id: 2, name: "jeb"},
    {id: 3, name: "val"}
]).where(["name", "=", "jeb"].exec()

I thought these were worth mentioning.😀

[appy-one]

The stream option sounds cool, I'll think about implementing something similar.

By the way: I was browsing the reflection code just now and realized my example code to get all child keys can be done easier (and quicker) by passing limit: 0 to the reflect options:

const childInfo = await collectionRef.reflect('children', { limit: 0, skip: 0 }); // Gets ALL child keys

(I've edited my answer above)

[appy-one]

PS

Another useful nanoSql feature is the ability to use its query engine with arrays of objects. ex.

You can do this with plain javascript!

const results = [
    {id: 1, name: "bill"},
    {id: 2, name: "jeb"},
    {id: 3, name: "val"}
].filter(item =>  item.name === 'jeb')

[clibu]

Yes, true however you can use the full range of query operators and custom functions which makes it a lot more powerful and useful. I actually use it with custom query functions, but the query presents just the specific data I'm interested in to my function.

Again its nice, but not essential.

[appy-one]

Finally what is the best way to iterate all nodes in a path, one at a time which is performant and doesn't uses lots of memory

Update: I've implemented forEach methods in the DataReference and DataReferenceQuery classes that allow streaming iteration of all children of a reference or query results. I expect to be able to publish today.

This is what will become possible:

// Stream all books one at a time (loads all data for each book):
await db.ref('books').forEach(bookSnapshot => {
   const book = bookSnapshot.val();
   console.log(`Got book "${book.title}": "${book.description}"`);
});

// Now do the same but only load 'title' and 'description' of each book:
await db.ref('books').forEach(
   { include: ['title', 'description'] }, 
   bookSnapshot => {
      const book = bookSnapshot.val();
      console.log(`Got book "${book.title}": "${book.description}"`);
   }
);

// Query books, streaming the results one at a time:
await db.query('books')
 .filter('category', '==', 'cooking')
 .forEach(
   { include: ['title', 'description'] }, // (optional: only load these child keys of each book)
   bookSnapshot => {
      const book = bookSnapshot.val();
      console.log(`Found cooking book "${book.title}": "${book.description}"`);
   }
);

Callbacks have the option to stop iteration by returning false, or return a Promise so iteration will wait until it resolves.

[clibu]

Thanks for forEach() - looks really good. I'm sidetracked working on broader issues and will get back to AceBase specific work next week. I commented query'ing our own abjects above, in case you didn't see it.

[appy-one]

I see I can query() using like which is case-insensitive however I can't see a way to perform a case-insensitive sort.

Update: I've taken some time to look at case-insensitive sorting. The problem with this is that I'd also have to enable case-insensitivity in the matching and index engines, specifically for operators <, <=, >, >= and ==. If I'd do that, I would want to add collation specific settings in the same go (see Intl.Collator on MDN). This would allow dramatically changing the sorting rules for different languages and locales with each query. Awesome stuff, definitely something I'd want to implement...

I'll keep you updated.

[appy-one]

The new forEach methods in the DataReference and DataReferenceQuery classes are available in acebase from v1.4.0, and acebase-client v1.3.0

[AAAATTIEH]

@appy-one
You've said back in 2021

Is it possible to use our own custom query filter function.

This was implemented in the early versions of AceBase, but I decided to remove it because of these reasons:

it requires loading all child data for each node to run the callback, which can become very slow and memory intensive.
it's not possible to do when using an acebase-client connected to an acebase-server because this would either require server roundtrips for each value (causing long lasting read locks), or server-side execution of the callback code (dangerous).
However, this was all before AceBase was able to run in the browser, so I will take a look at enabling this option again for standalone db's.

Can you point out to the commit where custom filtering was enabled or is there a way to enable it. I'm using acebase without acebase-server