Create/update many to many relationship documentation needed

[kyle-copeland]

Hello,

I'm currently evaluating Feathers for our team and love it. I'm trying to cover all of our use cases and am stumped on how to handle creating or updating associations between entities with many to many relationships.

I'd like to request for a save and update m to n relationship feathers example.

Scenario:
There are two services /players and /teams (these are arbitrary resources I thought of). Players may belong to many teams and Teams have many players. I'm not sure how to handle associating players with teams in an elegant fashion.

I'd like to understand how to do the following:

• Add a player to a team
• Remove a player from a team

I am able to setup the association fine:
team.model.js

...
  team.associate = function (models) {
    team.belongsToMany(models.player, {through: 'PlayerTeam'});
  };
...

player.model.js

...
  player.associate = function (models) {
    player.belongsToMany(models.team, {through: 'PlayerTeam'});
  };
...

[edwardsmarkf]

kyle - does that just resolve like this? (example) Thank you, Mark Edwards

…

On Thu, Apr 5, 2018 at 3:44 PM, Kyle Copeland ***@***.***> wrote: Hello, I'm currently evaluating Feathers for our team and love it. I'm trying to cover all of our use cases and am stumped on how to handle creating or updating associations between entities with many to many relationships. I'd like to request for a save and update m to n relationship feathers example. Scenario: There are two services /players and /teams (these are arbitrary resources I thought of). Players may belong to many teams and Teams have many players. I'm not sure how to handle associating players with teams in an elegant fashion. I'd like to understand how to do the following: - Add a player to a team - Remove a player from a team I am able to setup the association fine: team.model.js ... team.associate = function (models) { team.belongsToMany(models.player, {through: 'PlayerTeam'}); }; ... player.model.js ... player.associate = function (models) { player.belongsToMany(models.team, {through: 'PlayerTeam'}); }; ... — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#852>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ACyd4jo95mdpY5gNYF3GtdghdDwfwG4Uks5tlp40gaJpZM4TJRE4> .

[kyle-copeland]

@edwardsmarkf Hey Mark, thanks for reaching out. I think you example might have been left out.

[edwardsmarkf]

hi kyle - the image came from here: https://en.wikipedia.org/wiki/Many-to-many_(data_model) -- maybe github mail does not allow for photos? i hope i understood you correctly. Thank you, Mark Edwards

…

On Thu, Apr 5, 2018 at 5:51 PM, Kyle Copeland ***@***.***> wrote: @edwardsmarkf <https://github.com/edwardsmarkf> Hey Mark, thanks for reaching out. I think you example might have been left out. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#852 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ACyd4gMY3kJiprvR3I2fRZP7LThfbvLHks5tlrwDgaJpZM4TJRE4> .

[edwardsmarkf]

forgot to mention: https://en.wikipedia.org/wiki/Many-to-many_(data_model): The Author-Book many-to-many relationship as a pair of one-to-many relationships with a junction table can your relationship get a "junction table" between them? the junction table might be able to hold comments, dates, etc. just a thought. Thank you, Mark Edwards

…

On Thu, Apr 5, 2018 at 6:04 PM, Mark Edwards ***@***.***> wrote: hi kyle - the image came from here: https://en.wikipedia.org/wiki /Many-to-many_(data_model) -- maybe github mail does not allow for photos? i hope i understood you correctly. Thank you, Mark Edwards On Thu, Apr 5, 2018 at 5:51 PM, Kyle Copeland ***@***.***> wrote: > @edwardsmarkf <https://github.com/edwardsmarkf> Hey Mark, thanks for > reaching out. I think you example might have been left out. > > — > You are receiving this because you were mentioned. > Reply to this email directly, view it on GitHub > <#852 (comment)>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/ACyd4gMY3kJiprvR3I2fRZP7LThfbvLHks5tlrwDgaJpZM4TJRE4> > . >

[kyle-copeland]

Hey Edward, thanks. Handling the junction table with an ORM/feathers is the issue at hand.

Here is my best guess and how to solve the problem with Sequelize. Let me know what you all think.

app.use('/team/:teamId/player/:playerId', {
    // Gives the ability to add the association
    create(data, params) {
      return app.service('team').Model.findById(params.route.teamId).then(team => {
        return app.service('player').Model.findById(params.route.playerId).then(player => {
          return team.addPlayer(player);
        });
      });
    },

    // Gives the ability to delete an association
    remove(id, params) {
      return app.service('team').Model.findById(params.route.teamId).then(team => {
        return app.service('player').Model.findById(params.route.playerId).then(player => {
          return team.removePlayer(player);
        });
      });
    }
  });

[edwardsmarkf]

i wonder if this could be better answered at https://sequelize.slack.com ? or use a view instead?

please keep me informed as to what you decide is best. it's a very interesting issue.

[daffl]

That service you are suggesting is actually pretty neat. I sometimes also just create a separate service for the join table model. I wonder if @DesignByOnyx has any insights here from his travels.

Creating new related entries should work by passing arrays to create though right?

[kyle-copeland]

Thanks for the replies everyone. @daffl for your suggestion, you end up having a team-player service and combine entries through hooks? May you also explain what you mean by creating new related entries via an array?

[kyle-copeland]

@daffl here is my best guess at service vs. ORM

service-oriented way:

//AFTER HOOK: pseudo-code I haven't tested this

const {result, app} = context;

result.map(async team => {
  const players = await app.service('team-player').find({
      query: {
        teamId: team.id
      }
    }).then(results => {
       return app.service('player').find({
          query: {
            playerId: results.playerId
        });
    });
  
  team.players = players;
})

return context;

ORM way:

// BEFORE HOOK: this works
 context.params.sequelize = {
      include: [ {
        model: context.app.service('player').Model,
        as: 'players',
        attributes: ['name'],
        through: {
          attributes: []
        }
      } ],
      raw: false
  };

  return context;

[edwardsmarkf]

kyle - i am about to run into the same issue you are up against, so please let me know how this works out for you. Thank you, Mark Edwards

…

On Fri, Apr 6, 2018 at 11:22 AM, Kyle Copeland ***@***.***> wrote: @daffl <https://github.com/daffl> here is my best guess at service vs. ORM service-oriented way: //AFTER HOOK: pseudo-code I haven't tested this const {result, app} = context; result.map(async team => { const players = await app.service('team-player').find({ query: { teamId: team.id } }).then(results => { return app.service('player').find({ query: { playerId: results.playerId }); }); team.players = players; }) return context; ORM way: // BEFORE HOOK: this works context.params.sequelize = { include: [ { model: context.app.service('player').Model, as: 'players', attributes: ['name'], through: { attributes: [] } } ], raw: false }; return context; — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#852 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ACyd4hzQdRSLJ5tBm7I_BkOFPDYkyhU7ks5tl7JKgaJpZM4TJRE4> .

[DesignByOnyx]

n:m relationships are by far the toughest relationship to reconcile across CRUD operations. I always use "blogposts" and "tags" my classic n:m example. There are 3 different scenarios you have to consider:

1. Create/update a blogpost with only new tags
2. Create/update a blogpost with only existing tags
3. Create/update a blogpost with a combination of new and existing tags

#3 is by far the most complex; however, by solving that scenario you solve the first 2 for free. Here is how I would approach it:

• constraint: as far as clients are concerned, associating tags to a blog post would always happen through the blogpost service (just to keep it easy). Let's assume we are only updating a single blogpost with the following payload:

  {
    id: 123,
    title: 'My first blog post',
    body: '...',
    tags: [
      { id: 111, text: 'tag-1' },
      { id: 222, text: 'tag-2' },
      { text: 'new-tag' } // new tags will not have an "id"
    ]
  }

• on the blogpost service, focus on updating the blogpost first - we will save the tags for later. We will need to "cache" the tags data in a before hook so the blogpost can update correctly:

  (context) => {
    if (context.data.tags) {
      context._tag_data = context.data.tags;
      delete context.data.tags;
    }
    return context;
  }

• In an after hook, handle the tags. We will first need to create any new tags:

  async (context) => {
    const tags = context._tag_data;
    if (tags && tags.length) {
      // tags without an "id" are considered new
      const existingTags = tags.filter(t => t.hasOwnProperty('id'));
      const newTags = tags.filter(t => !t.hasOwnProperty('id'));
      await tagService.create(newTags).then(createdTags => {
        // update the context._tag_data to contain the existing and newly created tags
        context._tag_data = existingTags.concat(createdTags)
      })
    }
    return context;
  }

• Finally, associate all of the tags with the blogpost:

  async (context) => {
    const tags = context._tag_data;
    if (tags && tags.length) {
      const blogPostId = hook.result.id;
      const mappings = tags.map(t => ({ tagId: t.id, blogPostId }));
      await postTagsService.create(mappings).then(() => {
        // Put the tags on the final result
        hook.result.tags = tags;
      });
    }
    return context;
  }

---

I know that doesn't seem easy, but I've put a lot of time thinking about all of the use cases and the above is the only way I see about a holistic user-friendly solution. I'm up for suggestions, but you have to contend with established usability conventions (Wordpress, StackOverflow, etc) which allow creating and associating data in a single operation.

[daffl]

I also think Sequelize makes this a little more difficult than it needs to be. The model methods seem intuitive as a developer but from an API client perspective you just submit data, not call any methods.

For example, it is not possible to update or create associations with either an existing id or by giving a list of ids - which I found very counter-intuitive.

[abalad]

I have this same problem and I have not found something that is easy to use and safe.

[russellr922]

Same problem here, thanks @DesignByOnyx going to go with that approach for now. Anyone else come up with an intuitive solution for this?

[checkerap]

I'm also following this, currently looking to implement this for a new project this week

[checkerap]

@daffl thanks for the suggestion, that worked for me for now.

[tomlagier]

Just as a small note, it's actually more complex than @DesignByOnyx indicated - you will likely need to handle deletes as well. One way to accomplish this is to send all tags with each update request, and then to clear any tags associated with the post before saving all tags - this way you're assured to have the correct set of tags saved.

The downside is you do quite a bit of unnecessary work on each update (deleting and saving a list of potentially unchanged tags).

The only alternative I can think of is reading the relationship before updating, and working out the minimum number of [create, update, deletes] necessary. Might be worth it if you have a large number of tags.

I guess another idea is to create a service for the relationship, identify each entry by ID and call independently for each [post, tag] pair. That seems inefficient with network requests but is conceptually a little simpler than trying to do everything at once.

[Asher978]

class CustomService {
  constructor(options) {
    this.options = options || {};
  }

  /**
   * @params {object} data - Sent in from client
   * @params {INT} data.building_id - ID of the building from the Building Model
   * @params {INT} data.contact_id - ID of the contact from the Contact Model
   */
  create = async (data, params) => {
    if (isNaN(data.building_id)) {
      throw new errors.BadRequest('Building ID MUST be a Number', data);
    }
    if (isNaN(data.contact_id)) {
      throw new errors.BadRequest('Contact ID MUST be a Number', data);
    }

    const building = await this.options.building.get(data.building_id);
    const contact = await this.options.contact.get(data.contact_id);
    const buildingContacts = await building.addContact(contact);

    return buildingContacts;
  };
}

export default app => {
  app.use(
    'api/v1/building_contacts',
    new CustomService({
      building: app.service('api/v1/building'),
      contact: app.service('api/v1/contact')
    })
  );

  const buildingContactsService = app.service('api/v1/building_contacts');

  buildingContactsService.hooks(hooks);
};

Problem I am facing is that if the contact already exists on the building, i am getting a page not found error on postman.
If the contact has never been added to the building then this runs with no issues.

Any ideas?

[MarcGodard]

  const tags = context._tag_data;
  if (tags && tags.length) {
    const blogPostId = hook.result.id;
    const mappings = tags.map(t => ({ tagId: t.id, blogPostId }));
    await postTagsService.create(mappings).then(() => {
      // Put the tags on the final result
      hook.result.tags = tags;
    });
  }

Does this mean you need to create a feathers service for the in-between tables? Seems like an unneeded service. Is there another way of interacting with the database here?

[DesignByOnyx]

The "relationship" service is necessary, and every attempt I've made to avoid having as 3rd service has been fruitless or made other code unreasonably difficult. This was the biggest "ah ha" moment I had when dealing with n:m relationships, and trust me it will help you too. I didn't come up with this idea myself - people have written articles about the concept that "the relationship (eg. the join table) is an entity itself and should be treated as such".

Think about this scenario - you have a blog post and an existing tag and you want to relate the two. You're not creating or updating the blog post itself, and you're not creating or updating the tag - you are simply defining a relationship between the two. You shouldn't need to touch the blog service or the tag service - only the "blog-tags" service... which needs to exist in order for that to happen.

The other (more convoluted) option is to try to use the blog service or the tag service to update the relationship. Which one do you use? How do you convey that to your team? If you allow it to happen both ways, now you have twice the code to maintain as well as a difficult mental model. All of these problems go away if you have a 3rd service for the relationship itself.

[ghost]

I got say. let's make a very easy example, and I hope someone could provide an answer.
I have a Post with Types: "Romantic, Drama"

UI Looks as follow
PostOne
tags: [ romantic, drama ]

Backend look as follow
tables are Post, Type & Post_Type (M:N)

Post	#1
Name	PostOne

Type	#1	#2
Name	Romantic	Drama

PostType	#1	#2
PostId	1	1
TypeId	1	2

Now A user decide to remove "Drama" from PostOne.

UI Looks as follow
PostOne
tags: [ romantic ]

Backend look as follow

PostType	#1
PostId	1
TypeId	1

How to do this in Sequelize?