Cloud Firestore - How to get relational data from two collections?

Solution 1:

Is there a Join function available?

Unfortunately, there is no JOIN query in Firestore. Queries in Firestore are shallow: they only get items from the collection that the query is run against. There is no way to get documents from a top-level collection and other collections or sub-collections in a single query. Firestore doesn't support queries across different collections in one go. A single query may only use properties of documents in a single collection. So the most simple solution I can think of would be to use a database structure that looks similar to this:

Firestore-root
   |
   --- playlists (collection)
   |     |
   |     --- playListId (document) //The unique id of the play list
   |           |
   |           --- name: "playlist1"
   |
   --- songs (collection)
        |
        --- playListId (document) //The same as the above playListId
               |
               --- playListSongs
                     |
                     --- songId
                          |
                          --- title: "song1"

In order to display all playlists, just attach a listener to the playlists reference and get all playlist objects. If you want to get all songs that correspond to a particular playlist, just attach a listener to songs\playListId\playListSongs and get all song objects.

Regarding the count of all songs that correspond to a playlist, I recommend you see my answer from this post, where I have explained what you can achieve this. So according to the last part of my answer, your Firebase Realtime Database structure should look like this:

Firebase-Realtime-Database-root
    |
    --- playlists
          |
          --- playListIdOne: numberOfSongs
          |
          --- playListIdTwo: numberOfSongs

Edit:

I can't say I've understood it fully especially because the first answer involves Firestore and the second involves Firebase Realtime Database.

I gave you this solution because if you want to use Cloud Firestore to count and update elements every time a song is added or deleted, you'll be charged by Firestore for every write/delete operation. Firebase Realtime Database has another pricing plan, so you'll not be charged for that. See Firestore pricing Please read again till the end, my answer from this post.

Also, I couldn't really get the procedure for calculating the count of songs.

This is how you can get the number of songs from Firestore and write it to the Firebase Realtime Database:

rootRef.collection("songs").document(playListId).collection("playListSongs")
.get().addOnCompleteListener(new OnCompleteListener<QuerySnapshot>() {
    @Override
    public void onComplete(@NonNull Task<QuerySnapshot> task) {
        if (task.isSuccessful()) {
            Log.d("TAG", task.getResult().size() + "");
            DatabaseReference rootRef = FirebaseDatabase.getInstance().getReference();
            DatabaseReference playListIdRef = rootRef.child("playlists").child(playListId);
            playListIdRef.setValue(task.getResult().size());
            
        } else {
            Log.d(TAG, "Error getting documents: ", task.getException());
        }
    }
});

And this is how you can read:

DatabaseReference rootRef = FirebaseDatabase.getInstance().getReference();
DatabaseReference playListIdRef = rootRef.child("playlists").child(playListId);
ValueEventListener valueEventListener = new ValueEventListener() {
    @Override
    public void onDataChange(DataSnapshot dataSnapshot) {
            long playListIdOne = dataSnapshot.getValue(String.Long);
            Log.d(TAG, "playListIdOne: " + playListIdOne);
    }

    @Override
    public void onCancelled(@NonNull DatabaseError databaseError) {
        Log.d(TAG, databaseError.getMessage());
    }
};
playListIdRef.addListenerForSingleValueEvent(valueEventListener);

Secondly, as per your suggested structure, how would I get a list of all songs across all playlists?

In this case, you should create another collection, named allSongs. The additional collection in your database should look like this:

Firestore-root
   |
   --- allSongs
         |
         --- songId
              |
              --- //song details

This practice is called denormalization and is a common practice when it comes to Firebase. For a better understanding, I recommend you see this video, Denormalization is normal with the Firebase Database. It is for Firebase Realtime Database but same rules apply to Cloud Firestore.

Also, when you are duplicating data, there is one thing that you need to keep in mind. In the same way you are adding data, you need to maintain it. In other words, if you want to update/delete an item, you need to do it in every place that it exists.

Keeping in mind that one same song can be found in more than one playlists.

It doesn't matter because each song has a different id.