Queries startAt endAt

[arcticlula]

Do you think you can provide an example for using startAt or endAt in a query?
I'm trying to do an operation similar to a LIKE in SQL - for example i have a field name with the value chocolate in a collection. I'd like to be able to search for it with just cho or choc.

Common SQL Queries converted for the Firebase Database - The Firebase Database For SQL Developers #4
(Video from firebase's channel explaining in more detail)

[samuelgozi]

Hi and thanks for trying this lib.
This can be done, but probably not the way you thought.
I took the method from a StackOverflow answer and converted it to this lib:

const collection = db.reference('col');
const results = await collection.query({
    where: [
        ['foo', '>=', 'bar'],
        ['foo', '<', 'bar']
    ]
}).run()

You mentioned startAt and endAt. Both are used as cursors for a query and not filters.
I'll explain a little bit what that means. So lets say I perform a query that returns 1000 results. If I want to narrow the results I have to either be more specific with the filters, or just tell the query from which position in the matching documents to start returning them.

So let's say I have a document for each letter in the alphabet. If I want to return all the letters after "d" then I can do the following:

alphabet.query({
  offset: 4
}).run()

This will set the offset to start after the 4th document in that colleciton. And that will work, the query will return everything after "d". But what happens if I might have multiple entries for each letter? what If I have 3 times the letter "d" and I want to skip over the first two?
Well in that case I need a more specific way of telling the query from where to start. And for that, we got startAt.
startAt and endAt are references to specific documents. So If i want to start from the third "d" I would pass the reference to it in startAt as follows:

const thirdD = db.reference('alphabet/G5w6vY6M7mayBsai2FFv');

alphabet.query({
  startAt: thirdD
}).run()

The query results with start from that document specifically. This can be very helpful when working with pagination, infinite scrolling etc.

If you got any more questions feel free to ask!

[samuelgozi]

Just looked at the video you attached. It might be possible to do so with startAt, but will require some changes to the lib. However, it will probably be the same as the method I mentioned behind the scenes.

[arcticlula]

Hey thanks for looking it up and writing your answer in such detail! I had that same ideia that startAt and endAt were used for pagination and not as I intended to use them. It's just that using firebase to emulate some sql requests seems hacky sometimes (as seen in that video).
I'm gonna try your converted method when I get home!

[arcticlula]

Hi, it seems to be working (just got to figure out if i can search case-insensitive).
Just one quick observation, if the query doesn't match any collection it throws an error instead of returning an empty array. Is that the expected behaviour?

[samuelgozi]

@arcticlula I don't think supports case insensitive queries... This(and the solution in the video) seems to be stretching what's possible far enough, unfortunately(unless I missed something).

As for the error, no it shouldn't throw anything. Do you mind sharing the error(and possibly the response body of the request)? I'll try to provide a quick fix today.

[arcticlula]

I resolved by having an extra lower case field using toLowerCase (for example name: Chocolate, nameL: chocolate) and applying toLowerCase to my search too!

Regarding the error:

Error: Invalid Firestore Document
    at new Document (Document.js?2d8b:16)
    at eval (Query.js?4c57:226)
    at Array.map (<anonymous>)
    at Query.run (Query.js?4c57:226)

document.js:16

export class Document {
    constructor(rawDoc, db) {
        if (db === undefined)
            throw Error('Argument "db" is required but missing');
        if (!isRawDocument(rawDoc))
            **throw Error('Invalid Firestore Document');**

Query.js:226

async run() {
    return (await this.db.fetch(this.parentDocument.endpoint + ':runQuery', {
      method: 'POST',
      body: JSON.stringify(this)
    **})).map(result => new Document(result.document, this.db));**
  }

Probably an error creating the doc since the query didn't return any data.
Been using try catch to get around it:

try {
      const results = await suppliers.query({
        where: [
          ['nameL', '>=', name],
          ['nameL', '<', name + '\uf8ff']
        ]
      }).run()      
       return results
    }
    catch (error)  
       return []
    }

[samuelgozi]

Found the bug, apparently, when a query has no results, the firebase team thought that it would be a good idea to return an object that has the date of the query... I mean... It might have a reason, but why?

Anyways, make sure to update to version 0.7.1 to see the changes.

[arcticlula]

Thanks, will do!