0

Suppose I have a recipe page where the recipe can have a number of ingredients associated with it. Users can edit the ingredients list and update/save the recipe. In the database there are these tables: recipes table, ingredients table, ingredients_recipes_table. Suppose a recipe has ingredients a, b, c, d but then the user changes it to a, d, e, f. With the request to the server, do I just send only the new ingredients list and have the back end determine what values need to be deleted/inserted into the database? Or do I explicitly state in the payload what values need to be deleted and what values need to be inserted? I'm guessing it's probably the former, but then is this handled before or during the db query? Do I read from the table first then write after calculating the differences? Or does the query just handle this?

I searched and I'm seeing solutions involving INSERT IGNORE... + DELETE ... NOT IN ... or using the MERGE statement. The project isn't using an ORM -- would I be right to assume that this could be done easily with an ORM?

rahc01
  • 47
  • 7

2 Answers2

0

Can you share what the user interface looks like? It would be pretty standard practice that you can either post a single new ingredient as an action or delete one as an action. You can simply have a button next to the ingredients to initiate a DELETE request, and have a form beneath for a POST.

Having the users input a list creates unnecessary complexity.

Keegan
  • 1
  • 2
  • I haven't implemented it yet but I'm thinking delete and add buttons which appear after you click an edit button. And then a save button which pushes the batch changes. Would it be better to do delete/add as individual requests each time a user clicks delete/add? – rahc01 Jul 05 '19 at 18:35
  • Yes. That is standard practice. But you can absolutely have your form and delete buttons appear only once you hit an edit button. That could be a neat feature. – Keegan Jul 06 '19 at 00:54
0

A common pattern to use would be to treat this like a remote authoring problem.

The basic idea of remote authoring is that we ask the server for its current representation of a resource. We then make local (to the client) edits to the representation, and then request that the server accept our representation as a replacement.

So we might GET a representation that includes a JSON Array of ingredients. In our local copy, we remove the ingredients we no longer want, add the new ones in. The we would PUT our local copy back to the server.

When the documents are very large, with changes that are easily described, we might instead of sending the entire document to the server instead send a PATCH request, with a "patch document" that describes the changes we have made locally.

When the server is just a document store, the implementation on the server is easy -- you can review the changes to decide if they are valid, compute the new representation (if necessary), and then save it into a file, or whatever.

When you are using a relational database? Then the server implementation needs to figure out how to update itself. An ORM library might save you a bunch of work, but there are no guarantees -- people tend to get tangled up in the "object" end of the "object relational mapper". You may need to fall back to hand rolling your own SQL.

An alternative to remote authoring is to treat the problem like a web site. In that case, you would get some representation of a form that allows the client to describe the change that should be made, and then submit the form, producing a POST request that describes the intended changes.

But you run into the same mapping problem on the server end -- how much work do you have to do to translate the POST request into the correct database transaction?

REST, alas, doesn't tell you anything about how to transform the representation provided in the request into your relational database. After all, that's part of the point -- REST is intended to allow you to replace the server with an alternative implementation without breaking existing clients, and vice versa.

That said, yes - your basic ideas are right; you might just replace the entire existing representation in your database, or you might instead optimize to only issue the necessary changes. An ORM may be able to effectively perform the transformations for you -- optimizations like lazy loading have been known to complicate things significantly.

VoiceOfUnreason
  • 52,766
  • 5
  • 49
  • 91
  • Oh actually there was another question I wanted to ask but ended up omitting which was related to the RESTful bit. If each recipe has many ingredients and many authors and I wanted to fetch a list of recipes (paginated), would I have 3 API endpoints where I fetch the recipes then ingredients and authors based on the recipe ids? Or 1 endpoint which would fetch the recipes joined on the other 2 tables? Or would I implement a view and fetch that? – rahc01 Jul 05 '19 at 16:00