How to query multiple column using LIKE and OR condition in Firebase Firestore Database

Question

I am trying to run a Firestore query using LIKE and OR condition. I couldn't see any examples in the firestore docs too.

There was a section called Full-Text Search which recommend a Algolia Search.

Is there a way to achieve LIKE and OR condition in firestore query ?

Data Model

SQL Query

SELECT * FROM
   Booking
WHERE awbNumber LIKE %searchText%
OR referenceNumber LIKE %searchText%
OR shipperName LIKE %searchText%
OR receiverName LIKE %searchText%
OR origin LIKE %searchText%
OR destination LIKE %searchText%

Firestore Query using Angular Fire Library

commented out the code which are tried and not worked.

let query = this.db.collection(doc).ref.orderBy('id', 'desc');

if (searchText) {
        // query = query.(searchText).endAt(searchText + '\uf8ff');

        // query = query.orderBy('awbNumber').startAt(searchText).endAt(searchText + '\uf8ff');
        // query = query.orderBy('referenceNumber').startAt(searchText).endAt(searchText + '\uf8ff');
        // query = query.orderBy('additionalContacts').startAt(searchText).endAt(searchText + '\uf8ff');
        // query = query.orderBy('additionalLeaf').startAt(searchText).endAt(searchText + '\uf8ff');
        // query = query.orderBy('additionalWeights').startAt(searchText).endAt(searchText + '\uf8ff');
        // query = query.orderBy('deliveryOfficeAddress').startAt(searchText).endAt(searchText + '\uf8ff');
        // query = query.orderBy('destination').startAt(searchText).endAt(searchText + '\uf8ff');
        // query = query.orderBy('origin').startAt(searchText).endAt(searchText + '\uf8ff');
        // query = query.orderBy('receiverName').startAt(searchText).endAt(searchText + '\uf8ff');
        // query = query.orderBy('remarks').startAt(searchText).endAt(searchText + '\uf8ff');
        // query = query.orderBy('shipperName').startAt(searchText).endAt(searchText + '\uf8ff');

        // query = query.where('awbNumber == searchText');
        // query = query.where('referenceNumber', '==', searchText);
        // query = query.where('additionalContacts', '==', searchText);
        // query = query.where('additionalLeaf', '==', searchText);
        // query = query.where('additionalWeights', '==', searchText);
        // query = query.where('deliveryOfficeAddress', '==', searchText);
        // query = query.where('destination', '==', searchText);
        // query = query.where('origin', '==', searchText);
        // query = query.where('receiverName', '==', searchText);
        // query = query.where('remarks', '==', searchText);
        // query = query.where('shipperName', '==', searchText);
      }

query = query.limit(limit);

      return this.db.collection(doc, (ref) => query)
      .snapshotChanges()
        .pipe(
          map((actions) => actions.map(a => {
            const data = a.payload.doc.data() as any;
            const id = a.payload.doc.id;
            return { id, ...data, count };
          }))
        );

Is there any way to achieve the result that is mentioned in SQL query?

score 0 · Answer 1 · answered Jun 21 '20 at 14:41

Simple answer: no.

There is no OR operator when querying Firestore (there is some kind of it actually with the in and array-contains-any when you want to do OR on the same field. There is no LIKE operator.

If you really need that kind of query, Firestore may not be the best option

How to query multiple column using LIKE and OR condition in Firebase Firestore Database

1 Answers1