User Custom Field - Order

REST API
1

Hello,

We can store datas in “user_data” column for users. It is in JSON format. In “user_data” column, there are sub field like score,age,weight,height… But when i want to call system/user api to list the users, how can i order them according to their “scores” in “user_data”?

Is there a function for JSON parsing or something else?

Thanks,

2

I believe this would need to be accomplished client-side rather than in the query. You could receive the JSON, then sort it in your app, then present it to the user.

3

Alternatively, if you are able to store the data in any other table, you could simply store your additional user data in the separate table and query it as related data with an ownerId field in the extra table.

4

Hello Jeffrey, that is what i want exactly. I created another table for additional user data. I created a relation from phpmyadmin with the df_sys_user id field and other my custom table id. But in live api, i couldn’t get the data. Do you have any idea?

5

Could you post the API call you’re making and the result/error that was returned?

6

I am making a call from system/user api endpoint to get the users table and related table.

7

Also i get this error in phpmyadmin when i want to create a relationship with the custom table’s foreign key with df_system_user’ id field.

#1452 - Cannot add or update a child row: a foreign key constraint fails (bitnami_dreamfactory.#sql-9c9_2cb2, CONSTRAINT #sql-9c9_2cb2_ibfk_2 FOREIGN KEY (ownerid) REFERENCES df_sys_user (id))

8

I will attempt to get an example mocked up for you of how to accomplish this.

9

I will be waiting for it. Thank you so much jeffrey.

10

I have confirmed that, for security, relationship queries with user data are not supported. We are looking into a way to simulate a related data call to the user table with server-side scripts. Of course you’re welcome to investigate this as well.

My thought is that a post-process script on the user table call could also call the additional table and concatenate the response from that table, then sort. What do you think?

11

Is it something that can be done(in latest dreamfactory versions 2.3 or 2.4) to create a separate table (in mysql for example) containing a owner_id field which is a foreign key to user_id in user table - by virtual foreign key(I tried but didnt see the user table available in schema tab) or something similar.

I am working on a project which require many fields for a user, and these fields should be created/read/edited by another user via rest api.

Any ideas?