Views query alter add orderby function FIELD()
There is a similar answer here: https://drupal.stackexchange.com/a/200662/79998

You should either use $query-&gt;addField() to add the expression and then append or unshift in $query-&gt;orderby array,
or you can use directly $query-&gt;addOrderBy() to add the expression and order, if it's ok to be added last.

$query-&gt;addOrderBy(NULL, 'FIELD(node_field_data.nid,3,2,1,4)', 'ASC', 'order_field');