How to order by a column with NULL values in it due to leftJoin? If you have two fields on a content type, such as "publications" and "featured publications" and you want to display a single list merging the two fields with the featured content on top.

I'm using two left joins from the base entity table, which leaves the delta non-featured items as NULL in the results set.

The problem I ran into is that orderBy on a delta column doesn't work with NULL values as NULL values will appear before any value with an integer in it.

So given a query like this:

$query = $this-&gt;database-&gt;select('publication_field_data', 'pfd')
      -&gt;fields('pfd', ['id']);

    $query-&gt;leftJoin('node__field_publications', 'fp', 'fp.field_publications_target_id = pfd.id');
    $query-&gt;leftJoin('node__field_featured_publications', 'ffp', 'ffp.field_featured_publications_target_id = pfd.id');

    $query-&gt;orConditionGroup()
      -&gt;condition('ffp.entity_id', $current_nid)
      -&gt;condition('fp.entity_id ', $current_nid);
    // Sorting by deltas on the featured field, then the non-feature field
    // and then the non-featured field will fail, as the NULL values
    // on the featured field will appear first.
    $query-&gt;orderBy('ffp.delta');
    $query-&gt;orderBy('fp.delta');


You won't get the proper order.

So the question is, how can we fix the query so that it will order properly, first showing the featured items, then the non-featured items (and not showing duplicates).

As I'm using paging, you can just use two queries.
One solution I found is based on this response:
https://stackoverflow.com/a/4326579/1214689

$query-&gt;addExpression('ffp.delta IS NULL', 'featured_delta_is_null');
$query-&gt;orderBy('featured_delta_is_null');
$query-&gt;orderBy('ffp.delta');
$query-&gt;orderBy('fp.delta');


Since sorting on the featured delta will place items with Null values at the top of the list, we need to sort featured delta in two pieces, first by those that aren't NULL (by converting to a boolean, those are lower than the ones that are NULL), then sort on the actual delta for those values that aren't null, this will leave all the other values out to be sorted by the delta on the non featured ('field_publications') field.