How do I do a first-in-first-out system in Flutter/Firestore?

Question

I'm building an app very similar to an inventory management system. My roadblock right now is the number of queries allowed in firestore, because it doesn't allow multiple .where()'s.

So using the inventory management system concept, we create a collection:

We'll Call it 'InventoryCollection'. Let's say it has 3 fields:

- ItemID
- ItemName
- DateAdded

Each document in 'InventoryCollection' has a sub collection called 'PurchaseHistory': It has fields:

- PurchaseID (auto generated document id)
- ItemID (from parent collection document)
- PurchaseDate
- Quantity

Dilemma: How to deduct the Quantity of an Item using the concept of first in first out? So let's say Item1 has 4 PurchseHistory data. and let's say one of them has 0 quantity because they were already sold.

- PurchaseID: 12340 
- ItemID: 00001
- PurchaseDate: 01-feb-2021
- Quantity: 0

- PurchaseID: 12341(auto generated document id)
- ItemID: 00001
- PurchaseDate: 02-feb-2021
- Quantity: 10

- PurchaseID: 12342
- ItemID: 00001
- PurchaseDate: 03-feb-2021
- Quantity: 5

- PurchaseID: 12343
- ItemID: 00001
- PurchaseDate: 03-feb-2021
- Quantity: 15

I need to find a way to retrieve the PurchaseId 12341 to get the "First In" using the parameters below. I can then figure out a way to do the "First Out" on my own:

get all PurchaseHistory Documents with the ItemID: 00001
only get documents that has 1 or more Quantity
sort the data by PurchaseDate so that the first result will be the entry from feb 2
limit query result by 1 to only get the feb 2 document
get the purchaseID of that document to be used in the method below:

void _deductQuantity(String itemId, String PurchaseID {
  final itemCollection = FirebaseFirestore.instance.collection('InventoryCollection').doc(ItemId).collection('PurchaseHistory').doc(purchaseID);
  itemCollection.update({
    'Quantity':  _quantity - int.parse(_editItemQuantityController.text.trim()),
  });
}

In my mind, the query looks like this:

String purchaseIdStream;


   final docRef = FirebaseFirestore.instance.collection('InventoryCollection').doc(ItemId).collection('PurchaseHistory').doc();
    
    purchaseIdStream = await docRef
        .where('ItemId', isEqualTo: ItemId)
        .where('Quantity', isGreaterThan: 0)
        .orderBy('PurchaseDate')
        .get();
     var _id = purchaseIdStream.ref.documentID;

I fully understand firebase does not allow to orderby, if you don't have the field in a .where for purchasedate. So it's definitely not possible to do it on the code above. since I already have 2 where's and a 3rd where is not possible.

I understand that I have to do server side filtering instead of multiple .where's however, I am only a frontend dev. My understanding in backend concepts are very limited. So I need terrible guidance on how to implement this in dart/flutter.

Any advice will be highly appreciated. Thank you!

user_11566145 · Answer

First off, the .where() attribute is only for CollectionReference, not DocumentReference. Remove the .doc(ItemId) in docRef. I think it should work after that.

If it doesn't, get the data and sort it manually, like this:

final purchaseIdList =
    await docRef.where('ItemId', isEqualTo: ItemId).where('Quantity', isGreaterThan: 0).get();
purchaseIdList.docs.sort((QueryDocumentSnapshot a, QueryDocumentSnapshot b) =>
    (a.data()['PurchaseHistory']['PurchaseDate'] as String)
        .compareTo(b.data()['PurchaseHistory']['PurchaseDate'] as String));
final fifoId = purchaseIdList.docs[0].get('PurchaseId');

If I'm missing something, do tell. I'll edit the answer.

user_10451311 · Answer

The documentation says that if a field is used in orderBy, it also should be used in the first where.

citiesRef.where("population", ">", 100000).orderBy("population").limit(2);

user_2668529 · Answer

The answer is to use where for itemId and quantity, then do the rest of the filters in Dart:

var listOfDocs = await docRef
    .where('ItemId', isEqualTo: ItemId)
    .where('Quantity', isGreaterThan: 0)
    .get();

docs.sort((prev, next) => (prev.data()['PurchaseDate'] as Timestamp)
    .compareTo(next.data()['PurchaseDate'] as Timestamp));

final desiredDocId = docs.first.id;

print(desiredDocId);

How do I do a first-in-first-out system in Flutter/Firestore?

3 Answers