Syntax for the various query modifiers
Your question is 2-fold. 1 part of your question is drupal based, the other is (MySQL) SQL based ...

That line of code is as follows:


$query-&gt;orderby[0]: adds an ORDER BY clause fragment to the SQL query string. More could be tacked onto using php such as $query-&gt;orderby[1] or $query-&gt;orderby[123] ... these just build up an ordered php array of orderby sql clauses ...
The SQL expression FIELD(node.type, 'story', 'page', 'productTypeC', 'productTypeD') is used by MySQL within the ORDER BY clause and takes an additional value of DESC or ASC. That function uses the cardinality of the passed in parameters as a weighted list of values for orderby. You are correct in how FIELD() is ordering the results ....


You may order by any selected field in SQL or by an aggregate function in MySQL. Not all DBMS have the same functionality, for example as seen here the above FIELD() function in MySQL has to be emulated in Postgres:
https://stackoverflow.com/questions/1309624/simulating-mysqls-order-by-field-in-postgresql

So far as I'm aware there is no list of all the functions or values one could order by ...