Json-server: Many-to-Many Relationship Support

[benz2012]

I finally understand the way relationships must be defined for semi-deep urls to work, but the example that is normally given is /posts/0/comments where _comment_ has a many-to-1 relationship with _post_.
The db.json for this would be:

{
  posts: [
    {"id": 0, "text": "hello"}
  ],
  comments: [
    {"id": 0, "text": "hi dude", "postID": 0} // each comment has direct link to a single post
  ]
}

Is there any current way to have elements related by a multi-foreignKey object?
For example:

{
  "items": [
    {
      "id": 0,
      "clothingItem": "Button Down Shirt"
    },
    {
      "id": 1,
      "clothingItem": "Jeans"
    }
  ],
  "tags": [
    {"id": 0, "tag": "pants"},
    {"id": 1, "tag": "blue"}
  ],
  "itemsWithTags": [
    {"id": 0, "tagID": 0, "itemID": 1},
    {"id": 1, "tagID": 1, "itemID": 0},
    {"id": 2, "tagID": 1, "itemID": 1}
  ]
}

This is an extremely simple example, but in reality would be very useful for things such as tags. If this is not supported then that would mean there would need to be a duplicate tag object for every item who needs that tag. That is an absolutely ridiculous implementation when you start to add a few more items and a few more tags.

[xlozinguez]

@benz2012 it does work as you would expect if you query /items/:item_id/itemsWithTags (you might want to replace ID with Id instead though if it doesn't work accordingly.)

Alternatively, to list nested resources, you could use the ?_embed=itemsWithTags when querying /items/:item_id as described here

[joezimjs]

@xlozinguez With http://localhost:3000/items/1/?_embed=itemsWithTags, that would get you this result:

{
  "id": 1,
  "clothingItem": "Jeans",
  "itemsWithTags": [
    {
      "id": 0,
      "tagId": 0,
      "itemId": 1
    },
    {
      "id": 2,
      "tagId": 1,
      "itemId": 1
    }
  ]
}

I think the goal here is to embed a list of the tags into the result. To get that tag data we'd need to make another request, built up based on the data returned from the first request: http://localhost:3000/tags/?id=0&id=1

If you try to do it in one request, it doesn't work:

http://localhost:3000/items/1/?_embed=itemsWithTags&_embed=tags yields

{
  "id": 1,
  "clothingItem": "Jeans",
  "itemsWithTags": [
    {
      "id": 0,
      "tagId": 0,
      "itemId": 1
    },
    {
      "id": 2,
      "tagId": 1,
      "itemId": 1
    }
  ],
  "tags": []
}

[joezimjs]

Personally, I'd prefer to not need the itemsWithTags "table". It's a waste. The items have tags, and since this isn't a relational DB we should be able to represent that with an array, e.g.:

{
  "items": [
    {
      "id": 0,
      "clothingItem": "Button Down Shirt",
      "tagIds": [1]      /* <----- */
    },
    {
      "id": 1,
      "clothingItem": "Jeans",
      "tagIds": [0,1]      /* <----- */
    }
  ],
  "tags": [
    {"id": 0, "tag": "pants"},
    {"id": 1, "tag": "blue"}
  ]
}

tagIds may also work as tags or tagId, whatever you guys decide the naming scheme is. I have sorta gotten this to work with tagsId and http://localhost:3000/items/0/?_expand=tags, however it only works with item 0 because it only has one tag in the list:

{
  "id": 0,
  "clothingItem": "Button Down Shirt",
  "tagsId": [
    1
  ],
  "tags": {
    "id": 1,
    "tag": "blue"
  }
}

That's exactly what I want, but if I try it with item 1 (http://localhost:3000/items/1/?_expand=tags), then it doesn't show the tags because it has multiple:

{
  "id": 1,
  "clothingItem": "Jeans",
  "tagsId": [
    0,
    1
  ]
}

[cvharris]

I feel like this issue isn't getting the attention it deserves since the original post doesn't reflect the ideal ask, and that is getting a lot of upvotes. Any movement on this?

[MaxArt2501]

The gotcha is in plural.js:

// Expand function used in GET /name and GET /name/id
function expand(resource, e) {
  e && [].concat(e).forEach(function (innerResource) {
    var plural = pluralize(innerResource);
    if (db.get(plural).value()) {
      var prop = `${innerResource}${opts.foreignKeySuffix}`;
      resource[innerResource] = db.get(plural).getById(resource[prop]).value();
    }
  });
}

When you use an array for tagId, getById just serializes it, thus looking for an id "0,1" which of course isn't found.

I've overcome the problem patching it with something like this:

// Expand function used in GET /name and GET /name/id
function expand(resource, e) {
  e && [].concat(e).forEach(function (innerResource) {
    var plural = pluralize(innerResource);
    if (db.get(plural).value()) {
      var prop = `${innerResource}${opts.foreignKeySuffix}`;
      if (_.isArray(resource[innerResource])) {
        resource[plural] = resource[innerResource].map(function (id) {
          return db.get(plural).getById(id).value();
        });
      } else {
        resource[innerResource] = db.get(plural).getById(resource[prop]).value();
      }
    }
  });
}

(Some optimization calls are obviously due.) Of course I'm not using a modified vendor file in the project, but I've checked that it works.

I could open a PR about it if you're interested (I am). It's possibly breaking, but I don't think it would impact many users.

[SRyabinin]

Hello @MaxArt2501 ,
Could you explain some behavier? I use MaxArt2501/json-server and expect that
"posts": [ { "id": 1, "content": "blabla" }, { "id": 2, "content": "blabla" } ], "authors": [ { "id": 1, "postId": [1,2], "name": "John Doe" } ]
/authors/1?_embed=posts
return
{ "id": 1, "postId": [ 1, 2 ], "name": "John Doe", "posts": [ { "id": 1, "content": "blabla" }, { "id": 2, "content": "blabla" } ], }
But I can get expected result only with authorId in post (one author to many posts):
"posts": [ { "id": 1, "content": "blabla", "authorId": 1 }, { "id": 2, "content": "blabla", "authorId": 1 } ], "authors": [ { "id": 1, "postId": [1,2], "name": "John Doe" } ]
Existance of "postId": [1,2], does not get any effect, and you can remove it. So it's look like X-to-many relationship doesn't work.

[MaxArt2501]

@SRyabinin Because you have to use _expand, not _embed. The latter is used for the other kind of relationship, from 'parent' to 'children', and you don't have to specify any additional property in your entities.

I've added a couple of tests and they're passing, so it should work.

[SRyabinin]

@MaxArt2501 oh it's my fault, the _expand request works properly. Tnx.

[miniplus]

I've already seen a few forks of this project for this reason.
I'm wondering why this isn't being merged or looked into? Is it a bad practice or is the solution above not fully complete @typicode?

[kuncevic]

For many to many thing check out this https://github.com/jimschubert/json-server-many-to-many
Basically it is kinda middlewere you have to bring to your json-server this approach here is different from https://github.com/typicode/json-server/pull/648 as it kinda sql approach of referencing the things as https://github.com/typicode/json-server/pull/648 is nosql

[MaxArt2501]

Hey folks.
It's a pity that we still haven't this issue resolved, and not even a word from @typicode on the matter.

For what's worth, I've been using this patch for getById:

router.db._.mixin({
  getById(collection, id) {
    const idProp = this.__id();
    if (Array.isArray(id)) {
      const ids = id.map(_id => _id.toString());
      return this.filter(collection, doc => {
        if (this.has(doc, idProp)) {
          return ids.includes(doc[idProp].toString());
        }
      });
    }
    return this.find(collection, doc => {
      if (this.has(doc, idProp)) {
        return doc[idProp].toString() === id.toString();
      }
    });
  }
});

This is the original from the package lodash-id (also by Typicode) that json-server uses internally.

That would allow us to write foreign keys as an array:

"posts": [{
  "id": 1,
  "text": "hello",
  "tagsId": [ 1, 3 ]
}]

I think this is the least obtrusive way to solve the problem. Too bad it won't work for _embed too 🤷‍♂️
Note this is a little different from what I did in my (still unmerged) PR #648, as it would require the foreign key field to be named tagsId and not tagId.

This library has a well-deserved success, but this little thing returns every time to bother...

[lukzas]

I've made a dirty fix in plural.js for _embed to work with arrays as follows:

function embed(resource, e, arr = false) {
    e && [].concat(e).forEach(externalResource => {
      if (db.get(externalResource).value) {
        var query = {};
        const singularResource = pluralize.singular(name);
        query[`${singularResource}${opts.foreignKeySuffix}`] = arr? [resource.id] : resource.id;
        resource[externalResource] = db.get(externalResource).filter(query).value();
      }
    });
  }

function show(req, res, next) {
    const _embed = req.query._embed;
    const _expand = req.query._expand;
    const _arr = req.query._arr;
    const resource = db.get(name).getById(req.params.id).value();

    if (resource) {
      // Clone resource to avoid making changes to the underlying object
      const clone = _.cloneDeep(resource); // Embed other resources based on resource id
      // /posts/1?_embed=comments


      embed(clone, _embed, _arr); // Expand inner resources based on id
      // /posts/1?_expand=user

      expand(clone, _expand);
      res.locals.data = clone;
    }

    next();
  }

"authors": [{
        "id": "Mary"
    },
    {
        "id": "John"
    }
],
"posts": [{
        "text": "lorem",
        "authorId": [
            "John",
            "Mary"
        ]
    },
    {
        "text": "ipsum",
        "authorId": [
            "Mary"
        ]
    }
]

Call would be /authors/Mary?_embed=posts&_arr=true containing _arr param set to true indicating we're dealing with an array of ids, not a single value. For usual _embed calls nothing has changed.

I haven't tested it very well, but it works for my needs - maybe somebody would find this useful as many issues suggested a solution to _expand relation only.

[lukzas]

Provided the fix without a need for an additional param in PR #1006