Lookup Key Value in database

Connecting to SQL
1

Can i work with lookup keys in DF database?
When i put simple value like ‘1’ i get in database value like this: “3BoL5oV75bzrHKdLSyU8Tm75DhA79ScGr9I1l1jIa9o=”. What is this? Can i code and decode this value in sql database?

I need generate lookup keys for user automaticaly by SQL…

2

I’m not sure I follow your example. Can you provide the full API call and response from your DreamFactory instance? Does it behave the same way when tested in API Docs?

3

My question was not related to usage of API call. I want to setup lookup keys for each user. When i do this by DreamFactory admin interface, i’m adding a new lookup key, indicate its name (for exmple, “Key1”) and value (for example, “1”). And its working.

But, i need to create this key automatically for each user, when user registers. I think, i can do it by SQL trigger in database layer and the result, there is not need to change DreamFactory php-scripts. But when i see lookup keys in database table, which where created by DreamFactory admin interface, i see values like “3BoL5oV75bzrHKdLSyU8Tm75DhA79ScGr9I1l1jIa9o=” and i realise, that value was encrypted.

How can i generate lookup keys automatically for each user?

1 Like
4

I want to setup lookup keys for each user. When i do this by DreamFactory admin interface, i’m adding a new lookup key, indicate its name (for exmple, “Key1”) and value (for example, “1”). And its working.

But, i need to create this key automatically for each user, when user registers. I think, i can do it by SQL trigger in database layer and the result, there is not need to change DreamFactory php-scripts. But when i see lookup keys in database table, which where created by DreamFactory admin interface, i see values like “3BoL5oV75bzrHKdLSyU8Tm75DhA79ScGr9I1l1jIa9o=” and i realise, that value was encrypted.

How can i generate lookup keys automatically for each user?

5

Our engineers mocked up an example for you.

This script is designed to trigger on user.register.post.post_process. Guest access and open registration will need to be enabled and configured. System/user script access will need to be added to the selected guest and open registration role. Then try registering a new user.

var user_data, result;

user_data = {
    "lookup_keys": [
        {
            "name": "somekey",
            "value": "somevalue",
            "private": false,
            "allow_user_update": false
        }
    ]
};

result = platform.api.get("system/user?fields=id", {"filter": "email='" + event.request.body.email + "'"});
var_dump(result);
if (result.record && result.record.length === 1) {
    user_data.id = result.record[0].id;
    result = platform.api.patch("system/user", user_data);
    var_dump(result);
}
1 Like
6

Thanks @jeffreystables, it did really works.

ps*. DF 1.9.4

1 Like
7

nice question @juniorconte. @jeffreystables is it possible to query user lookup using php? i know the platform object isn’t available for PHP (yet?), but I’m hoping there’s some other way to query user lookup keys on the fly through a custom script.

8

Does there happen to be a PHP iteration of this code? I’m trying to work with this V8JS example, but Ubuntu (linux) is giving me a hard time with the ‘module “lodash” cannot be found’ error.

9

Is there a way to retrieve these with GET system/user? I tried the PATCH system/user with this payload through test_rest:

[{“id”:32,“lookup_keys”:[{
“name”:“mykey”,
“value”:“myvalue”,
“private”:false,
“allow_user_update”:true
}]}]

It appears to work correctly, the response is a resource object with the same ID. The value doesn’t seem to show up anywhere, making me wonder if it did anything at all. It’s not in the user lookups admin (that’s where it should be, right?) and GET system/user doesn’t return it either. Am I looking in the right place(s)?

Is there a chance for a more deterministic and better-documented API for lookups? This is critical functionality for my application and I would imagine for many others’ applications as well. I may be able to help write a branch or even some documentation.

1 Like
10

If you’re missing Lodash, you can get it at lodash.com. There should be a CDN link, add a script include in your master template (or the template for that page). It’s a good library for manipulating arrays and objects, I use it often when I do JS-heavy sites.

Do you still need PHP code?

1 Like
11

@ssamuel I’ve been experiencing the same issue as you and have found that in DreamFactory 2.1.2 it appears that “lookup_keys” has now been super seeded with a related table called “user_lookup_by_user_id”. Please try the following:

var user_data, result;

user_data = {
    "user_lookup_by_user_id": [
        {
            "name": "somekey",
            "value": "somevalue",
            "private": false,
            "allow_user_update": false
        }
    ]
};

result = platform.api.get("system/user?fields=id", {"filter": "email='" + event.request.body.email + "'"});
var_dump(result);
if (result.record && result.record.length === 1) {
    user_data.id = result.record[0].id;
    result = platform.api.patch("system/user?related=user_lookup_by_user_id", user_data);
    var_dump(result);
}

You will find that you can now do a GET Request with system/user?related=user_lookup_by_user_id which will return the fields you are after.

Hope this helps.

1 Like