hook_views_query_alter if date field empty sort with another field postdate

Question

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

Answer 1

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:

https://stackoverflow.com/q/2258376/3653989

https://stackoverflow.com/q/19296047/3653989

https://stackoverflow.com/q/3730093/3653989

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