Firestore replicating a SQL Join for noSQL and Flutter

Question

I realise there is many questions in regards to replicating joins with NoSql document databases such as FireStore, however i'm unable to find a thorough solution utilising Dart/Flutter with FireStore.

I have done some research i feel that in the following example i would be looking for a 'many to many' relationship (please correct me if this is wrong) as there may be a future need to look at all profiles as well as all connections.

In firebase, i have two root level collections (profile & connection):

profile
    > documentKey(Auto Generated)
         > name = "John Smith"
         > uid = "xyc4567"

    > documentKey(Auto Generated)
         > name = "Jane Doe"
         > uid = "abc1234"

    > documentKey(Auto Generated)
         > name = "Kate Dee"
         > uid = "efg8910"



connection
    > documentKey(Auto Generated)
         > type = "friend"
         > profileuid = "abc1234"
         > uid = "xyc4567"

    > documentKey(Auto Generated)
         > type = "family"
         > profileuid = "abc1234"
         > uid = "efg8910"

For this example the 'connection' documents have been created hypothetically for the user John Smith (uid: xyc4567) when he connected to Jane Doe (uid: abc1234) and Kate Dee (uid: efg8910).

Here is the relational SQL i'm looking to replicate to show a list of profiles which John Smith has connected with:

Select * FROM profile, connection 
WHERE profile.uid = connection.profileuid 
AND profile.uid = "xyc4567"

In flutter my flutter app i have a fireStore query starting point:

stream: Firestore.instance.collection('profile')
.where('uid', isEqualTo: "xyc4567").snapshots(),

Obviously it only returns from one collection. How do i join the collections in a many to many relationship?

Solution 1

Unfortunately, there is no JOIN clause in Cloud Firestore nor in others NoSQL databases. In Firestore queries are shallow. This means that they only get items from the collection that the query is run against. There is no way to get documents from two top-level collection 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 is to query the database to get the uid of a user from the profile collection. Once you have that id, make another database call (inside the callback), and get the corresponding data that you need from the connection collection using the following query:

stream: Firestore.instance.collection('connection').where('uid', isEqualTo: "xyc4567").snapshots(),

Another solution would be to create a subcollection named connection under each user and add all connection objects beneath it. This practice is called denormalization and is a common practice when it comes to Firebase. If you are new to NoQSL databases, I recommend you see this video, Denormalization is normal with the Firebase Database for a better understanding. It is for Firebase realtime database but same rules apply to Cloud Firestore.

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

Solution 2

Suppose, you want to use a Stream that depends on some Future objcets.

Stories
     Document ID (Auto Generated) //Suppose, "zddgaXmdadfHs"
       > name = "The Lion & the Warthog"
       > coverImage = "https://...."
       > author = "Furqan Uddin Fahad"
       > publisDate = 123836249234
Favorites
     Document ID (Auto Generated)
       > storyDocID = "zddgaXmdadfHs" //Document ID of a story
       > userId = "adZXkdfnhoa" //Document ID of a user

Sql equivalent query should look like this

SELECT * FROM Favorites AS f, Stories AS s
WHERE f.storyDocID = s.DocumentID 
AND f.userId = user.userId

And Firestore query like this

final _storeInstance = Firestore.instance;

Stream <List<Favorite>> getFavorites() async*  {
  final user = await _getUser(); //_getUser() Returns Future<User>
  yield* _storeInstance
      .collection('Favorites')
      .where('userId', isEqualTo: user.userId)
      .snapshots()
      .asyncMap((snapshot) async {
        final list = snapshot.documents.map((doc) async {
          final story = await _getStory(doc['storyDocID']);
          return Favorite.from(doc, story); //Favorite.from(DocumentSnapshot doc, Story story) returns an instance of Favorite
        }).toList(); //List<Future<Favorite>>
        return await Future.wait(list); //Converts List<Future<Favorite>> to Future<List<Favorite>>
      });
}

Future<Story> _getStory(String storyDocID) async {
  final docRef = _storeInstance
      .collection('Stories')
      .document(storyDocID);
  final document = await docRef.get();
  final story = Story.from(document);
  return story;
}

Solution 3

I did some like this to join results from two colections objects and categories.

i did two StreamBuilders to show in a list, in the first one i got the categories and put in a map, then i query the objects and get the category object from the map using the categoryID:

StreamBuilder<QuerySnapshot>(
              stream: Firestore.instance
                  .collection('categoryPath')
                  .snapshots(),
              builder: (BuildContext context,
                  AsyncSnapshot<QuerySnapshot> categorySnapshot) {
                //get data from categories

                if (!categorySnapshot.hasData) {
                  return const Text('Loading...');
                }

                //put all categories in a map
                Map<String, Category> categories = Map();
                categorySnapshot.data.documents.forEach((c) {
                  categories[c.documentID] =
                      Category.fromJson(c.documentID, c.data);
                });

                //then from objects

                return StreamBuilder<QuerySnapshot>(
                  stream: Firestore.instance
                      .collection('objectsPath')
                      .where('day', isGreaterThanOrEqualTo: _initialDate)
                      .where('day', isLessThanOrEqualTo: _finalDate)
                      .snapshots(),
                  builder: (BuildContext context,
                      AsyncSnapshot<QuerySnapshot> objectsSnapshot) {
                    if (!objectsSnapshot.hasData)
                      return const Text('Loading...');

                    final int count =
                        objectsSnapshot.data.documents.length;
                    return Expanded(
                      child: Container(
                        child: Card(
                          elevation: 3,
                          child: ListView.builder(
                              padding: EdgeInsets.only(top: 0),
                              itemCount: count,
                              itemBuilder: (_, int index) {
                                final DocumentSnapshot document =
                                    objectsSnapshot.data.documents[index];
                                Object object = Object.fromJson(
                                    document.documentID, document.data);

                                return Column(
                                  children: <Widget>[
                                    Card(
                                      margin: EdgeInsets.only(
                                          left: 0, right: 0, bottom: 1),
                                      shape: RoundedRectangleBorder(
                                        borderRadius: BorderRadius.all(
                                            Radius.circular(0)),
                                      ),
                                      elevation: 1,
                                      child: ListTile(
                                        onTap: () {},
                                        title: Text(object.description,
                                            style: TextStyle(fontSize: 20)),
//here is the magic, i get the category name using the map 
of the categories and the category id from the object
                                        subtitle: Text(
                                          categories[object.categoryId] !=
                                                  null
                                              ? categories[
                                                      object.categoryId]
                                                  .name
                                              : 'Uncategorized',
                                          style: TextStyle(
                                              color: Theme.of(context)
                                                  .primaryColor),
                                        ),

                                      ),
                                    ),
                                  ],
                                );
                              }),
                        ),
                      ),
                    );

I'm not sure if is what you want or is clear but i hope it help you.

Solution 4

I think denominational should not be preferred because to maintain the it you have to make extra writes to firestore

instead jorge vieira is correct since you are allowed to make double reads as compare to the writes

so its better to read twice instead of writing writing data twice and its also very impractical to remember every demoralized thing in a large project

View more solutions