[Closed] How to disable sort in a find or findRaw query?

[dotnetic]

I want to find an array of pages in a specified order, but it seems that ProcessWire by default sorts ascending by ID, but I want to preserve the order in my query.

Is there a way to do this?

My query looks like this: 

$articles = $pages->find('id=4|2|1');

which gives me the result in this order: 1,2,4

[da²]

Sorting is managed with "sort" selector.

If you want to sort by reverse id:

$pages->find('id=4|2|1', sort='-id');

 

[dotnetic]

I don't want a reversed sort order. I want to retain the order of the id's that i specified. Take this query for example.

$pages->find('id=8|3|4|2|20|2');

I want to get the pages in this order: 8,3,4,2,20,2

[da²]

So, except if there's a magic trick I'm not aware of, you have to get the pages in correct order from the resulting PageArray, with your own code.

[da²]

Could you explain the conditions that justify this sorting?

Is it static or dynamic?

If static maybe you can solve this by manual sorting in PW admin. Then you use "sort=sort" in your selector.

 

If dynamic the only solution I see actually would be like:

$sortedIds = [5872, 5869, 5870, 5871]; // List created dynamically

$results = $pages->find('id=' . implode('|', $sortedIds));
$sortedResults = [];

foreach ($results as $result) {
//    echo $result->id . "\n";

    $nextId = array_shift($sortedIds);
    if(!$nextId) break;

    $sortedResults[] = $results->getPageByID($nextId);
}

var_dump($sortedResults);

 

[dotnetic]

16 minutes ago, da² said:

Could you explain the conditions that justify this sorting?

 

Yes,  sure. I have my custom tabulator.info integrated to select which products in which order should appear in a category of a shop like "shirts for boys, single color", "shirts for girls, multicolor", "caps", "hoodies" and more.

On the left you see a tabulator with all available products. When clicking a product, it is moved to the right tabulator, which displays the selected products.
Then you can reorder the products in that second tabulator via drag and drop.

When saving the page the id's and their order are saved to another field (selectedarticles). Later on the frontend, the products in this category should be displayed in the manually specified order.

Sure, I could have created pages for this, but this would produce much overhead (as I have many categories and for each category you can choose which products and in which order) and I only need a reference to the original product page.

21 minutes ago, da² said:

$sortedIds = [5872, 5869, 5870, 5871]; // List created dynamically

$results = $pages->find('id=' . implode('|', $sortedIds));
$sortedResults = [];

foreach ($results as $result) {
//    echo $result->id . "\n";

    $nextId = array_shift($sortedIds);
    if(!$nextId) break;

    $sortedResults[] = $results->getPageByID($nextId);
}

var_dump($sortedResults);

I did something similiar like your example code, but it would be nice, if ProcessWire would provide a sort=false option or something similar to retain the order.

[dotnetic]

Here is my articleSelector in action. 

[da²]

OK, I don't know if this is possible in PW. I searched in source code for "ORDER BY FIELD" but found nothing.

Maybe that could be a PW feature request to add selector for ORDER BY FIELD.

You can also do the MySQL request using $database API variable, but then you must do yourself values sanitization, and resolving pages references if any.

SELECT * FROM table ORDER BY FIELD(id, 5,3,6,1,4,2);

 

[ryan]

@dotnetic

The getByIDs method should do it: 

$items = $pages->getByIDs([ 4, 2, 1 ]); 

 

[dotnetic]

3 hours ago, ryan said:

The getByIDs method should do it: 

Thanks for your help, but I was a bit unspecific in my request. I actually want a findRaw and only query some fields of these pages, because it is much faster. And so getByIDs would not help me. Or can I combine it somehow with findRaw?

[ryan]

@dotnetic I don't think there's an option like that for findRaw, but you could still use findRaw, as it'd be fast and simple to get them in the order you want: 

$getIDs = [ 1014, 1, 2 ];
$getFields = [ 'id', 'name', 'title', 'url' ];
$rows = [];
$a = $pages->findRaw($getIDs, $getFields);
foreach($getIDs as $id) {
  if(isset($a[$id])) $rows[] = $a[$id];
}

 

[Robin S]

There is a way to have pages returned by $pages->find(), $pages->findRaw(), etc, in the order of some supplied IDs. You use "id.sort" in the selector: https://processwire.com/blog/posts/pw-3.0.200/#pages-api-additions

$data = $pages->findRaw('id.sort=1014|1|2', ['id', 'name', 'title', 'url']);

You have to supply the IDs for all the pages you want to match: https://github.com/processwire/processwire-issues/issues/1581

[dotnetic]

1 hour ago, Robin S said:

$data = $pages->findRaw('id.sort=1014|1|2', ['id', 'name', 'title', 'url']);

 

This is really cool and does exactly what I want. Thx.

[ryan]

@Robin S Good call! Not sure how I forgot about that one.