Searching userrecords

[sandrab]

Hi,

We created a user record type with a custom field to store the id of an external system. Next, we want to create a webhook that finds user records of this type by the external id and updates some other custom field in this record.
We tried with code like this:

Code: Select all

import org.cyclos.entities.users.User
import org.cyclos.entities.users.UserRecord
import org.cyclos.entities.users.UserRecordType
import org.cyclos.model.system.fields.CustomFieldVO
import org.cyclos.model.system.fields.CustomFieldValueForSearchDTO
import org.cyclos.model.users.records.UserRecordQuery
import org.cyclos.model.users.recordtypes.RecordTypeVO
import org.cyclos.model.users.users.UserVO

// Set up the variables for this example - in reality these come from different sources.
String userName = 'johnDoe'
String recordTypeName = 'exampleRecord'
String someIdentifierField = 'someExternalId'
String identifierValue = 'abcdef'
String someOtherField = 'someVisibleField'

try{
    // Find the userrecord of type exampleRecord for the given user which has the given external identifier as customField value.
    def user = conversionHandler.convert(User, userName)
    UserRecordType userRecordType = entityManagerHandler.find(UserRecordType, recordTypeName)
    def q = new UserRecordQuery()
    q.type = new RecordTypeVO(userRecordType.id)
    q.user = new UserVO(user.id)
    q.setUnlimited()
    def param = new CustomFieldValueForSearchDTO()
    def customField = new CustomFieldVO()
    customField.internalName = "${recordTypeName}.${someIdentifierField}"
    param.setField(customField)
    param.setStringValue("\"${identifierValue}\"")
    def params = new HashSet<CustomFieldValueForSearchDTO>()
    params.add(param)
    q.setCustomValues(params)
    def recordsFound = recordService.search(q)
    // We should find exactly one record. If not, throw an exception.
    if (recordsFound?.totalCount != 1) {
        throw new Exception("Search for ${recordTypeName} userrecord for user ${user.username} and ${someIdentifierField} ${identifierValue} resulted in ${recordsFound?.totalCount} records.")
    }
   
    // Update other information in our exampleRecord.
    UserRecord userRecord = entityManagerHandler.find(UserRecord, recordsFound[0].id)
    def exampleRecordInfo = scriptHelper.wrap(userRecord)
    exampleRecordInfo."${someOtherField}" = "someValue"
    return "${recordTypeName} is updated."
} catch (Exception e){
    return "Exception with message: ${e.getMessage()}"
}

Although this code seems to work, we have some questions you might be able to help us with.

a) Would this (using a UserRecordQuery like this) be the best way to search userrecords, or is there a better/easier/faster way, you think?

b) Searching on a custom field like this only works when the custom field is set to 'Include as search filter'. That means it becomes visible in the search filter in the list of userrecords. We would not want that, because in our case the external identifier is not very relevant to people searching in userrecords (although we don't want to hide the field from them altogether); only for the underlying checks in the code it is relevant. Would there be a way to make this work without the field showing up in the search filter?

c) As an extra security measure we may want to check the external identifier is never used by anyone else. We could probably use the code above and leave out the line that filters the query by user:

Code: Select all

q.user = new UserVO(user.id)

This would give us all userrecords containing the external identifier in the custom field, for any user. Would this become a performance issue if there would be a lot of user records over time? Or is the performance of the query not influenced by searching directly on the given user or not?

Thanks so much!
Sandra

Using Cyclos 4.11.1

[luis]

If you want to make sure a record field value is unique, just mark it as unique in the field definition. It will then be unique among all records of this type, regardless of the user.

I'd suggest a different approach: find the record using a JPQL and make sure its user is the expected one. It would be something like this:

Code: Select all

import org.cyclos.entities.users.QRecordCustomFieldValue
import org.cyclos.entities.users.QUserRecord
import org.cyclos.entities.users.RecordCustomField
import org.cyclos.entities.users.User
import org.cyclos.entities.users.UserRecord
import org.cyclos.entities.users.UserRecordType
import org.cyclos.model.ValidationException

// Set up the variables for this example - in reality these come from different sources.
String userName = 'johnDoe'
String recordTypeName = 'exampleRecord'
String someIdentifierField = 'someExternalId'
String identifierValue = 'abcdef'
String someOtherField = 'someVisibleField'

// Fetch the record with this value
QRecordCustomFieldValue fv = QRecordCustomFieldValue.recordCustomFieldValue
UserRecord record = entityManagerHandler.from(fv)
    .select(fv.owner())
    .where(
        fv.owner().type().internalName.eq(recordTypeName),
        fv.field().internalName.eq(someIdentifierField),
        fv.stringValue.eq(identifierValue))
    .fetchFirst()

if (record == null || record.user.username != userName) {
    throw new ValidationException("The record with identifier $identifierValue doesn't exist or doesn't belong to user $userName")    
}

def exampleRecordInfo = scriptHelper.wrap(record)
exampleRecordInfo."${someOtherField}" = "someValue"
return "${recordTypeName} is updated."

[sandrab]

This is great, thank you so much, Luis!

Any chance you might add the DBQuery class to the scripting api docs? For now, I just made an educated guess that I could also use 'fetch()' to retrieve all results instead of just one result with 'fetchFirst()'. But maybe there is more handy info available on the DBQuery class. I found martin.rueegg asked the same thing a while ago. Would there be a specific reason this class is not in the api docs?

[luis]

Sure, I've added it to the task list for version 4.12.
Thanks for noticing it.