Outputting different data using the same PDO query. I'm officially confused

Development
1

I apologize for the lengthy post, but I felt this was the only way to show what has happened, why and what I’ve done so far.

I’m getting different results on two different pages using the same code which should be producing the same results. I have no idea what the problem is now. I’ve cleared the cache, the global cache, manually cleared the core/cache directory and even moved to a testing server. Nothing has worked. Here’s what I’ve done.

The Situation
Recently we made a change that connected an HR system to MODX by means of a JSON file and an importer. This change made it so that the old TV for Last Name (AttorneyLastName) could not be changed manually. So an override field was included (AttorneyLastNameAlias) to update it on the front end to show preferred names rather than legal names.

The Obstacle
Old lists that were previously sorting by AttorneyLastName were now showing some people out of order because someone with a preferred name starting with “M” is now within “P” because of their legal name. I figured the PDO query needed to pick only from a predefined, comma-separated list of resources and sort by that specific order.

The Solution
Create a list that I could import into an array, deconstruct and build my comma-separated list.

Here I pulled all people from the parent resources using the last name variables to determine what the preferred names were and their associated resource ID numbers. This was outputted to the “order” placeholder for the next step.

[[pdoResources? 
    &parents=`186,187,188`
    &depth=`1`
    &tpl =`@INLINE [[+AttorneyLastNameAlias:notempty=`[[+AttorneyLastNameAlias]]`:default=`[[+AttorneyLastName]]`]]|[[+id]]`
    &outputSeparator=`||`
    &includeTVs=`AttorneyLastName,AttorneyLastNameAlias`
    &tvPrefix=``
    &totalVar=`totalAttorneyOrder`
    &limit=`1000`
    &select = `{"modResource":"id,pagetitle,uri,template"}`
    &where=`{"template:IN":[16,49]}`
    &toPlaceholder=`order`
]]

With the double pipe being the record separator and the single pipe to divide the name from the ID, this would be produced:

Abramson|636||Cass|637||Basil|640||Sandridge|641

The order placeholder was inserted into a snippet called “output2array” and placed within &resources and &sortby in the next query:

[[pdoResources? 
    &parents=`186,187,188`
    &depth=`1`
    &resources=`[[output2array? &list=`[[+order]]`]]`
    &tpl =`@INLINE <tr><td>[[+id]]</td><td>[[+AttorneyLastNameAlias:notempty=`[[+AttorneyLastNameAlias]]`:default=`[[+AttorneyLastName]]`]]</td><td>[[+AttorneyFirstNameAlias:notempty=`[[+AttorneyFirstNameAlias]]`:default=`[[+AttorneyFirstName]]`]]</td></tr>`
    &includeTVs=`AttorneyLastName,AttorneyLastNameAlias,AttorneyFirstName,AttorneyFirstNameAlias`
    &tvPrefix=``
    &totalVar=`totalAttorneys`
    &sortby=`FIELD(modResource.id, [[output2array? &list=`[[+order]]`]] )`
    &sortdir=`ASC`
    &limit=`500`
    &select = `{"modResource":"id,pagetitle,uri,template"}`
    &where=`{"template:IN":[16,49]}`
    &toPlaceholder=`results`
]]

The snippet might be a little overkill, but it works for what I needed:

<?php
// import the pdoResources list
$arr = $list;

// explode the pdoResources list by the double pipe
$newlist = (explode("||",$arr));

//sort the new list alphabetically ignoring case
natcasesort ($newlist);

// define parts of new list and create exploded list
$explodeList = '';
foreach ($newlist as $listID => $lname) :
    $item = trim(explode('|', $lname)[1]);
    $explodeList .= "$item,";
endforeach;

// explode list for the second time by the comma and create the final list that will build the modx resource ID order
$finalList = (explode(",",$explodeList));
array_pop($finalList);

$idList = '';
$numItems = count($finalList);
$i = 0;
foreach ($finalList as $listID => $modID) :
    if(++$i === $numItems) : $idList .= "$modID";
    else : $idList .= "$modID,";
    endif;
endforeach;

// return the string of modx resource ids as presorted by the True attorney last name
return $idList;

In the end, $idList outputted a string of 400+ resource ids presorted by preferred last name and creates the list located here exactly as expected. https://dev.bipc.com/testtest

However, when placed where I need it to go, it ignores the AttorneyLastNameAlias TV and outputs a different list as seen here: https://dev.bipc.com/people/

This is verified by the resource ID #7826. On the test page, it places it in the M’s where it should go with the last name “Madani”. But on the people page it shows up with the Ps with the last name Pratico which is NOT the preferred name.

Here’s where it got even more confusing to me, when I replaced the snippet within the second PDO query with the 400+ numerical string, it puts her in the right spot but keeps the “Pratico” last name signifying that it’s ignoring that TV somehow. But further still, I tried to display the table on the People page referencing the content area of the test page using a FastField, thinking it would just display the content as shown there. Nope! It still shows it as if it were on the people page. Now my mind is broken.

Again, sorry for the lengthy post but any feedback would be appreciated.

2

This seems all way to complicated. If I understand the code correctly, then you basically want to sort by AttorneyLastNameAlias and if AttorneyLastNameAlias is empty, use the value of AttorneyLastName instead.

I believe this should be possible by just changing the sortby property in the pdoResources call.
Maybe this works:

&sortby=`IF (AttorneyLastNameAlias IS NULL, AttorneyLastName, AttorneyLastNameAlias)`
&sortdir=`ASC`

or this:

&sortby=`IF (AttorneyLastNameAlias <> '', AttorneyLastNameAlias , AttorneyLastName)`
1 Like
3

The first one worked like a charm! It was still showing the legal name despite being sorted properly. I added it to a chunk instead of an @INLINE command and then it worked too. How did you know to do that? I’ve searched all over the KB and never saw that. Is it just a form of MySQL or something?

So I also applied it to the actual list that shows now on https://dev.bipc.com/people. She now shows up at the start of the M’s as expected, but when I use the alpha filters at the top, she is still listed under P. Then when I click “ALL”, she is listed under both M & P. This is really strange.

4

Maybe it’s possible to give the LastNameAlias field a default value equal to the LastName field, that way there is always a value for that tv. Then you can stop using the LastName tv in any queries or display filters.

1 Like
5

I think, that would simplyfie things alot, when you just store the LastName into the LastNameAlias, when its empty.

6

@bruno17 Are you suggesting a plugin tied to OnDocFormSave that fills the LastNameAlias TV from the LastName TV if the former is empty?

7

That’s going to be too complicated for some of the people using it. I already suggested having a third TV “preferredLastName” that is populated on save like that but it hidden from view and using that to do the sorting and everything. And that is why this thread exists.

8

What I’m suggesting would not require anything more from editors than what you seem to already have in place.

As I understand it, you have a NameAlias TV that is used to supply a different value than the Name TV. My suggestion is to not allow the NameAlias TV to be empty: it either has a user supplied value or, if no alias is needed, a default value equal to the Name TV.

I was thinking possibly the default value property of TVs could be utilized to accomplish this, but also sounds like it would be possible to do using a plug-in. In either case it would be automatically set – no user interaction necessary unless the alias is needed.

9

I understand what you’re saying. I’m concerned that there will be confusion the way it’s already organized in the TV resource panel. I want to amend this suggestion slightly, suppose I create a 3rd TV that we keep hidden from view. This TV would be “preferredLastName” and when a resource saves, it auto populates this field with the LastNameAlias if filled and if not, it uses LastName.

Now, I already know how to populate that field on save, but is there a way to set the default value to something like this too? Just so I don’t have to go and resave every resource. If not, is there a way to tell MODX to trigger a save for a few hundred resources without needed to go into everything one at a time?

10

You probably have to write a snippet that you run once to create the new TV value for all the necessary resources.

Use $resource->getTVValue('AttorneyLastName'); to read the values of the existing TVs and $resource->setTVValue('preferredLastName', 'the value'); to create the new TV.

Or maybe read/write the values using the class modTemplateVarResource.

Or if you familiar with SQL, you could do the same with a SQL INSERT INTO Statement.

11

I’m not positive about this but I think that unless you set the TV to allow a blank value, if one somehow ends up blank, the user will be unable to save the resource (and probably won’t get any indication of why it won’t save).

12

Here’s a thread from the old forum talking about using a template tag as the default value for a TV. Maybe something in it will be useful. There are some plugin examples if you go to the second page.