Support related table fields save

[rrjanbiah]

As you support fetching related table fields, would it be possible to saving related fields?

TIA

[mevdschee]

Thank you for reaching out.

Can you give an example of the desired behaviour? How would you identify these related items? How do you differentiate between update and insert on the related items? Hoe does error reporting work?

Kind regards, Maurits

[rrjanbiah]

@mevdschee What about Rails' approach? Posting something like (wrapped with table name):

table1[col1] = 'foo';
table1[col2] = 'foo';
table2[col1] = 'foo';

[mevdschee]

That seems to be Rails ActiveRecord code. Can you give an example as a REST request? Also does this update or insert into table2? What if there are multiple records from table2 associated with table1?

[rrjanbiah]

@mevdschee Will come up with possible usecases.

[mevdschee]

I understand the use cases, but there are many challenges in the design (of the syntax). Maybe the simplest is to support that a series of requests is automatically treated as a transaction. That, on the other hand, poses a problem when people forget to close their transaction.

[rrjanbiah]

@mevdschee Still thinking over this. Over the top of my head, thinking of "hinting" through magic fields about creating, saving in serial fashion, saving with related field entry, etc.

[komlos]

It can be done by using UUIDs with PUT requests instead of POST and the database autoincrement primary key.

The problem with the standard approach is that you need two requests to save the related table fields. The first request for saving your main table fields, which must return with the primary key of the newly saved row. This primary key is usually autoincremented by the database. The second one for saving the related table fields together with the primary key from the first request.

This approach can also cause other problems. What if timeout happens between the two requests and the second request is not saved? When you realise this and try to make the same POST request again to resave the resources, the database will return with a NEW primary key, and save the related fields with the new key thus leaving the previous row orphaned. This is because POST method is not an idempotent one, that is, it changes the resource every time it is called. PUT method should be used instead which is idempotent.

Also, in order to save the main table and related table fields in one call, your one request has to know the primary key in advance and must send it with that one request. That is possible if you control the generation of UUIDs and not the database.

See this post, especially Seth's answer:
http://stackoverflow.com/questions/6324547/how-to-handle-many-to-many-relationships-in-a-restful-api

About Safe and Idempotent methods:
http://restcookbook.com/HTTP%20Methods/idempotency/

[mevdschee]

What if timeout happens between the two requests and the second request is not saved?

I guess that traditionally this is solved by allowing a transaction to take place. Maybe we need a way to express a series of requests.

Also, in order to save the main table and related table fields in one call, your one request has to know the primary key in advance and must send it with that one request.

Or you need a way to express re-use the result of a previous request in the next one

[BarryDam]

Or what if you could create a callback method like

'after_create'=> function($db, $tab, $createdId, $createdRow) {
}

Than you could save data to some other table. Like for instance an logging table.

[meesvandongen]

What about posting to the /api.php itself? An object like this could work
[ {'table1': { 'field': 'value' }}, {'table2': { 'field': 'value' }} ]