2

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
4

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);
  • @montaigne when you say 'my_type' what's supposed to go there? I've tried the field_type, which is the field name in the content type; I've tried field_data_field_type which is the table the type is stored in and I've tried both field_type_value itself and just 'my_type' as you typed it. I just get errors that no such column 'my_type' for example, exists. – Sage Mar 19 '15 at 3:18
  • Try the updated snippet. I had a quoting error in the SQL string and am now using placeholders. Renamed 'my_type' to 'my_alias' to clarify that this parameter is used to pass an alias for this expression that you can use later for the orderBy method. See the API and documentation for more information. – montaigne Mar 19 '15 at 12:45
  • @montaigne thank you that makes sense. However I'm still getting the error 1054 Unknown column 'my_alias' in 'order clause, which makes no sense to me. I don't know why it thinks 'my_alias' is supposed to be a column in the table...I'm updating my question with the complete code at the bottom so you can see what I'm working with. – Sage Mar 19 '15 at 23:06
  • Try adding the field's value column to the SELECT statement ($query->addField('fdft', 'field_type_value')) prior to adding the CASE expression. Also, note that you can only pass one field at a time to SelectQueryInterface::orderBy. To order by multiple fields call the method multiple times. – montaigne Mar 20 '15 at 1:51
  • @montaigne I updated those things (see code above again) and am still getting column not found error. I've ensured that's the name of the column and field in mysql, checked my search index to make sure it's part of it, flushed my caches. It just keeps giving me the same error. The only way it doesn't give me the error is if I add a return line after the end of addExpression(); like so: { return 'my_alias'; } but then it doesn't give me the results ordered. Do you think I need a return line or am I barking up the wrong tree there? – Sage Mar 20 '15 at 22:29
3

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).

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.