Query for embedded documents on MongoDB

help
1

Hi, I had a project built with Laravel and MySQL, and in that project, I had a particular database table called “Events”, which is used to store user events (it’s a calendar-like app), I also had a restful API for that table that would accept two parameters: $start and $end, this would return all Event records that are relevant for the timespan between $start and $end, and I had created a query scope function to execute this operation:

public function scopeIntersectingEvents($query, $start, $end) {
    // Query all events that occur whithin the given time range,
    // and events that start within the time range and end outside it
    // (in a later point)

    $queryTwo = clone $query;
    $Q1 = $queryTwo->where('start_date', '>=', $start)
          ->where('start_date', '<=', $end)
          ->where('end_date', '>', $start);

    // Query all events that occur outside the given time range, but
    // encompasses the entire time range, and events that that start outside
    // the time range (in an earlier point) and end within the time range
    return $query->where('start_date', '<=', $start)
          ->where('start_date', '<=', $end)
          ->where('end_date', '>=', $start)
          ->union($Q1);
  }

Now, I’m trying to port this app to Meteor and I’m new with the whole NoSQL thing, but based on what I’ve read about denormalization in NoSQL Databases, I’ve decided that it would be better to embed the events collection inside the users collection, that way I wouldn’t have ‘pseudo-joins’ between these two tables, and instead of having a separate events collection with each record containing an user_id field, I’d simply query for the user and with that I’d also receive the events associated with that record, please correct me if I’m wrong.

But now, I don’t know how I’d execute the above operation on a embedded document in a NoSQL database, I can see two possibilities: to simply fetch the user document, and then filter the events array of objects to return the relevant event documents, which I don’t know if would be appropriate. The other solution is to move the events subdocument to it’s own collection, and then I could combine operators and projection to achieve the same result, maybe in a cleaner and more appropriate way. I don’t know if there’s a more appropriate way to obtain the same result I had with the SQL database with a Mongo database. Should I move the events subdocument to it’s own collection, or filter the events array? Thanks and sorry for the long post.

2

Porting a SQL schema to MongoDB is not something I would undertake lightly. An optimum SQL design is not going to be optimum for MongoDB - and denormalising data as a workaround is likely to be problematic.

You do not have to use MongoDB with Meteor*, even though it’s currently the easiest way to get reactivity out of the box.

Meteor is now fully compatible with NPM, so you have access to a huge range of solutions in the SQL space if you don’t need reactive data.

Alternatively, if you do need reactive SQL, then you could look at using numtel:mysql for MySQL, or numtel:pg for PostgreSQL. I’ve used numtel:mysql myself and it works really well.

Alternatively alternatively, you could consider Apollo - MDG’s implementation of GraphQL, which is going to get full Meteor support from 1.5 - but you can use it now if you’re prepared for a bit of work. That has got reactive support on the roadmap and will be the future of data management in Meteor.

* If you want to make use of Meteor’s accounts system, then you will need MongoDB, at least for now.

1 Like
3

Well, I could indeed use SQL, even though I’d lose of the coolest Meteor features. But I also want to learn more about NoSQL itself, and I believe that, although the result may not be optimal, it’s totally possible to port this structure to an non-relational database right? I mean, it’s not that complex. And actually, that’s a query I wouldn’t be executing to often, thus why I considered moving the events subdocument to it’s own collection.

EDIT: Well, for the time being, I’ve decided to leave Events in it’s own collection, then I can use the $or operator and try to reproduce the SQL query used in my Laravel app, what would be the drawbacks of this approach?

4

Well, I don’t know your application, schema or query, so it’s difficult to be objective. However, to be completely non-specific I would say make sure you index your collection(s) adequately. That seems to be the one thing most people seem to forget about with MongoDB, even though they’re right on top of it with SQL.

1 Like