How to inner-join in firestore

Question

I want to build a view to show some events inside a listview in my app like this:

I have these two tables:

Users

Events

But I don't know how do a "inner join" between the tables USERS and EVENTS...

I tried this:

import 'package:cloud_firestore/cloud_firestore.dart';
import 'package:flutter/material.dart';
import 'package:project/Methods.dart';
import 'package:project/Views/CadastroUsuario.dart';
import 'dart:math';

class EventClass{
  String owner;
  String description;
  String city;
  String state;
  String place;
}

class EventsListing extends StatefulWidget {
  @override
  EventsListingState createState() => new EventsListingState();
}

class EventsListingState extends State<EventsListing> {
  List<EventClass> events;

  @override
  void initState() {
    super.initState();
    events = new List<EventClass>();
  }

  void buildEventClass(DocumentSnapshot doc) async {
    EventClass oneEvent = new EventClass();

    DocumentReference document = Firestore.instance.collection("users").document(doc["userid"]);

    document.get().then((DocumentSnapshot snapshot){
      oneEvent.owner = snapshot["name"].toString();
    });
    oneEvent.description = doc["description"];
    oneEvent.place       = doc["place"];
    oneEvent.city        = doc["city"];
    oneEvent.state       = doc["state"];
    events.add(oneEvent);
  }

  @override
  Widget build(BuildContext context) {
    return new Scaffold(
      appBar: new AppBar(
        title: new Text('Events'), 
      ),
      body: new StreamBuilder(
        stream: Firestore.instance.collection("events").snapshots(),
        builder: (BuildContext context, AsyncSnapshot<QuerySnapshot> snapshot){
          if (snapshot.connectionState == ConnectionState.waiting)
            return Text("Loading...");

          return new ListView(
            padding: EdgeInsets.only(left: 5.0, right: 5.0, top: 5.0),
            children: snapshot.data.documents.map((document){
               buildEventClass(document);
               return events.length == 0 ? new Card() : item(events.last);
              }).toList()
          );
        },
      ),
      floatingActionButton: new FloatingActionButton(
        tooltip: 'New',
        child: new Icon(Icons.add),
        onPressed: () async {
          Navigation navigation = new Navigation();
          navigation.navigaTo(context, CadastroUsuario());
         },
      ),
    );
  }

  Widget item(EventClass oneEvent) {
    return new Card(
      elevation: 4.0,
      child: new Column(
        children: <Widget>[
          new Row(
            children: <Widget>[
              new Column(
                children: <Widget>[
                  new Text(oneEvent.owner.toString(),
                    style: TextStyle(fontSize: 20.0),
                    overflow: TextOverflow.ellipsis,),
                ],
              ),
              new Column(
                children: <Widget>[

                ],
              )
            ],
          ),
          new Container(
            color: Colors.blue,
            height: 150.0,
          ),
          new Row(
            children: <Widget>[
              new Row( 
                children: <Widget>[
                  new Text(oneEvent.description.toString(), 
                    style: TextStyle(fontSize: 20.0),
                    overflow: TextOverflow.ellipsis,),
                ],
              ),
              new Row( 
                children: <Widget>[
                  new Text(oneEvent.place.toString(), 
                    style: TextStyle(color: Colors.grey[350]),
                    overflow: TextOverflow.ellipsis,),
                ],
              ),
              new Row( 
                children: <Widget>[
                  new Text(oneEvent.city.toString() +' - '+ oneEvent.state.toString(), 
                    style: TextStyle(color: Colors.grey[350]),
                    overflow: TextOverflow.ellipsis,),
                ],
              )
            ]
          )          
        ],
      )
    );
  }
}

But every time that I try to show these events I get this exception

Exception has occurred.
PlatformException(error, Invalid document reference. Document references must have an even number of segments, but users has 1, null)

What I'm doing wrong? How I can do a "inner join" between thesse tables and show the events?

I'm using the Firebase Firestore.

PS: I already know that Firestore is a noSQL database and have no "joins", but I want to do something like a join.

You said it, firestore is a noRelational DB, so if you need to merge data you would do it in your app/backend code, its that simple. Theres no way to query multiple collections at once in firestore. — Karlo A. López, Oct 18 '18 at 01:16
@KarloA.López Ok, but how I do it? I need to get the `EVENTS` documents first and then get `USERS` document? Can you help me with an example? — Matheus Miranda, Oct 18 '18 at 11:07
@FrankvanPuffelen I get the error in the end of this method `void buildEventClass(DocumentSnapshot doc) async` after the method `then()` in `document.get().then((DocumentSnapshot snapshot)` is executed to fill the `event.owner`. — Matheus Miranda, Oct 18 '18 at 11:52
@MatheusRibeiro Hello, to begin firebase has not inner join, in this moment i can't write a some code but you can try the following idea (which it worked for me weeks ago): firstly, you need to get all users from `users` table with `firestore.collection('users').snapshot()`, secondly, you have to map each `user` and query `events` table with `where(userIdField, user.Id)` or something like that and finally you will get all events by user. I hope my help is useful!. Enjoy! — Nano, Oct 18 '18 at 12:54
@Nano Thanks, for now I can't test it, but soon as I can I will try it... If you have a little time and you can, I will aprecciate an example... — Matheus Miranda, Oct 18 '18 at 13:08
Helllow, did you know how to do this ? https://stackoverflow.com/questions/59473751/how-to-compare-result-of-two-queries-in-firestore Thank you — Oren Cohen, Dec 24 '19 at 22:36
@OrenCohen I'm afraid not, I never worked with Angular. But you are on the right way, what you need to do is: make your first query, then, when it's retrieve the data you'll make your next query inside `.then((snapshot) => { New query here }` getting your field that you want to compare. Like the accepted answer shows. — Matheus Miranda, Dec 26 '19 at 11:03

Karlo A. López · Accepted Answer · 2018-10-19T15:37:22.493

As I was telling in the coments Firestore does not support multi collection querys cause its no relational DB. If you need to access multiple collections you would manage querys independently.

This is how I usually get related collections data (Sorry this is JS code but I dont know DART):

    var data = {};

    //First you get users data
    DocumentReference document = Firestore.collection("users")

    document.get().then((snapshot) => {

        //In this case I will store data in some object, so I can add events as an array for a key in each user object

        snapshot.forEach((userDoc) => {
            var userDocData = userDoc.data()

            if (data[userDoc.id] == undefined) {
                data[userDoc.id] = userDocData
            }

        })

        //So in this moment data object contains users, now fill users with events data

//In this var you count how many async events have been downloaded, with results or not.    
var countEvents = 0

        Object.keys(data).forEach((userDocId) => {

    //Here Im creating another query to get all events for each user

            SnapshotReference eventsForCurrentUserRef = Firestore.collection("events").where("userId", "==", userDocId)

            eventsForCurrentUserRef.get.then((eventsForUserSnapshot) => {
//Count events
countEvents++

                eventsForUserSnapshot.forEach((eventDoc) => {

                    var eventDocData = eventDoc.data()

                    //Check if array exists, if not create it
                    if (data[eventDocData.userId].events == undefined) {
                        data[eventDocData.userId].events = []
                    }

                    data[eventDocData.userId].events.push(eventDocData)


                })

if(countEvents == Object.keys(data).length){
//Lookup for events in every user has finished
}

            })


        })

    })

I did as you suggested and it looks like it worked, I get the documments and I fill a class, but I don't know how to show this class in a listview, because the `then()` function is a async method and when I try to build the listview the class is empty at the moment. — Matheus Miranda, Oct 19 '18 at 13:10
Nice observation, I've updated my example with some logic in which I count the number of event results and compare it with the number of users, so when the count is equal to de number of users you know for sure that every user has the corresponding array of events. — Karlo A. López, Oct 19 '18 at 15:38

How to inner-join in firestore

1 Answers1

Linked

Related