I'm using hook_views_query_alter() and am wondering where I can find the syntax for the various query modifiers.

One of the examples I found looked like this:

$query->orderby[0] = "FIELD(node.type, 'story', 'page', 'productTypeC', 'productTypeD') ASC";

I'm not sure what that syntax is saying. Does it mean order by the node.type field, where nodes with story are first, then page, and so on? Or something else? I need to be able to group on multiple fields (and not all fields are on the initial node (relationships.)

Is there somewhere that all of the available modifiers are documented, including their syntax? By modifiers, I mean $query->orderby, $query->where, and so on.


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:

  1. $query->orderby[0]: adds an ORDER BY clause fragment to the SQL query string. More could be tacked onto using php such as $query->orderby[1] or $query->orderby[123] ... these just build up an ordered php array of orderby sql clauses ...
  2. 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 ...

  • Thanks. I'm not looking for a list of values one can order by; I'm looking for a list of the modifiers available to Drupal queries: orderby is one; I'm assuming groupby is another, and I know you can add where clauses using the $query->whatever syntax.
    – EmmyS
    Jun 29 '12 at 20:36
  • ahh ... I believe Views queries are built with drupal SelectQuery objects, you have all the methods used therein (at least). It may also help to look at hook_views_post_build() prior to the query running to access the SelectQuery before its processed .... Views has alot of hooks.
    – tenken
    Jun 29 '12 at 20:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.