I have a view that show all contents of type NEWS NEWS has like fields :

Content = Title

Scheduler = publish-on

and in sort criteria : I want sort contents with two option : With scheduler (publish-on = DESC) and With post date

I want sort content with scheduler, and when scheduler field is empty he sort with post date. I have created hook views query alter and I have a problem, how can I sort field with condition if publish_on is empty then sort with postdate

function custom_sort_views_query_alter(&$view, &$query) {

    if($view->name === 'articleview') {
        $view->query->add_where(1,'publish_on',  'NOT IN');
        $view->query->orderby[0]['field'] = "CASE WHEN publish_on IS NULL THEN created ELSE publish_on END";
        $view->query->orderby[0]['direction'] = "DESC";
        $view->query->orderby[1]['field'] = "created";
        $view->query->orderby[1]['direction'] = "DESC";
  • publish_on empty for some records or for all records? – Adrian Cid Almaguer Feb 4 '16 at 0:10
  • for some records – Salaktarus Feb 4 '16 at 0:12
  • Then if some records have the publish_on field empty sort all the records with postdate? – Adrian Cid Almaguer Feb 4 '16 at 0:19
  • if some records have the publish_on field empty sort those empty records with postdate, and records who have publish_on field not empty sort with publish_on field ! – Salaktarus Feb 4 '16 at 0:21
  • and you use postgres or mysql? – Adrian Cid Almaguer Feb 4 '16 at 0:25

The solution that comes to my mind now (after your question's comments) is make two queries, one query with the records that have publish_on field empty and the other with the field non empty and then use a UNION between them.

Why this? AFAIK You can't sort some records in a query based in a condition and other records based in another condition, so to achieve this you must split the records and apply the sort method separately. This is records of a condition first and records of another condition before, but you can't merge the records and the order condition.

Maybe this posts can help you:




Maybe you also want to read http://www.w3schools.com/sql/sql_union.asp

  • like this ? : function custom_sort_views_query_alter(&$view, &$query) { if($view->name === 'articleview') { $date1 = $view->query = "publish_on"; $date2 = $view->query = "created";; if(!empty($date1)){ $view->query->orderby[0]['field'] = "publish_on"; $view->query->orderby[0]['direction'] = "DESC"; }else{ $view->query->orderby[1]['field'] = "created"; $view->query->orderby[1]['direction'] = "DESC"; } } } – Salaktarus Feb 4 '16 at 0:57
  • @Salaktarus I edit with aditional info – Adrian Cid Almaguer Feb 4 '16 at 1:07
  • ok, but i don't understand what you means when you say : so to achieve this you must split the records and apply the sort method separately. – Salaktarus Feb 4 '16 at 1:13
  • the problem is postdate has a field in table at database and publish_on from other table – Salaktarus Feb 4 '16 at 1:21
  • @Salaktarus read this w3schools.com/sql/sql_union.asp – Adrian Cid Almaguer Feb 4 '16 at 2:08

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.