Hi,
Is it possible to make one json with multiple rows by the primary field?
For example, when one parent has two children, I’d like to return just one json with array that has all names.
{
“parentname”: “test”,
“kids”: [
{
“name”: “first”,
“age”: 8
},
{
“name”: “second”,
“age”: 5
}
]
}
The SQL query results are
test first 8
test second 5
Thanks!
Do these tables have a relationship in their schema?
Yes, they are connected by foreign key, and I can create a view to join them.
In the DreamFactory API you can simply include the url parameter related=* to pull all related data 1 level of relationship away from the primary query.
I’m not sure if this would format the data you wish though. Give it a shot.
It’s not working.
I can get all records but each record has patient name inside.
Hey Jasmine, I’m not sure I understand what you’re trying to accomplish. Let me show you what I did as an example and then you can let me know how it deviates from your intended results.
I create a table called parents. It has 2 fields, id (auto increment int) and name (varchar(64)).
I then created a table called children. It has 3 fields, id (auto increment int), parentid (foreign key constrained to parents.id), and name (varchar(64)).
Here is the schema dump from DreamFactory:
{
"name": "parents",
"label": "Parents",
"plural": "Parents",
"primary_key": "id",
"name_field": null,
"field": [
{
"name": "id",
"label": "id",
"type": "id",
"db_type": "int(11)",
"length": 11,
"precision": 11,
"scale": 0,
"default": null,
"required": false,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": true,
"is_primary_key": true,
"is_foreign_key": false,
"ref_table": "",
"ref_fields": "",
"validation": null,
"value": []
},
{
"name": "name",
"label": "name",
"type": "text",
"db_type": "text",
"length": 0,
"precision": 0,
"scale": 0,
"default": null,
"required": false,
"allow_null": true,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_foreign_key": false,
"ref_table": "",
"ref_fields": "",
"validation": null,
"value": []
}
],
"related": [
{
"name": "childrens_by_parentid",
"type": "has_many",
"ref_table": "children",
"ref_field": "parentid",
"field": "id"
}
],
"access": [
"GET",
"POST",
"PUT",
"PATCH",
"MERGE",
"DELETE"
]
}
{
"name": "children",
"label": "Children",
"plural": "Childrens",
"primary_key": "id",
"name_field": null,
"field": [
{
"name": "id",
"label": "id",
"type": "id",
"db_type": "int(11)",
"length": 11,
"precision": 11,
"scale": 0,
"default": null,
"required": false,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": true,
"is_primary_key": true,
"is_foreign_key": false,
"ref_table": "",
"ref_fields": "",
"validation": null,
"value": []
},
{
"name": "parentid",
"label": "parentid",
"type": "reference",
"db_type": "int(11)",
"length": 11,
"precision": 11,
"scale": 0,
"default": null,
"required": true,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_foreign_key": true,
"ref_table": "parents",
"ref_fields": "id",
"validation": null,
"value": []
},
{
"name": "name",
"label": "name",
"type": "string",
"db_type": "varchar(64)",
"length": 64,
"precision": 64,
"scale": 0,
"default": null,
"required": true,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_foreign_key": false,
"ref_table": "",
"ref_fields": "",
"validation": null,
"value": []
}
],
"related": [
{
"name": "parents_by_parentid",
"type": "belongs_to",
"ref_table": "parents",
"ref_field": "id",
"field": "parentid"
}
],
"access": [
"GET",
"POST",
"PUT",
"PATCH",
"MERGE",
"DELETE"
]
}
I have a record of a parent named Bob. Bob has two children named Bill and Barney. When I do a GET on his record (record 2,) this is the result
GET http://dsp/rest/db/parents/2?related=*
{
"id": 2,
"name": "Bob",
"childrens_by_parentid": [
{
"id": 3,
"parentid": 2,
"name": "Bill"
},
{
"id": 4,
"parentid": 2,
"name": "Barney"
}
]
}That’s exactly what I want.
And I found the foreign key was missing on my test DB. Sorry about that.
Now it’s working perfectly.
Thank you so much!
awesomesauce! glad it’s working for you
One more question!
If the parent table has 2 dependant tables then all tables data should be there, right?
I defined foreign keys on both, but I can see only one table data. I set “related=*”
And what’s the relationship name? Is it the foreign key name?
Thanks again,
Jasmine
I have tested using Chrome Postman and it’s working for multiple dependants.
It looks like some caching issue. I did refresh Schema and restarted Apache web server but it didn’t help.
About relationship names, I figured it out but not sure how it created.
Hi Is it possible to get response while I POST json using stored procedure.
(edit: new topic opened here: How to get response when I POST using sql store procedure)