How do I override the default sorting when filtering a view by Taxonomy terms?

Question

My current settings …

In Drupal 6, I currently have a view that filters and sorts nodes by the following criteria:

The taxonomy term is very general, and nodes can either be assigned to "Referenzen" only, or any of its sub-nodes. This is done for the basic structuring of the site. Here, in this view though, I only want to "load all nodes with 'Referenzen' assigned", regardless of their sub-term.

The problem …

"Wichtig" ("important") is an integer value that defines the primary sort criteria for me. I do not care whether a reference is filed under "Media" or "Consulting", or any other sub-term.

However, the nodes are finally displayed like this:

Node 1   Important:  100   Taxonomy: Referenzen
Node 2   Important:    0   Taxonomy: Referenzen
Node 3   Important: -100   Taxonomy: Referenzen
Node 4   Important:  100   Taxonomy: Referenzen – Media
Node 5   Important:   80   Taxonomy: Referenzen – Media

As you can see, the "Important" factor comes second when sorting the nodes, and the taxonomy grouping comes first.

How can I change this behavior, so the following is achieved:

Node 1   Important:  100   Taxonomy: Referenzen
Node 4   Important:  100   Taxonomy: Referenzen – Media
Node 5   Important:   80   Taxonomy: Referenzen – Media
Node 2   Important:    0   Taxonomy: Referenzen
Node 3   Important: -100   Taxonomy: Referenzen

The executed query

SELECT node.nid AS nid, node.title AS node_title, node.language AS node_language, node_data_field_image.field_image_fid AS node_data_field_image_field_image_fid, node_data_field_image.field_image_list AS node_data_field_image_field_image_list, node_data_field_image.field_image_data AS node_data_field_image_field_image_data, node.type AS node_type, node.vid AS node_vid, node_revisions.teaser AS node_revisions_teaser, node_revisions.format AS node_revisions_format, node_data_field_important.field_important_value AS node_data_field_important_field_important_value, node_data_field_date.field_date_value AS node_data_field_date_field_date_value 
FROM node node  
INNER JOIN term_node term_node ON node.vid = term_node.vid 
LEFT JOIN content_field_image node_data_field_image ON node.vid = node_data_field_image.vid 
LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid 
LEFT JOIN content_field_important node_data_field_important ON node.vid = node_data_field_important.vid 
LEFT JOIN content_type_story node_data_field_date ON node.vid = node_data_field_date.vid 
WHERE (term_node.tid = 2) AND (node.language in ('***CURRENT_LANGUAGE***')) AND (node.status <> 0) 
ORDER BY node_data_field_important_field_important_value DESC, node_data_field_date_field_date_value DESC

The exported view

… can be downloaded here:

http://cl.ly/1c2c0X3f1Q2L3Y3x3l3w

MySQL Query Browser

… however tells me that those that have the "Wichtig" field not set have the value NULL and therefore are sorted last. All newly created nodes get "Wichtig" set as 0, however all old ones still have NULL.

So what can I do to make those with NULL have the value 0 instead and how can I prevent this for new articles in the future?

Answer 1

Maybe the MySQL IFNULL() function could be the solution. It allows you to check the selected field value and give it a custom one when null. In your SQL statement, instead of

node_data_field_important.field_important_value AS node_data_field_important_field_important_value

you should have:

IFNULL(node_data_field_important.field_important_value,0)  AS node_data_field_important_field_important_value

Try the modified SQL statement in your MySQL Query Browser and see if it works.

I'm an old database administrator, but a Drupal beginner, so I don't really know how to put this function in a View using the web interface. Do you?

I'm searching a way to add custom SQL to views (because as a beginner I'm interested too) and found something about hook_views_query_alter(). It belongs to Views 2 API. You should use that hook to change the corresponding array value using your exported view code as reference for array indexes. I have no time to try it, but probably this is the right way.

IMHO another way should be taking the exported view code, modify it manually adding the IFNULL() MySQL function on the field name and then reimport it into a new view. I'm not sure that Drupal will write a correct SQL query or that it won't discard some SQL it doesn't expect... This should be the same as using the hook above, but in a rude and not to so elegant manner...

Hope it helps.

Answer 2

if you want to alter views query to overwrite the default shorting

function modulename_views_query_alter(&$view, &$query){
if ( $view->name == 'test_merchandise' ) {
//here you will get the whole $query object and alter only the place you want change
$query->orderby[0] = "substring(node_data_field_date_field_date_value,1,4) DESC";
print_r($query);
}
}

Answer 3

If I understand well your question, why not validate the "Wichtig" ("important") field before be stored to store 0? If is possible, I think that you have several alternatives to validate fields:

Rules module has form support to validate fields: http://drupal.org/node/298487 (see Form support)

also, with hook_form_alter (or hook_form_FORM_ID_alter) you can validate fileds with custom modules: http://api.drupal.org/api/drupal/developer--hooks--core.php/function/hook_form_alter/6 http://api.drupal.org/api/drupal/developer--hooks--core.php/function/hook_form_FORM_ID_alter/6

this guide show you how to validate fields in custom forms http://drupal.org/node/751826

also Form API Validation allows to you validate fields: http://drupal.org/project/fapi_validation

if I am not wrong and is useful my answer, I have some experience validating with rules and hook_form_alter, so if is necessary, I can add more information inside this answer.