2

My current settings …

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

enter image description here

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.

enter image description here

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.

enter image description here

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?

  • do you have a relationship setup in this view? – uwe Oct 4 '11 at 1:19
  • Can you post the executed query and an export of the view? – Berdir Oct 4 '11 at 1:47
  • @uwe999 No, I don't, it's fairly simple. – slhck Oct 4 '11 at 7:42
  • @Berdir I updated my question. – slhck Oct 4 '11 at 7:46
  • Does the same thing happen when you run the query manually from Query Browser, or similar tool? – mpdonadio Oct 5 '11 at 0:17
2
+25

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.

  • Changing this does the trick in SQL! Now I only need to port it to the view, so everything's displayed correctly. I consider myself a Drupal beginner, so I really don't know how to do that. – slhck Oct 5 '11 at 10:57
  • you could use drupal.org/project/views_php module to be able to enter PHP code to do a custom sort. I can't give you the exact code, this is the help text for the code you need to write "The comparison code must return an integer less than, equal to, or greater than zero if the first row should respectively appear before, stay where it was compared to, or appear after the second row." – uwe Oct 5 '11 at 17:40
  • I guess I see what the problem is now. I introduced the "Wichtig" field after already creating several nodes. Thus, for those created earlier, there is no "Wichtig" stored (i.e. it's NULL), and all new ones automatically get 0 as value. I guess I'll only need a query to update all my old records to change NULL to 0. You as a database administrator can surely come up with something? – slhck Oct 6 '11 at 16:52
  • If that is the case, you can just run a regular update query like this: UPDATE node_data_field_important SET field_important_value=0 WHERE field_important_value IS NULL; You'd better try it first on a copied version of the node_data_field_important table. – Cheope Oct 11 '11 at 22:58
  • ...Or make a backup copy of the table first. Beware that those null values come from inserted (and updatable) values in a table and not from the LEFT JOIN query, that put null where it doesn't find any correspondence. – Cheope Oct 11 '11 at 23:18
1

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);
}
}

0

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.

  • I see now that for new nodes, the value is 0 automatically. It seems that NULL only exists for those that were written before I introduced the custom field. So I guess I don't need any custom validation. Still, thanks for the hints! – slhck Oct 6 '11 at 16:51
  • then, I think that if you have access to database, you can replace with 0 where is null for respective nodes. – cigotete Oct 6 '11 at 17:12

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.