Can my data have relationships?


#1

Yes. DreamFactory supports arbitrary data relationships including parent-child and many-to-many via foreign key relationships. You can download arrays of objects and related objects in a single query. You can explicitly manage the relationships, but we also provide pre-built arrays of objects and related objects in JSON arrays. When you manipulate the array and call the update service all of the relationships are automatically updated for you.

These blog posts provide some examples.

We may also implement multi-table joins that do not have a foreign key relationship. Let us know your specific use cases for multi-table joins.


#2

Hi Ben,

would you mind updating your blog-post about relational schema?

It doesn’t work with the current addressbook, since there are upper/lowercase differences. Other than the stated relation, this one works:

contactinfos_by_contactId,contactrelationships_by_contactId

andy


#3

Thanks for catching that. I’ll update accordingly.


#4

Hey Andy,

Per the original post, ContactInfos_by_contactId,ContactGroups_by_ContactRelationships works fine for me and returns JSON below with 200 successful.

{
  "record": [
    {
      "contactId": 1,
      "firstName": "Jon",
      "lastName": "Yang",
      "imageUrl": "",
      "twitter": "@jon24",
      "skype": "jon24",
      "notes": "Marital Status: M   Gender: M   Salary: 90000 Children: 2 Education:  Bachelors Occupation:  Professional",
      "ContactInfos_by_contactId": [
        {
          "infoId": 1,
          "ordinal": 0,
          "contactId": 1,
          "infoType": "Home",
          "phone": null,
          "email": "jon24@Home.com",
          "address": "3761 N. 14th St",
          "city": "MEDINA",
          "state": "ND",
          "zip": "58467",
          "country": "USA"
        },
        {
          "infoId": 2,
          "ordinal": 0,
          "contactId": 1,
          "infoType": "Work",
          "phone": null,
          "email": "jon24@Work.com",
          "address": "2243 W St.",
          "city": "MEDINA",
          "state": "ND",
          "zip": "58467",
          "country": "USA"
        },
        {
          "infoId": 3,
          "ordinal": 0,
          "contactId": 1,
          "infoType": "Mobile",
          "phone": null,
          "email": "jon24@Mobile.com",
          "address": "5844 Linden Land",
          "city": "MEDINA",
          "state": "ND",
          "zip": "58467",
          "country": "USA"
        }
      ],
      "ContactGroups_by_ContactRelationships": [
        {
          "contactGroupId": 7,
          "groupName": "Mid West"
        }
      ]
    },
    {
      "contactId": 3,
      "firstName": "Ruben",
      "lastName": "Torres",
      "imageUrl": "",
      "twitter": "@ruben35",
      "skype": "ruben35",
      "notes": "Marital Status: M   Gender: M   Salary: 60000 Children: 3 Education:  Bachelors Occupation:  Professional",
      "ContactInfos_by_contactId": [
        {
          "infoId": 7,
          "ordinal": 0,
          "contactId": 3,
          "infoType": "Home",
          "phone": null,
          "email": "ruben35@Home.com",
          "address": "2612 Berry Dr",
          "city": "RESEDA",
          "state": "CA",
          "zip": "91335",
          "country": "USA"
        },
        {
          "infoId": 8,
          "ordinal": 0,
          "contactId": 3,
          "infoType": "Work",
          "phone": null,
          "email": "ruben35@Work.com",
          "address": "942 Brook Street",
          "city": "RESEDA",
          "state": "CA",
          "zip": "91335",
          "country": "USA"
        },
        {
          "infoId": 9,
          "ordinal": 0,
          "contactId": 3,
          "infoType": "Mobile",
          "phone": null,
          "email": "ruben35@Mobile.com",
          "address": "624 Peabody Road",
          "city": "RESEDA",
          "state": "CA",
          "zip": "91335",
          "country": "USA"
        }
      ],
      "ContactGroups_by_ContactRelationships": [
        {
          "contactGroupId": 7,
          "groupName": "Mid West"
        }
      ]
    }
  ]
}