handling MANY_MANY put request for sub-resources

[kaushikkapil]

@evan108108 Suppose I have two tables "question" and "test". Now, I have a mapping table "test_question_mapping", with the following structure:

-------------------------------------------
|  id    |   test_id   |   question_id   |
-------------------------------------------

For a test, the question is now a subresource, and I am able to call the standard RESTfull APIs on top of it, and corresponding entries get added to the mapping table. Everything works just as expected !

However, I run into some problems if I want to store some additional information in the mapping table, for e.g.:

--------------------------------------------------------------
|  id    |   test_id   |   question_id   |    is_optional  |   
--------------------------------------------------------------

Here, at the time of adding the question to test, I want to say whether it is mandatory or optional to answer that question in this test. In this case, when I try the sub-resource "PUT" API with RESTfullYii, I see that a new row is inserted, but the "is_optional" field is not added. How can I solve this problem? Kindly suggest.

Regards, Kapil

[evan108108]

Why not override or "post.filter" the sub-resource "PUT" and add your custom logic to update the join table? I think this is the best way.

[kaushikkapil]

Yes....I thought about the same way, but that just has the JSON data.....is it right to pull out the data from JSON and update the models from that?

[evan108108]

Sure why not...

[kaushikkapil]

Cool.....this solves it then.....i was just a bit hesitant to do it. Thanks again !!

[kaushikkapil]

@evan108108 I tried what you suggested, and the approach does work fine for the cases where the columns other than the resource and sub-resource are nullable. However, if we have "not-null" columns in the database, then we get a "DB exception" when RESTfullYii internally calls the insert command for the sub-resource.

{
success: false
message: "CDbCommand failed to execute the SQL statement: SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "created_by" violates not-null constraint DETAIL: Failing row contains (3, 1, 1, null, null, null, null, null).. The SQL statement executed was: INSERT INTO "interview_user_mapping" ("interview_id", "user_id") VALUES (:yp0, :yp1)"
data: {
errorCode: 500
message: "CDbCommand failed to execute the SQL statement: SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "created_by" violates not-null constraint DETAIL: Failing row contains (3, 1, 1, null, null, null, null, null).. The SQL statement executed was: INSERT INTO "interview_user_mapping" ("interview_id", "user_id") VALUES (:yp0, :yp1)"
}-
}

I see two workarounds to this problem:

1. Define a custom API for PUT sub-resources, which would take care of the custom insert
2. Instead of defining a custom API, we can make a model for the Mapping table and consider it as a resource. Then we can make a "POST query" on that table with all the fields needed.

Assuming that we don't want to modify the current code for handling "put for subresources", which one is a better approach in your opinion or do you think there is an another approach which works better?

Regards, Kapil

[evan108108]

I think approach 2 makes the most sense given that you are treating it like a resource already (it has a numerical PK and properties additional to what's needed for the join). Failing that I would say you should modify your DB "join" table to allow Nulls and possibly default values.

On Monday, July 28, 2014, Kapil Kaushik notifications@github.com wrote:

@evan108108 https://github.com/evan108108 I tried what you suggested, and the approach does work fine for the cases where the columns other than the resource and sub-resource are nullable. However, if we have "not-null" columns in the database, then we get a "DB exception" as we try to call the insert API for the sub-resource.

{ success: false message: "CDbCommand failed to execute the SQL statement: SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "created_by" violates not-null constraint DETAIL: Failing row contains (3, 1, 1, null, null, null, null, null).. The SQL statement executed was: INSERT INTO "interview_user_mapping" ("interview_id", "user_id") VALUES (:yp0, :yp1)" data: { errorCode: 500 message: "CDbCommand failed to execute the SQL statement: SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "created_by" violates not-null constraint DETAIL: Failing row contains (3, 1, 1, null, null, null, null, null).. The SQL statement executed was: INSERT INTO "interview_user_mapping" ("interview_id", "user_id") VALUES (:yp0, :yp1)" }- }

I see two workarounds to this problem:

1. Define a custom API for PUT sub-resources, which would take care of the custom insert
2. Instead of defining a custom API, we can make a model for the Mapping table and consider it as a resource. Then we can make a "POST query" on that table with all the fields needed.

Assuming that we don't want to modify the current code for handling "put for subresources", which one is a better approach in your opinion or do you think there is an another approach which works better?

Regards, Kapil

— Reply to this email directly or view it on GitHub https://github.com/evan108108/RESTFullYii/issues/147#issuecomment-50306236 .

[kaushikkapil]

thanks for your feedback evan.......you can go ahead and close this thread now !

[Maykonn]

@evan108108, it does not work for HAS_MANY or HAS_ONE relations? See #195