By charlesydr on

hi,

I created a content type and a view associated with it, there is a field expiration_date, and for some nodes, this field is empty meaning no expiration date is available.

In a table view, when the nodes are sorted according to expiration_date in a ascending order, those nodes without expiration date come first, however , I could not find a way to make them come at the last.

A more general question is the ordering for empty fields, how to define make them ordered higher rather than lower.

Thanks for your inputs.

⋅ Categories: Drupal 6.x

Comments

charlesydr’s picture

charlesydr commented

has been struggling with the problem, help/insight appreciate!

Equ’s picture

Equ commented

The same problem here. Any suggestions?

Equ’s picture

Equ commented

I've solved this problem by altering a query. Here is what I did in case if someone else needs this:

<?php

function myModuleName_views_pre_execute(&$view) {
    if($view->name == 'myViewName') {
        $view->build_info['query']=str_replace("ORDER BY my_field_name_value", "ORDER BY ISNULL(my_field_name_value), my_field_name_value", $view->build_info['query']);
    }
}

?>

You can find the exact name of the field (my_field_name_value) by looking at $view->build_info['query'] (the line which starts with ORDER BY).

interestingaftermath’s picture

interestingaftermath commented

Another solution

if ($view->name == 'myView') {			
$query->orderby[3] = "CASE WHEN myFieldName IS NULL THEN 0 END, myFieldName ASC"; }

The 3 in this case was because I already had an orderby 0, 1 & 2

span’s picture

span commented

Thanks, worked great with some minor adjustments that were specific to my view. :D

crantok’s picture

crantok commented

I couldn't get the above to work, nor many other things (see notes below if you're interested). I assume, looking at the examples, that the Views API has changed.

My case was different in one respect. I was sorting strings (each containing date and time with most significant digit first). I wanted the dates in ascending order with those rows containing an empty string to appear at the end. Anyway, my solution was this...

1) I added the string field twice as (ascending) sort criteria.

2) After adding the sort field, I could see its alias in the view SQL. I used that alias in the module code below.

3) module code...

Important note: The empty string must use single quotes (''). Using double quotes ("") breaks something internally in Views and screws up the ORDER BY clause.

function MY_MODULE_views_query_alter( $view, $query ) {
  if ( $view->name == 'MY_VIEW' ) {
    $view->query->orderby[0]['field'] =
      "(NAME_OF_STRING_FIELD_ALIAS_AS_IT_APPEARS_IN_THE_VIEW = '')";
  }
}

Before implementing this solution, I tried using hook_views_post_build() and hook_views_pre_execute() both with direct property manipulation and with calling the query->add_orderby() method. In none of those four combinations could I get my custom sort criterion to work. I also tried using the DB API's hook_query_alter() (as described here), but failed at that too. If anyone has a working solution for any of those then I would be very grateful if I could see your code.

geerlingguy’s picture

geerlingguy commented

Awesome solution, works perfectly; one thing that I did differently was set the first sort as 'descending' instead of 'ascending', so that after I add the extra orderby in hook_views_query_alter(), the rows with a date value show first (blank dates show at the end of the view).

__________________
Personal site: www.jeffgeerling.com

gold’s picture

gold
they/them
Location 20 minutes in the future
commented

This is still an out of the box issue in Drupal 8.x.

The Views Sort NULL field module does sort this out though.  If you are using postgresql you will also want to check issue #2840400 for a 7.x and 8.x patch that fixes the lack of ISNULL() in that db.

--
Regards
Gold
Drupal Code Monkey