Data object relationships

Please, describe your problem here.

Hi I have what seems like a simple challenge that eludes me. I really don’t know where to start.

My example- I created a CRUD app for my son and his friends that allows them to “catalog” things they see in nature, by name, color and the number of sides (for example, “sun, orange, 1” and “stop sign, red, 8”)
The database has grown quite large as he and his friends log objects each day.

I now want to assign “points” to each child’s data based on the color and number of sides of the items they logged (for example, red items get 5 points, orange items get 3 points) (8 sided items multiply that item’s color score by 1.5x, 1 sided items just get the score assigned the item’s color). The points per color and point multiplier per sides does not change.

I see this as a data object relationship issue. I imagine 3 data tables-
1 . The CRUD app data table
2. A table with schemas “color” and “pointTotal”
3. A table with schemas “sides” and “multiplier”

Question#1: is there a way to retrospectively calculate point totals in Backendless? When I set up the CRUD table, I did not know anything about data object relationships. I could simply delete the “color” schema which currently is of “string” type then recreate it with “data object relationship” and re-enter the data manually, but if there is an easier way…)

Question#2: going forward, is there a way to calculate point totals per unit time as the kids enter data? And retrieve that data to display their respective scores on the UI?

I use Appgyver as my UI builder, but I assume the UI builder could any UI builder- its the REST API that matters?

Sorry for the long question - thanks for any pointers (would love to know where to start)

Hello @Rick_Bo,

I could simply delete the “color” schema which currently is of “string” type then recreate it with “data object relationship” and re-enter the data manually, but if there is an easier way…)

An easier way would be to write a simple script that will fetch all the data from the main (CRUD) app data table and then update every single object with relation to Color and Sides tables.

Here’s a quick sketch of what such a script might look like (using JS), I haven’t tested it, but it should work.

Hope this gives you some idea.

const Backendless = require('backendless')

Backendless.initApp('you-app-id', 'you-api-key')

const ColorPointsMap = {
  red   : 1,
  orange: 2,
  blue  : 3,
  // ... so on
}

async function run() {
  const MAX_PAGE_SIZE = 100

  async function findAllRecords(tableName) {
    const total = await Backendless.Data.of(tableName).getObjectCount()

    const itemsList = []
    const requests = []

    let currentQuery = {
      pageSize: MAX_PAGE_SIZE,
      offset  : 0
    }

    while (currentQuery.offset < total) {
      requests.push(Backendless.Data.of(tableName).find(currentQuery))

      currentQuery = {
        ...currentQuery,
        offset: currentQuery.offset + MAX_PAGE_SIZE
      }
    }

    const results = await Promise.all(requests)

    results.forEach(items => {
      items.forEach(item => {
        itemsList.push(item)
      })
    })

    return itemsList
  }

  const records = await findAllRecords('Records')

  await Promise.all(records.map(async record => {
    await updateColor(record)
    await updateSides(record)
    await countPoints(record)
  }))
}

async function updateColor(record) {
  let existingColor = await Backendless.Data.of('Color').findFirst({
    where: `color = '${ record.color }'`
  })

  if (!existingColor) {
    existingColor = await Backendless.Data.of('Color').save({
      color      : record.color,
      pointsTotal: ColorPointsMap[record.color]
    })
  }

  await Backendless.Data.of('Records').setRelation(record, 'color', [existingColor])
}

async function updateSides(record) {
  let existingSideRecord = await Backendless.Data.of('Sides').findFirst({
    where: `side = '${ record.side }'`
  })

  if (!existingSideRecord) {
    existingSideRecord = await Backendless.Data.of('Sides').save({
      side      : record.side,
      multiplier: record.side === 8 ? 1.5 : 1
    })
  }

  await Backendless.Data.of('Records').setRelation(record, 'side', [existingSideRecord])
}

async function countPoints(record) {
  const data = await Backendless.Data.of('Records').findFirst({
    where     : `objectId = '${ record.objectId }'`,
    properties: ['color.pointsTotal as colorPoints', 'side.multiplier as sideMultiplier']
  })

  await Backendless.Data.of('Records').save({
    objectId: record.objectId,
    points  : data.colorPoints * data.sideMultiplier
  })
}

run()

going forward, is there a way to calculate point totals per unit time as the kids enter data?

You can create an afterCreate Event Handler and do the logic there. Basically, you’ll need to find objects from the Color and Sides tables which are corresponding to what kids are entered (by using whereClause, see code above). And then update the record object with calculated points.

And retrieve that data to display their respective scores on the UI?

You may store totalPoints directly in the main CRUD table (I called it Records in my script), also you may count it on client-side retrieving only needed information about tit:

const data = await Backendless.Data.of('Records').find({
  properties: ['*', 'color.pointsTotal as colorPoints', 'side.multiplier as sideMultiplier']
})

where side column is 1:1 relation to the Sides table and color is a 1:1 relation to the Color table.

If you have any questions, feel free to ask me here.

Cheers,
Stanislaw

Hello.
Thank you so much for taking the time to understand my question and giving a detailed answer- I really appreciate it! Backendless has an amazing community forum.
My situation is that I have no coding experience and have managed so far to create this app with a “low-code” app builder (AppGyver). I am unfamiliar with JS.
Is there a way to do the above using a “low-code” solution?
I understand you will not be able to comment on the use of AppGyver, but is there a way to achieve this solution with the Backendless Frontend UI builder? I might be able to read that solution and extrapolate to the UI builder I am more familiar with.
I will read your solution above and read up on JS to understand the solution as best I can of course.
Again, thank you for your prompt and detailed response.

Sorry, I didn’t realize that you want to get this using low code.
Answering your question - yes, all this can be done with the help of our no-code tools. I recommend that you try porting my script I wrote, except for the function that loads all the entries in one go (findAllRecords). Instead, replace it with a simple call to the table with the maximum number of pages (100). This will simplify your task if there are less than 100 records in the database at the moment. If there are more records, this is also not a problem, just put the necessary offset later. I understand that this may all sound difficult for you, start by simply porting what I wrote using the constructor in the UI Builder. Along the way, if you have any questions, do not hesitate to contact us, we will be happy to help.

Cheers,
Stanislaw

Perfect thank you. Would you know of any tutorial videos related to this? I have found videos very helpful. Thanks!

Thanks again Stanislaw.

I am taking small steps towards my goal; again, excuse my lack of knowledge. To review, I have an “items” table where kids enter items they see in nature, their color, and their shape. “Color” values are the hex codes for the color, “shape” is the number of sides the item has. I assigned “point values” to each item’s “color” in a related table and want to calculate / display these point values for each user AND add these point values as a column in the “items” table AND add the summed values for each user in the “users” table.

I just figured out how to load related objects to my “items” table using the Backendless Rest Console (screenshots attached).

The Rest Console gives me the syntax of the “request URL” to load the related objects. I now can use that request URL on my UI side of things to load / display “colorPoints” (I didn’t know how to do this before).

My questions are:

  1. now that I can load my related objects (“colorPoints” from my colorpoints table), how can I
    a. use the “aggregate functions” button on the Rest Console to, say, sum “colorPoints” values for a particular user? and POST those values to my “users” table?
    b. “POST” a new object to the “items” table that includes “color”? I tried this in the request body:
    {“item”: “donut”, “color”: “#4B0082”, “shape”: 6}, but it doesn’t add “#4B0082” to the “color” column (which has a 1:1 data object relation to the “colorpoints” table) - it seems to “ignore” my request to include “color” as if that column didn’t exist (screenshots attached).
    c. “POST” the colorPoints value for each item into the “item” table"?

Thanks for your patience!

Hello @Rick_Bo

It’s hard to understand what exactly do you want.

More precisely, it is not clear this paragraph:

want to calculate / display these point values for each user AND add these point values as a column in the “items” table AND add the summed values for each user in the “users” table.

As for your questions:
a. “aggregate functions” and your data structure is unsuitable for this issue
b. REST CONSOLE have no ability to work with add/set relations to objects
c. It doesn’t make sense. You can use request example below to get colorPoints value from “items” table

Regard,
Viktor

Thanks for your reply.

Hello.

I’ve done some additional reading into creating data object relations via REST API.

I’ve read Backendless documentation under both the “deep-save” and “relations API”. It seems to me like the “deep-save” functionality is what I will need.

The examples in the “deep-save” section show how to save an object in a “parent” table and an object in a “child” table and establish a relationship between the two.

My question: is there a way to save an object in a “parent” table and establish a relationship between a “parent” and “child” table without adding anything to the “child” table? I want my “child” table to be read-only but relate to objects in the “parent” table.

Thanks.

Hello @Rick_Bo ,

To just establish relation you should put child with objectId.
For example I have parent table Customer with property subsidary that is relation to Subsidary table. Subsidary table contains the following records:

[
	{
		"___class": "Subsidary",
		"ownerId": null,
		"updated": null,
		"created": 1622729965000,
		"objectId": "91583AE5-4570-4126-B21C-F092DA070264"
	},
	{
		"___class": "Subsidary",
		"ownerId": null,
		"updated": null,
		"created": 1622729966000,
		"objectId": "A11DFB2F-5A22-465F-A579-B07B113CC2C5"
	},
	{
		"___class": "Subsidary",
		"ownerId": null,
		"updated": null,
		"created": 1622729964000,
		"objectId": "E478BCB7-D9BE-4870-BA55-86C71BA67058"
	}
]

So to create a record in Customer table with relation to object with id 91583AE5-4570-4126-B21C-F092DA070264 I should use the following query:

curl -X PUT 'https://hotair.backendless.app/api/data/Customer/deep-save' --header 'Content-Type: application/json' \
--data-raw '{
		"subsidary": {"objectId":"91583AE5-4570-4126-B21C-F092DA070264"}
	}'
1 Like

thank you for your reply and explanation- very helpful

Thanks again Sergey.

So I tried to replicate your suggestion using a front-end UI called AppGyver.
If I may, here are screenshots of what happened:


(above are my currently empty parent “Customer” table and child “Subsidary” table)

(above I am configuring my PUT request using the /data/Customer/deep-save end to my URL)

(above I am setting the “subsidary” value to the first objectId in the child “Subsidary” table, so I am sending a key:value pair (“subsidary” : “objectId”)

(the error message I get- it seems the relational column “subsidary” in the “Customer” table is not recognized)

So I haven’t been able to get a working REST request for a PUT query (a “GET COLLECTION” query works fine- I don’t currently have any objects in the “Customer” table so it shows an empty array, but if I add objects to the table I can successfully use the GET COLLECTION" request to see them)

I know you can’t comment on AppGyver, but do you have any suggestions?

Thanks.

Thanks again Sergey.

So I was able to make your suggestion work on the REST API side- thanks!

Now let’s say the Customer table had another property “name” and the Subsidary table had another property “company”. Let’s say there are several data entries n the Subsidary table with “company” : [“starbucks”,“amazon”,“taco bell”,“macy’s”] (each with their own objectId).

Let’s say I want instead to create a record in the Customer table with “name” : “Bob” with relation to an object with “customer” : “starbucks” in the Subsidary table. How would I do that?

I tried to replicate your suggestion but the result was that it created a record “Bob” in the Customer table with relation to “starbucks” in the Subsidary table and added a duplicate “starbucks” entry in the “company” column of the Subsidary table with a different objectId (so now there are two “starbucks” entries in the Subsidary table, each with a different objectId)

Thanks.

Hello @Rick_Bo,

You have
Customer table with columns name and subsidary
Subsidary table with column company
Company table with column name

To create a record Bob in the Customer table with subsidary company starbucks, you should firstly find the existing starbucks subsidary to avoid creating a new one (it will be the additional GET request).

So the steps are:

  1. Get the existing subsidary object with company name = “starbucks”
  2. Create a Customer object with relation to the existing subsidary

Otherwise it is an expected behaviour:

it created a record “Bob” in the Customer table with relation to “starbucks” in the Subsidary table and added a duplicate “starbucks” entry in the “company” column of the Subsidary table with a different objectId (so now there are two “starbucks” entries in the Subsidary table, each with a different objectId)

Regards,
Olha