Another sort issue

[digitex]

I'm having trouble with custom sorting by field value.

On a page behind a login wall I have a list of members that sort by last name A-Z by default with a drop down select to allow users to change the sort to last name Z-A or by street. The last name sort works but when sorting by street, which is just a text field containing a street name in title case, I get very odd results. It will sort A-Z by street name but omit some of the members and stick them in at the end, also in alphabetical order but separated from the rest. As far as I can see, there's nothing different about the values in those fields, I even cleared a few and re-entered the data to see if there was some gremlin character in there, but no effect.

To illustrate:

Street:

     Alpha St.
     Beta St.
     Foxtrot St.
     Golf St.
     Michael St.
     Tango St.
     Uniform St.
     Whiskey St.
     Alpha St.
     Foxtrot St.
     Hotel St.
     Tango St.

The code is:

<?php
if($input->get->order) {
    $us = $sanitizer->selectorValue($input->get->order);
    $input->whitelist('order', $us);
    if($us == "az") {
        $sort = "last_name";
    } elseif($us == "za") {
        $sort = "-last_name";
    } elseif($us == "add") {
        $sort = "street";
    }
} else {
    $sort = "last_name";
}
include("./head.inc"); ?>

    <div class="container">
        <div class="member-directory">
        <?php
            echo "<h1>{$page->title}</h1>\n" . $page->body;
            
            $members = $pages->find("template=user, roles=member, limit=100, sort=$sort");
            $paginate = $members->renderPager();
            echo "<div class=\"page-controls\">{$paginate}\n";
            echo "<div class=\"filter\">\n
                <select onchange=\"window.location.href=jQuery(this).val()\" name=\"order\" id=\"order\">
                <option value=\"?order=az\"";
                if($input->get->order && $us == 'az') { echo " selected=\"selected\""; }
                echo ">Alphabetical A-Z</option>";
                echo "<option value=\"?order=za\"";
                if($input->get->order && $us == 'za') { echo " selected=\"selected\""; }
                echo ">Alphabetical Z-A</option>";
                echo "<option value=\"?order=add\"";
                if($input->get->order && $us == 'add') { echo " selected=\"selected\""; }
                echo ">Street Name</option>";
                echo "</select>\n</div>\n</div><!-- end of page-control -->\n";
            echo "<ul class=\"member-list\">";
                foreach($members as $member) {
                    echo "<li class=\"member-item\"><div class=\"member-name\">{$member->last_name}, {$member->first_name}</div><div class=\"member-address\">{$member->street_number} {$member->street}</div></li>";
                }
                echo "</ul>";
                echo $paginate;
        ?>
        </div><!-- end member-directory -->
    </div><!-- end container -->
<?php include("./foot.inc"); ?>

Has anyone encountered anything similar to this? Is there something in the selector that should change or something missing from the selector?

Any help would be appreciated. The site is live and I thought it was working until someone pointed this out. I'm not sure if something changed to cause it or if I just didn't notice.

[wbmnfktr]

I don't know if this helps or if you already tried it but...

• .. does $pages->find("template=user, roles=member, limit=100, sort=street") work on its own?
• .. does your example work when there is no limit in it?
• .. does it work without pagination?
• .. are those wrong sorted entries in any kind special or do they have something in common?
• .. does it work in your dev-environment?

[digitex]

17 minutes ago, wbmnfktr said:

I don't know if this helps or if you already tried it but...

• .. does $pages->find("template=user, roles=member, limit=100, sort=street") work on its own?
• .. does your example work when there is no limit in it?
• .. does it work without pagination?
• .. are those wrong sorted entries in any kind special or do they have something in common?
• .. does it work in your dev-environment?

No I get the same result when using street directly in the selector.

I did try it with the limit removed which effectively removes the pagination as well. If there's something different about the wrong sorted entries I can't see it. They were all imported by csv at the same time from the same file created in the same way.

The server it's on is the dev-environment. I just moved the necessary files to the root when publishing.

I also tried

foreach($members->sort($sort) as $member) {
                    echo "<li class=\"member-item\"><div class=\"member-name\">{$member->last_name}, {$member->first_name}</div><div class=\"member-address\">{$member->street_number} {$member->street}</div></li>";
                }

Which did not work either.

[wbmnfktr]

Did you look into the database itself to verify that there aren't any gremlins in those street names?

What happens when you look for pages containing the streets that cause an sort issue?
Do you find all entries or only a few?

There is one thing... ok, that shouldn't be the problem but... does ProcessWire stumble over street and street_number?

[digitex]

I know what the problem is. I went into phpmyadmin and looked directly at the field value in the table. Most of the values have a leading space before the street name, the ones that do not are the ones appearing at the tail end of the list. The space is not visible when you view the field in the admin.

@ryan Is this a bug in the Import Pages From CSV module? The CSV file I imported had no leading space and I did a zap gremlins (BBEdit) before import. Although I can't verify that the zap gremlins command actually did what it says it will do.

Is there an easy way to fix it or do I have to go through manually and remove the space?

Edited to add: @wbmnfktr I was drafting this post as you were posting your question. You got it. A phantom space in the database.

[wbmnfktr]

Oh... kay... as I use the CSV import module a lot I should check my sites to verify this issue. 

I never noticed any extra space so far but I will have a closer look now.

[wbmnfktr]

Checked some sites and no extra space. Even imported some new entries into text fields.

Import Pages from CSV 1.0.6
ProcessWire 3.0.99

[digitex]

I fixed it in phpmyadmin. I haven't seen this behaviour with Import from CSV before either. If it's not a bug, and it likely isn't, the fact that the space doesn't appear in the admin when it does appear in phpmyadmin and effects sorting, I think is an issue.