Using orderBy correctly with hook_query_alter

Question

I have a hook_query_alter function that I'm using to edit my search.module results. It works ok so far. However, I'm having trouble adding an orderBy condition that I need. Here's the SQL I want added that I'm using successfully elsewhere in a custom module:

    ORDER BY ('fdfty.field_type_value' = 'Webinar'), 
('fdfty.field_type_value' = 'Workshop'), 
('fdfty.field_type_value'= 'Conference'), 
fdfed.field_event_dates_value, n.title

That's accepted SQL for ordering by specific values. However, when I try to add something similar to my query_alter function orderBy clause, like so:

$query->orderBy('fdft.field_type_value' = 'Conference');

Well, Drupal doesn't like the '=' sign being in there. Is there another way to include such normal SQL criteria in hook_query_alter?

Thanks!

Here's the complete code I'm working with in template.php:

// function for search results page to return correct results that meet business logic
function zen_pathfinder_query_alter(QueryAlterableInterface $query) {
    if (get_class($query) == 'PagerDefault') {
        foreach ($query ->getTables() as $table) {
            if ($table['table'] == 'search_index') {
                $query->leftjoin('field_data_field_event_dates', 'fdfed', 'n.nid = fdfed.entity_id');
                $query->leftjoin('field_data_field_type', 'fdft', 'n.nid = fdft.entity_id');
                $query->where('fdfed.field_event_dates_value >= CURRENT_DATE OR fdfed.field_event_dates_value IS NULL');
                    //->groupBy('fdft.field_type_value');
                $query->addField('fdft', 'field_type_value');
                $sql = 'CASE WHEN fdft.field_type_value = :first THEN 0 
                WHEN fdft.field_type_value = :second THEN 1 
                WHEN fdft.field_type_value = :third THEN 2
                WHEN fdft.field_type_value = :fourth THEN 3
                WHEN fdft.field_type_value = :fifth THEN 4              
                WHEN fdft.field_type_value = :sixth THEN 5 
                WHEN fdft.field_type_value = :seventh THEN 6 
                WHEN fdft.field_type_value = :eighth THEN 7 
                WHEN fdft.field_type_value = :ninth THEN 8 
                WHEN fdft.field_type_value = :tenth THEN 9              
                END';
                $query->addExpression($sql, 'my_alias', array(
                ':first' => 'Webinar',
                ':second' => 'Workshop or Training',
                ':third' => 'Conference',
                ':fourth' => 'Article or Report',
                ':fifth' => 'Blog',
                ':sixth' => 'Podcast or Webcast',
                ':seventh' => 'Newsletter or Magazine',
                ':eighth' => 'Tool',
                ':ninth' => 'Book or Workbook',     
                ':tenth' => 'Certificate or Degree Program' ,           
                ));
                $query->orderBy('my_alias');
                $query->orderBy('fdfed.field_event_dates_value');
                $query->orderBy('title');
            }
        }
    }
}

I keep getting a "Column not found: 1054 Unknown column 'my_alias' in 'order clause" error when I go to the search page. So I ran dpm((string) $query); and the results are as follows:

      SELECT SUM(i.score * t.count) AS calculated_score
        FROM 
        {search_index} i
        INNER JOIN {node} n ON n.nid = i.sid
        INNER JOIN {search_total} t ON i.word = t.word
        WHERE  (n.status = :db_condition_placeholder_0) AND( (i.word = 
:db_condition_placeholder_1) )AND (i.type = :db_condition_placeholder_2) AND (n.type NOT 
IN  (:db_condition_placeholder_3, :db_condition_placeholder_4, 
:db_condition_placeholder_5)) 
        GROUP BY i.type, i.sid
        HAVING  (COUNT(*) >= :matches) 
        ORDER BY calculated_score DESC
        LIMIT 1 OFFSET 0

        SELECT fdft.field_type_value AS field_type_value, CASE WHEN fdft.field_type_value = 
    :first THEN 0 
                        WHEN fdft.field_type_value = :second THEN 1 
                        WHEN fdft.field_type_value = :third THEN 2
                        WHEN fdft.field_type_value = :fourth THEN 3
                        WHEN fdft.field_type_value = :fifth THEN 4              
                        WHEN fdft.field_type_value = :sixth THEN 5 
                        WHEN fdft.field_type_value = :seventh THEN 6 
                        WHEN fdft.field_type_value = :eighth THEN 7 
                        WHEN fdft.field_type_value = :ninth THEN 8 
                        WHEN fdft.field_type_value = :tenth THEN 9              
                        END AS my_alias
        FROM 
        {search_index} i
        INNER JOIN {node} n ON n.nid = i.sid
        INNER JOIN {search_total} t ON i.word = t.word
        LEFT OUTER JOIN {field_data_field_event_dates} fdfed ON n.nid = fdfed.entity_id
        LEFT OUTER JOIN {field_data_field_type} fdft ON n.nid = fdft.entity_id
        WHERE  (n.status = :db_condition_placeholder_0) AND( (i.word = 
    :db_condition_placeholder_1) )AND (i.type = :db_condition_placeholder_2) AND (n.type NOT 
    IN  (:db_condition_placeholder_3, :db_condition_placeholder_4, 
    :db_condition_placeholder_5)) AND (fdfed.field_event_dates_value >= CURRENT_DATE OR 
    fdfed.field_event_dates_value IS NULL) 
        GROUP BY i.type, i.sid
        HAVING  (COUNT(*) >= :matches) 
        ORDER BY my_alias ASC, fdfed.field_event_dates_value ASC, title ASC

Answer 1

You can use SelectQueryInterface::addExpression to add a CASE statement to the select and then order by that:

$sql = '
  CASE WHEN fdfty.field_type_value = :first THEN 0 
       WHEN fdfty.field_type_value = :second THEN 1
       WHEN fdfty.field_type_value = :third THEN 2
  END';
$alias = $query->addExpression($sql, 'my_alias', array(
  ':first' => 'Webinar',
  ':second' => 'Workshop',
  ':third' => 'Conference',
);
$query->orderBy($alias, ASC);

Answer 2

Have a look at the API.

orderBy in Drupal doesn't accept operators. Instead it should be:

$query->orderBy('fdft.field_type_value', ASC);

I suggest either sorting by the key or weight of the field value (if there is one).