sorting by number in "name" field of product database

Ask the community
, ,
1

Hi folks, the “name” field in my product database looks like (for ex)

1980-81 stuff #6 Joe Brown
1980-81 stuff #10 Bob Brown
1980-81 stuff #23 Luke Smith

I query the database for matches with 1980-81 and would like to have my results displayed (via a repeater) in the order above, but sorting by .ascending(“name”) doesn’t do it this way because “name” is a string (this is well documented). So I thought I could strip out the numbers from the string and sort according to that. But I don’t have the code below quite right, suggestions?

function retnum(str) {
//var num = str.replace(/[^0-9]/g, ‘’);
var num=str.match(/\d/g);
return parseInt(num, 10 );
}

$w.onReady( function () {
wixData.query( ‘Stores/Products/1980-81’ )
// Query the collection for any items whose “Name” field contains
// the value the user entered in the input element
//.contains(“name”, $w(“#searchBox”).value)
.contains( “name” , ‘1980-81’ ).ascending(retnum( “name”) )
.find() // Run the query
.then(results => {
// Set the table data to be the results of the query

originalPropertiesInfo = results.items; 
    $w(`#repeater1`).data = originalPropertiesInfo; 
  }) 
  . **catch** ((err) => {

let errorMsg = err;
});

//Set the information to the repeater …
…

});

});

2

Hi Ronald,

Maybe, you have gotten user-defined function calls to work in wixData query functions. I don’t know if it can be done. Maybe a Wix staff member can verify.

I tried to use the retnum function, but it didn’t return the correct result. So I proceeded to do it in a way that I was sure would work.

With a little manipulation of the result set using JS functions isNaN, Number, and sort, you can achieve what you want.

$w.onReady(function () {
    wixData.query('Stores/Products')
    .contains("name",'1980-81')
    .ascending("name")
    .find() 
    .then((results) => {   
       // loop through items
       for (var i = 0; i < results.items.length; i++) {
         let name = results.items[i].name;
         let intName = "";
         for (var c = 0; c < name.length; c++) {
            // now loop through characters of name string
            // and build a string of just the numbers
            if (isNaN(name.substr(c,1)) === false){
                 intName = intName + name.substr(c,1);
            }
          }
          // Use Number function on a newly added property, 
          // so it sorts right.
          results.items[i].nameSort = Number(intName);
        }
        // Apply JS sort function on the new column (property)
        results.items.sort(
        (a, b) => (a.nameSort > b.nameSort) ? 1:-1);
        console.log(results);
        $w(`#repeater1`).data = results.items;
      })
      .catch((err) => {
       let errorMsg = err;
      });
});
3

Many, many thanks I will give this a try. I can likely modify this approach to handle cases where the items are numbered some other way.