Combining firebase/firestore queries in AB to workaround missing OR functionality in Firestore

Hi David,

I am making good progress on Firebase/Firestore but have hit a small obstacle which I am hoping that you may be able to help me with.

When querying the firestore data you can add additional query conditions (AND) by just adding additional.where statements into the query. However, Firestore does not currently support (OR) conditions If you take a look at the code below which runs on view show you can see that I have two mutually exclusive queries (with one of them commented out right now).

BlockApp BlockedText "Syncing data..." GetOption "dockitvesseltoken" "vtoken" GetOption "dockitusertoken" "usertoken" StartJS var db = firebase.firestore(); window.App.RootScope.Notes_Report.Data = []; //db.collection("notes").where("VesselID", "==",window.App.RootScope.vtoken).where("Shared", "==","checked").orderBy("Created","asc").get().then(function(querySnapshot) { db.collection("notes").where("VesselID", "==",window.App.RootScope.vtoken).where("Shared", "==","unchecked").orderBy("Created","asc").get().then(function(querySnapshot) { querySnapshot.forEach(function(doc) { window.App.RootScope.Notes_Report.Data.push(doc.data()); }); blockUI.stop(); $scope.$apply(); }); EndJS

I have found that one solution to the missing OR functionality is to create two more queries and bind them separately to the UI.(combine the queries). Here is some code that I ran across:

// Retrieve cities from California db.collection("cities").where("state", "==", "CA") .onSnapshot(function(querySnapshot) { querySnapshot.forEach(function(doc) { // binded to the UI allItems.push(doc.data().name); }); }); // Retrieve cities from Colorado db.collection("cities").where("state", "==", "CO") .onSnapshot(function(querySnapshot) { var cities = []; querySnapshot.forEach(function(doc) { // binded to the UI allItems.push(cities); }); });

Any chance you could give me a hand building the syntax to combine the two queries that are in the first code block above?

Thanks!

Pete

Hello Peter,

Sorry, but I need to ask... what is the problem exactly and what is the expected results? We are talking about fill a Report's Data variable? Are you try to fill it and get some error? There is possible to execute a callback or function after all the queries are executed / returned? If so... maybe it's possible to save the data in certain variable and use it in the "final callback" function. Maybe it's possible to just fill the Report's Data variable "step by step", when the queries finished... I hope that some of these questions can help to finally solve the problem!

---

Hi David, I just needed to focus on it myself a bit. For any other potential Firebase users, what I needed to do to handle 'OR' queries is to run one query and push the query results to the Report.Data. Then run a second query to get the next 'OR' data, assign it to a variable and push variable to the same report. Here is the js code that pulled in any shared notes for my vessel plus any notes that were not shared but were created by my user.StartJS var db = firebase.firestore(); window.App.RootScope.Notes_Report.Data = []; db.collection("notes").where("VesselID", "==",window.App.RootScope.vtoken).where("Shared", "==","checked").orderBy("Created","asc").get().then(function(querySnapshot) { querySnapshot.forEach(function(doc) { window.App.RootScope.Notes_Report.Data.push(doc.data());});$scope.$apply();}); db.collection("notes").where("VesselID", "==",window.App.RootScope.vtoken).where("UserID", "==",window.App.RootScope.usertoken).where("Shared", "==","unchecked").orderBy("Created","asc").get().then(function(querySnapshot) { var notes = []; querySnapshot.forEach(function(doc) { window.App.RootScope.Notes_Report.Data.push(notes);});$scope.$apply();}); blockUI.stop(); EndJS Cheers, Pete

Hello Peter,

So, if I understand well, what you are doing is to share some code with us? Everything working? :-)

---

Hi David, I had believed that everything was working although at the time I didn't have enough data in the database to properly test. It turns out that the code didn't pull in all of the data and the solution was to just run the two queries sequentially. The proper working code is as follows if anyone is interested: StartJS var db = firebase.firestore(); window.App.RootScope.Notes_Report.Data = []; db.collection("notes").where("VesselID", "==",window.App.RootScope.vtoken).where("Shared", "==","checked").orderBy("Created","asc").get().then(function(querySnapshot) { querySnapshot.forEach(function(doc) { window.App.RootScope.Notes_Report.Data.push(doc.data());});$scope.$apply();}); db.collection("notes").where("VesselID", "==",window.App.RootScope.vtoken).where("UserID", "==",window.App.RootScope.usertoken).where("Shared", "==","unchecked").orderBy("Created","asc").get().then(function(querySnapshot) { var notes = []; querySnapshot.forEach(function(doc) { //window.App.RootScope.Notes_Report.Data.push(notes);});$scope.$apply();}); window.App.RootScope.Notes_Report.Data.push(doc.data());});$scope.$apply();}); blockUI.stop(); EndJS

The only issue that I have right now is with the returned sort order. Because the orderBy is defined in each query, I end up with two separate and distinct sort orders rather than a consolidated sort order for all returned data. I have a lead on how to fix this issue and will report back here if I find a solution.

I have to say that after a prolonged painful period of trying different solutions for handling the offline first, account synchronized data with both CouchDB/PouchDB and a home grown solution using my own back end and rest services I am finally making good progress with Firebase and the variety of solutions that Firebase offers seems to be a natural fit with AB for developing more complex datacentric apps.

Hello Peter,

Maybe it's not possible, but, you can consider some server's help too. So, for example, maybe it's possible to perform queries over Firestore from the app's server, so the server can perform at least some kind of "complex queries", returning to the app the appropriate data, already ordered, etc.

If not, then we are talking to made the work in the client side, and, maybe it's possible too, but don't feeding the Report until all the queries become executed, so we have the queries' data, and therefore maybe can do some kind of operation with it, before feed the Report's data.

---

Hi,

It looks like I am going to need to deal with this on the client side. I am now looking at the Report sample that uses a select tool to set the sort order field "ArrayGetItem "[Select1.Items]" "[Select1.ItemIndex]" "[Report1.Order]""

Would it be an option for me to somehow set the Notes_Report.Order to the JSON source object field "Created" after I have retrieved the two queries but before I display the report?

Pete

Looks like it was as simple as adding "SetVar "[Notes_Report.Order]" "Created" "String"" after the JS block in the view show code.

Hello Peter,

If the Report Order is enough... it's enough... in other case we must work with the data before assign to the Report, ordering the variable in the proper way, maybe by fill an Array variable with some records' fields... and order that Array variable (using Javascript), then use that list of fields to order the entire records... something like that... sounds possible... but if the Report Order is enough... maybe we no need to go ahead.

---

Well I still have a bunch of other objects to deal with now such as to do lists, checklists, maintenance logs etc... so I will keep your words in mind if I run into any other sorting issues on multiple queries with those objects but so far so good.

Thanks as always, Pete

Hello Peter,

Don't hesitate to post your questions: we try our best in order to help! :-)

---