Creating records using related data and obtaining their primary keys?


#1

I’ve got a Db structure where I have tblStock and tblStockLines related by fk_stockID in the lines table.

When adding a new line to an existing stock or a whole new set (stock and stock line) - how can I get the primary key of the newly inserted records? Specifically the child object’s primary key.

Almost like using the fields command when using POST or PUT that results in a lookup of the inserted data.


#2

//Planned? Perhaps something here to get the related table id’s back
var stockUpdateParams = {
resource: stock,
ids: ‘id’,
}

//Currently just sending the object to be updated like this (stock = object)
SharedFactoryStock.StockUpdate().update(stock, StockUpdatedSuccess, StockUpdatedFailure);


#3

Hi @drewpearce - have you guys got a function where the newly created related table’s new primary key’s can be returned in the calling function - currently I have to POST to the DB using an alternate tempGUID and then search for the record by that tempGUID in order to obtain its ID in case I PUT the record again (if the ID isn’t in the in-memory object then DF creates yet another related/child record).


#4

Hey Martin, if you include the ‘related’ url parameter you will get back the new related data as well.
Example, I have a table called movie, and a table called movie_revenue where I’m inputting Movie box office data.
Movie_revenue is related to Movie using a foreign key field, movie_revenue_id.

My POST url: /api/v2/localdata/_table/movie?related=movie_revenue_by_movie_revenue_id

And my payload:
{
“resource”: [{
“movie_title”: “Avatar”,
“movie_year”: 2009,
“movie_revenue_by_movie_revenue_id”: [{
“revenue_in_millions”: 2788
}]
}]
}

And the response back from the server:
{
“resource”: [
{
“movie_Id”: 3,
“movie_revenu_by_movie_revenue_id”: [
{
“revenue_id”: 2,
“revenue_in_millions”: 2788,
“movie_revenue_id”: 3
}
]
}
]
}

edit: sorry for the bad formatting. can’t seem to get the markdown to do what i want. paste it into jsonlint.com for pretty formatting. :unamused:


#5

Hi @drewpearce thanks for the info. I’m trying this, but I’m not getting the child record. Have converted it to put the object in as a parameter object.

var stockNewParams = {

                resource: [],

                related: 'tblstocklinein_by_fk_stockID',

                fields: '*'
            };

            stockNewParams.resource.push(n);

            console.log(stockNewParams);

            SharedFactoryStock.StockNew().create(stockNewParams, stockNewSuccess, stockNewFailure);

And When I get a response back from the server it’s just got the parent record ID and not the related tables ID.
I’ve also tried fields: as blank and fields as * and as id.


#6

this is the object I’m sending?

fields: "id"
related: "tblstocklinein_by_fk_stockID"
resource: Array[1]
0: Object
$$hashKey: “uiGrid-017A”
_rowStatus: "NEW"
agr: 1.97
avgLenCurrent: 91.31
avgLenIn: 91.31
avgWtCurrent: 145
avgWtIn: 145
commentIn: ""
dateIn: "2016-01-18"
daysInStock: NaN
fgr: 1.45
fk_companyID: "{COMPANY_ID}"
fk_openedByTaskID: 181
fk_qualityIndicatorID: null
fk_siteDepartmentID: 28
fk_siteDepartmentPositionID: 11764
fk_siteID: "2"
fk_stockBatchID: "139"
fk_stockGroupID: "1257"
id: null
isActiveStock: false
isIntermediaryStock: false
mgr: undefined
numCurrent: 113.79
numIn: 113.79
rgrIn: 0.93
sku: "67"
tblstocklinein_by_fk_stockID: Array[1]
tempGUID: "52a8ceec-f6e5-4421-b0dd-9cf52b8229d1"
wtCurrent: 16500
wtIn: 16500


#7

Should I not be using something like: https://github.com/dreamfactorysoftware/dsp-core/wiki/SQL-Related-Data#getting-the-related-data

Where we have to related - Comma-delimited list of relations to return for each record in response, or “" to retrieve all related records. By default, all fields of the related record(s) are returned. Optional fields, limit, and order can be sent for each relation. The following separate URL parameters apply to each relationship given…
<relation_name>.fields - A comma-delimited list of fields to return in the response for the related record(s). If this parameter is set to "
” or missing, all fields will be returned. Setting it to empty ("") will result in just the primary key field(s) and value(s) being returned.
<relation_name>.limit - An integer count value limiting the number of related records of the given relationship to return in the response. Default is unlimited until max response size met.
<relation_name>.order - Declares the ‘order by’ field and direction for sorting the related results per record per relationship.


#8

So now I’m trying send the following object to DreamFactory and still no related table?
fields: "id,tempGUID"
related: "tblstocklinein_by_fk_stockID"
resource: Array[1]
0: Object
length: 1
proto: Array[0]
tblstocklinein_by_fk_stockID.fields: “id,tempGUID”


#9

martin, is this on version 1.9?


#10

Hi Drew, sorry man, it’s the latest version, my bad, i didn’t have the “related” parameter pulling through to my angular factory, fixed it and the related data came through. Thanks man!

Is there a way to specify the individual fields on the related table?