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