Composite keys / many-to-many SQL DB


#1

Hello,

I’m using Dreamfactory as an Remote SQL Access to a MySQL-Database. This API should be implemented in an angular-based client with angular’s $resource. Here I need to patch relational tables with composite primary keys. Most examples for integrating a REST API with $resource work with a single identifier:

$resource(’/myapp/firsttable/:id’, {id: “@id”}, {update: {method:‘PATCH’}})

Unfortunately, I found no way to integrate it by using two identifiers.

After some research I found out that it is possible to call the relational table by calling the two primary tables with their key, which then redirect to the wanted table. http://stackoverflow.com/questions/6324547/how-to-handle-many-to-many-relationships-in-a-restful-api

/firsttable/{ID}/secondtable/{ID} → /relationaltable/{uuid}

Unfortunately, this doesn’t work with Dreamfactory. The call is only executed until it reaches the first ID. How can I resolve this problem? Do i need to configure something in the backend or do i need to implement this in a different way?

Thanks.


#2

It would have to be implemented in some other way. The API does not natively support queries more than a single level (table) deep. However, we do support calling SQL stored procedures and views. You could perform your more complex related data queries in a procedure or view, and call the procedure or view through DreamFactory. Here are the relevant pages from our documentation: