2

I'm running wordpress.

So have a comma separated string of post IDs and need to order found posts by the order which they are listed in the string.

Without sql query I can reach this simply by adding 'orderby' => 'post__in' in arguments.

But I need to do it via sql query. Here is my SQL query:

$idList = '2,50,10,25,150,1200,356';
$sql = "SELECT * FROM wp_posts WHERE ID IN ($idList)";

Above query return results in growing order, like this: $idList = '2,10,25,50,150,356,1200'; but I need how they are ordered in my string. Didn't find how I can do the same in sql query. Any ideas please?

1000111
  • 13,169
  • 2
  • 28
  • 37
aiddev
  • 1,409
  • 2
  • 24
  • 56
  • 2
    MySQL server or sql-server? MySQL has the FIELD function. you can try `SELECT * FROM wp_posts WHERE ID IN (2,50,10,25,150,1200,356) ORDER BY FIELD(ID, 2,50,10,25,150,1200,356)` – Raymond Nijland Nov 03 '17 at 15:56

2 Answers2

3

There is a way to define a custom ORDER BY order in mySQL by using FIELD() 

SELECT * FROM wp_posts WHERE ID IN ($idList) ORDER BY field(ID,$idList);

EDIT :

Check out this https://stackoverflow.com/a/9378709/5671807. I am quoting from

Note however, that

1 - It makes your SQL less portable, as other DBMSs might not have such function. 2- When your list of languages (or other values to sort by) gets much longer, it's better to have a separate table with sortorder column for them, and join it to your queries for ordering.

AnouarZ
  • 1,087
  • 8
  • 23
0

You can use ...ORDER BY FIND_IN_SET(ID, $idList).

Query:

SELECT * 
 FROM wp_posts 
WHERE ID IN ($idList)
ORDER BY FIND_IN_SET(ID, $idList)

Note:

FIND_IN_SET() function

This function returns 0 when search string does not exist in the string list and returns NULL if either of the arguments is NULL.

Syntax:

FIND_IN_SET (search string, string list)
1000111
  • 13,169
  • 2
  • 28
  • 37