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->database->select('publication_field_data', 'pfd') ->fields('pfd', ['id']); $query->leftJoin('node__field_publications', 'fp', 'fp.field_publications_target_id = pfd.id'); $query->leftJoin('node__field_featured_publications', 'ffp', 'ffp.field_featured_publications_target_id = pfd.id'); $query->orConditionGroup() ->condition('ffp.entity_id', $current_nid) ->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->orderBy('ffp.delta'); $query->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.