Help interrogating users DB

Hello lovely MODX community.
I need help with something.

Unfortunately the guy who worked on the modx backend at our firm has left and now i’m stuck with a request from a client which i don’t know how to tackle. I’m mainly a frontend guy but i know my way around things and i might have a gist of the solution… I just need some support on the actual implementation.

Existing situation
The website is on Revo 2.6.5 and has several contexts, user groups, and a lot of users which are imported through a csv file that also carries extra fields.

Request
A page with a search field connected to a specific and unique extra user field that retrieves other details from the corresponding user. So there should never be more than 1 result anyway.

Example:
The search field queries for “client_code”, an extra field that is unique to every user.
The query would have to find the user corresponding to such “client_code” and:

  1. check if the user is active
  2. check if the user is present in a specific set of user groups
  3. print results such as the user’s fullname, email, extra_field_1, extra_field_2, etc.

Execution
What i had in mind is a 2 snippets systems (something like simplesearch):

[[!searchSnippet &userGroups=ID1,ID2 &tpl=tpl-of-the-search-field &resultsPage=ID]]
The idea is to put here the IDs of the user groups to check upon, a tpl with the search form (for styling and layout purposes) and the ID of the results page (i wouldnt bother with ajax or presenting results on the same page)

[[!resultsSnippet &tpl=tpl-of-the-result]]
This would have to retrieve the results of the query and print values in the tpl like:

  • [[+client_code]] <-- this is the same value as the initial query
  • [[+fullname]]
  • [[+email]]
  • [[+extra_field_1]]
  • Member of [name of userGroup the user is in] with code [[+extra_field_2]]
  • Bonus -> An error message if no user is found among the specified user groups.

The problem now is i have NO IDEA of how to even get started with these snippets.
Has anyone already made something like this or can guide me through?
Maybe there’s already a plugin for this which i don’t know of?

I’m also open to different solution but i tought this was the simplest iteration with the tools and knowledge i have at my disposal.

Thanks for your time.

So where exactly is this unique extra user field “client_code” saved in the database?
As an “Extended Field” (database table “modx_user_attributes” -> column “extended”)?
In a custom database table?
This is crucial to determine how to query your users.

I think you only need 1 snippet (on 1 page).
In the snippet, check if there is a $_GET or $_POST-parameter. Then query the users and if you find one, output the user data.

Exactly. Most of the field are in the extended field column.

I’ll just go with a real life example, these are the actual db tables i think i have to interrogate. (details have been changed for privacy reasons )

modx_users
id = 559
username = AT00044
active = 1

modx_user_attributes
id = 559
internalKey = 559
fullname = Mario Rossi
email = mario@rossi.it
extended =
{
“UserImport”:
{
“Date”: “2017-03-16 18:06:20”,
“Key”: “uik58cac608c52aa6.82096252”
},
“legal_code”: “MRRRS55M24H501V”,
“fiscal_code”: “069XD90589892”,
"client_code": “68100401662”,
other fields…
}

modx_member_group
usergroup = 8
member = 559

modx_membergroup_names
id = 8
name = Group Name


The process would be something like:

  • I query 68100401662 limiting the search to userGroups 8 and 9.
  • search for the value inside the extended column in modx_user_attributes
  • if found, get the corresponding id
  • parse that id into modx_users to check if it’s active.
  • if active then check if it’s a member of either group 8 or 9 in modx_member_group
  • if found then print out its username, fullname, email, fiscal_code, legal_code, and other fields from modx_user_attributes
  • Also print out “Group Name” from modx_membergroup_names

Example output:
Mario Rossi (AT00044)
mario@rossi.it
069XD90589892 / MRRRS55M24H501V
Member of Group Name with client code: 68100401662

I think i got the logic right… but i have absolutely no idea on how to start getting this done in PHP.

Extended Fiels are not ideal to query the database. Is there any chance you could use another field for the “client_code”? (Maybe a field like “fax” that is probably empty anyway?)

To query the users you could use code like this:

<?php
$output = '';
$search_value = '68100401662';
$query = $modx->newQuery('modUser');
$query->where(array(
    'modUser.active' => 1,
    'UserGroupMembers.user_group:IN' => array(8,9),
    'Profile.extended:LIKE' => '%' . $search_value . '%'
    
));
$users = $modx->getCollectionGraph('modUser', '{"Profile":{}, "UserGroupMembers": {}}', $query);
foreach ($users as $user)
{
    $profile = $user->getOne('Profile');
    $fullname = $profile->get('fullname');
    $email = $profile->get('email');
    $extended = $profile->get('extended');
    $client_code = $extended["client_code"];
    
    $output .= '<li>' . $fullname . '|' . $email . '|' . $client_code . '</li>';
}
return $output;

It uses LIKE to find the “client_code” which is not optimal!

1 Like

That’s a great starting point. I’ll try playing around from here :slight_smile:

Ok!! So far so good.

It’s working!! And here’s what i tweaked so far:

page content

<form method="post" action="">
<input type="text" name="userCode" placeholder="Codice Fiscale" value="">
<input type="submit" name="submit" value="Cerca" />
</form>
[[!getUserDetails? &userGroups=`10,11`]]

getUserDetails Snippet

//init
$output ="";

//if form has been submitted
if(isset($_POST['submit'])){

  // get form input
  $input = $_POST['userCode'];

  // initialize query
  $query = $modx->newQuery('modUser');

  // query conditions
  $query->where(array(
    'modUser.active' => 1, // user is active
    'UserGroupMembers.user_group:IN' => explode(",", $userGroups), // user in array of groups
    'Profile.extended:LIKE' => '%' . $input . '%', // parse input into extended field
  ));

  // get list of users
  $users = $modx->getCollectionGraph('modUser','{"Profile":{}, "UserGroupMembers": {}}',$query);

// if results are found
  if($users){ 

    // has results
    $output .='Hai cercato:' . $input . '';

    // outputs results
    foreach ($users as $user) {

      $properties = $user->toArray();

      // profile details
      $profile = $user->getOne('Profile');
      $extended = $profile->get('extended');
      $properties['username'] = $user->get('username');
      $properties['fullname'] = $profile->get('fullname');
      $properties['email'] = $profile->get('email');
      $properties['codicefiscale'] = $extended["codice_fiscale_dati_attivita"];

      // group details
      // $group = ???
      // $groupname = $group->get('name');

      //$output .= '<div>' . $username . '-' . $fullname . '<br>' . $email . '<br>' . $codice_fiscale_dati_attivita . '<br>' . '</div>';
      $output .= $modx->getChunk('userDetails',$properties);


    } 

  }else{

    // no results
    $output .='Nessun risultato';

  }

}
// do nothing
else{};

// final output
return $output;

userDetails Chunk

<div>
<strong>Codice Fiscale:</strong> [[+codicefiscale]]<br>
<strong>Nome e Cognome:</strong> [[+fullname]]<br>
<strong>Email:</strong> [[+email]]<br>
<strong>Codice cliente:</strong> [[+username]]<br>
</div>

What i miss

  • Group details: i’d like to print the name of whichever group the user is in. Notice that each user will only be in 1 of the groups in the array so i don’t need to account for redundancy or cycle through results. It’ll either be a member of group 10 or 11 (as for the example)

Try this:

$chunk_vars = array(); //Array with all the values for the chunk
$chunk_vars['username'] = $user->get('username');
$chunk_vars['fullname'] = $profile->get('fullname');
$chunk_vars['email'] = $profile->get('email');

$chunk_vars['codice_fiscale_dati_attivita'] = $extended["codice_fiscale_dati_attivita"];
$chunk_vars['groupname'] = $user->getOne('PrimaryGroup')->get('name');

$output .= $modx->getChunk('userDetails',$chunk_vars);
1 Like

Sorry i was editing the post as i understood that i had to declare the chunk placeholders as properties beforehand.

So the “output to chunk” part is done :slight_smile:

Just missing the group name thing.

$properties['groupname'] = $user->getOne('PrimaryGroup')->get('name'); throws me a php error.

The thing is the users of the site are in several groups (even 7-8 of them at a time) but for the purpose of this snippet the query is done with an array of groups that a user is never in at the same time.

So what i’d need to do is:

Search for a user in group 10 or 11.
User is found in group 10 -> print group 10 name.

I suppose i’ll have to do a separate query to parse the actual user id to the members of each group in the array. Am i right?

Maybe this works:

$group_members = $user->getMany('UserGroupMembers');
foreach ($group_members as $group_member){
	$properties['groupname'] = $group_member->getOne('UserGroup')->get('name');
}
1 Like

It does work indeed!!!
That’s a wrap!

Thanks a lot for the amazing help and support!!!

Because you are querying the database with LIKE' => '%' . $input . '%', it is probably a good idea to check the value of client_code again in the foreach loop, to make sure it matches the search value exactly. (In the current implementation a search string like “client_code” would return all the users.)

if ($extended["client_code"] == $input){
    //found correct user
}

Additionally you should also sanitize the search value and you could do some additional checks before you query the database.

$input = $modx->sanitizeString($_POST['userCode']); //sanitize value
if (!preg_match("/^\d{10,}$/",$input)){ //verify the search value
	return "search string has to be 10 digits or longer";
}
1 Like

The double check and sanitation are actually good suggestions :slight_smile:

I also changed the query to LIKE' => '%"client_code":"' . $input . '"%' to hit that exact part of the extended field. This also works as a check so that the input is a complete value and not just part of it. I was also hitting unwanted and multiple results if i searched for “city” or any other extra fields, so this also fixes that.

I’ve learned a lot today!!
Thank you so much :slight_smile:

I owe you a beer :beer: