lundi 31 juillet 2017

Firebase: How to structure for Multiple WHERE IN Query

I am using Firebase in my small, side projects for almost a year and totally love it. But couldn't use it for large projects that require complex queries, because of single orderByChild() limitation.

Still thinking of ways to make use of Firebase for the multi where clause scenarios. In one my main project that we developed in PHP, we have a query as below:

SELECT * 
FROM  `masterEvents` 
WHERE causeId IN ( 1, 2, 3, 4, 5, 6, 7, 8 ) 
AND timeId IN (1, 2, 3, 4)
AND localityID IN (1, 2, 3, 4, 5)

Above query. filters event that happens in certain localities, happens in specific time blocks and covered under given causes.

  • Localities can be 40 in number
  • Causes will be around 20
  • Time will be 21

Solution 1: All 3 filter keys as children

{
    "eventIndex": {
        "ev1": {
            "name": "Event 1",
            "location": 2,
            "cause": 3,
            "time": 5,
        },
        "ev2": {
            "name": "Event 2",
            "location": 5,
            "cause": 2,
            "time": 1,
        },
        "ev3": {
            "name": "Event 3",
            "location": 26,
            "cause": 12,
            "time": 18,
        }
    }
}

forEach(location in selectedLocationArray) {
    firebase.database().ref("eventIndex").orderByChild("location").equalTo(location).on("value", snap => {
        // loop through all events and filter them based on selectedCauseArray and selectedTimeArray
    });
}

Solution 2: 1 Filter key in node path and 2 filter keys as children

{
    "eventIndex": {
        "location1": {
            "ev1": {
                "name": "Event 1",
                "cause": 2,
                "time": 1,
            },
            "ev2": {
                "name": "Event 2",
                "cause": 12,
                "time": 18,
            }
        },
        "location2": {
            "ev4": {
                "name": "Event 4",
                "cause": 2,
                "time": 1,
            }
        },
        "location3": {
            "ev8": {
                "name": "Event 9",
                "cause": 2,
                "time": 1,
            },
            "ev9": {
                "name": "Event 9",
                "cause": 12,
                "time": 18,
            }
        }
    }
}

forEach(location in selectedLocationArray) {
    forEach(cause in selectedCauseArray) {
        firebase.database().ref("eventIndex/location" + location).orderByChild("cause").equalTo(cause).on("value", snap => {
            // loop through all events and filter them based on selectedTimeArray
        });
    }
}

Solution 3: 2 Filter key in node path and 1 filter keys as children

Which of the above will be good efficient solution that I could take? Thanks :-)

PS: Code can be treated as Pseudo code to give an idea of the logic, rather than actual code.




Aucun commentaire:

Enregistrer un commentaire