Firebase Filtering data with PHP

I'm using firebase latest library (4.15.1) I want to query like,

"SELECT * FROM `users` WHERE `email`='[email protected]' and firstanme='John'"

My Code is, but it's not working.

return $this->database->getReference($this->dbname)->hasChildren('email')->getValue();

This is my firebase data structure.

Solution 1:

hasChildren('email') might return a boolean value...

$email = $firebase
           ->getReference("users/{$uid}/profile/email")
           ->getValue();

and equalTo can be used to formulate the WHERE condition.

$user = $firebase->getReference("users/{$uid}")

           ->orderByChild("email")
           ->equalTo("[email protected]")

           ->limitToFirst(1)
           ->getSnapshot();

or one can also filter alike:

           ->orderByChild("email")
           ->startAt("[email protected]")
           ->endAt("[email protected]")

Solution 2:

The hasChildren('email') returns bool value and you cannot chain with ->getValue() method.

Solution 3:

The Firebase Realtime Database is a NoSQL database, not a relational database and not meant to be used as one - that's why queries like the one you are looking for will probably never be possible out of the box.

You can see all filtering and ordering capabilities at https://firebase.google.com/docs/database/rest/retrieve-data (in the Sections "Filtering Data", "Complex filtering" and "How Data is Ordered).

Where the PHP SDK adds a little bit of convenience is that it removes the limitation of the Firebase APIs returning unordered data (the following section is from the red box on https://firebase.google.com/docs/database/rest/retrieve-data#section-rest-filtering

Filtered data is returned unordered: When using the REST API, the filtered results are returned in an undefined order since JSON interpreters don't enforce any ordering. If the order of your data is important you should sort the results in your application after they are returned from Firebase.

The PHP SDK will post-process the returned data and order it as you expect it.

Martin Zeitler already gave some good tips on getting to result in his answer and comments (I recommend reading them, just one note: it's not kreait/the PHP SDK that provides just one criteria, it's the Firebase API).

However I do recommend one of the following approaches:

Restructure/denormalize the data so that it satisfies your requirements. Denormalization is a perfectly fine approach in a NoSQL database. If, for example, you need to find users by Name, create a new tree in which the key is the name and the children of that key are the userids.

Then, you can do another filtered query on your users tree to only return the users with the given IDs.

Use Firestore (https://firebase.google.com/docs/firestore/) - Firestore provides more querying capabilities than the Realtime Database

If you want to keep everything as it is, move the logic into your client application. Get all users (just one query) and then filter them out on your own, e.g.:

$allUsers = $firebase->getDatabase()
    ->getReference('users')
    ->getValue();

$filtered = array_filter($allUsers, function (array $userData) {
    $userEmail = $userData['profile']['email'] ?? '';
    $userName = $userData['profile']['firstname'] ?? '';

    return stripos($userEmail, 'mail.com') !== false
        && stripos($userName, 'john') !== false;
});

I haven't actually tested the code, but you should get the gist.