Many-To-Many Relationships in Google App Engine Datastore (ndb)

Question

I have two models: Members and Events. A member can participe in many events and in an event, there are many participants. I think about like this:

class Members(ndb.model):
    event = ndb.KeyProperty(repeated=True)

class Events(ndb.model):
    member = ndb.KeyProperty(repeated=True)

What's the best way to do many-to-many relationship?

Weismannism

Answer 1

I think in this case you want to have an array/list of keys in one model pointing to the other model. Since there is a limit on the length of the array/list (the max is 5000 right now) you probably want to have the member model point to the events models (I am assuming members probably don't go to many events (more than 5000), but there are many (more than 5000) members at an event). Now if you want the list of attendees you just query for the event key in the members models. You can do a key only query if you just want the head count, this will save on cost.

If for some reason you need to have both cases be over 5000, then you can make another (attendee) model that contains a member key and an event key, then make this attendee model for each guest for each event. To get the list of guests just query this model for a specific event, if you need a list of events for a member do the opposite. This isn't much more work than the above so might be your go to way of doing it even if 5000 isn't a limiting factor.

Nakesha

If there are fewer than 5000 members at any given event, and members go to fewer than 5000 events you can consider what to query for more often, the list of events for a given member, or the attendee list for an event. If it is the first then go with a list of events in the member model, if it is the second you can consider a list of members in the event model, although now you can never have more than 5000 guests. – Nakesha

Hi Micheal, thank you for your great answer. This scores quite high on my search for info so I hope you don't mind asking you a question. If I'd go for a attendee model option do I have to fetch Event details per Member instance and vice versa? I would implement this in a def in Member that would fill Event details and can be called or not depending if I want to lazy load a list of Members or not. This because I can't get all data in one query since joins are not supported (unless using cloud SQL) – Kassala

"do I have to fetch Event details per Member instance and vice versa" If I understand you I think the answer is yes. If you want to know the details of an event that a member is going to you would first query the attende model to get a list of all events the member is attending. This will return you keys (or a field where you can then make a key from it). You would then fetch (you won't need to query) all the events in that list. The downside to this method is the extra work needed to get information. – Nakesha

Thank you Michael, I'm slowely finishing up a project to play with with students and coursed. have added a StudentCourse class that has methods like isInOther and getCourseForStudentKey seems like some extra work but it's fine. Maybe worried about getting a bit too expensive when full text search is done as well (course and student name). Expensive in terms of what Google may charge such a site. – Kassala

Be careful that the limit should be way less than 5000: #15377619 – Danaides