I have created a view for a content type "News Article". This content type has a "List (text)" field named "Type" (field_type) that can either be "Events" or "News".
It's not worth creating two different content types since all fields are the same, but each Type (event/news) will be shown in different view blocks.
A News Article has a Start date and an End date. The events view block should only show upcoming events, aka events that have not yet ended. The End date field is not required for the events that start and end on the same day, so this is my logic:
If the event has an End date, it should show up if that date has not passed; or if the event has no End date, it should show up if the Start date has not passed. On top of that, all other filters (which I won't show since they're unnecessary here) are required. And since we cannot nest filter groups, the solution is:
[ Content: Content type (= News Article) AND
Content: Type (= Event) AND
Content: End (empty) AND
Content: Start (>= +0 day) ]
OR
[ Content: Content type (= News Article) AND
Content: Type (= Event) AND
Content: End (not empty) AND
Content: End (>= +0 day) ]
The problem is, no results show up in the preview. If I test each filter group alone, it all works fine. I removed some filters, and found out the problem: It's "Content: Type (= Event)". If I only take one of these filter groups, and add "Content: Type (= Event)" a second time, all results disappear.
In the SQL query preview, I noticed these two lines. Can they be the problem?
LEFT JOIN {node__field_type} node__field_type_value_0 ON node_field_data.nid = node__field_type_value_0.entity_id AND node__field_type_value_0.field_type_value = 'Event'
LEFT JOIN {node__field_type} node__field_type_value_02 ON node_field_data.nid = node__field_type_value_02.entity_id AND node__field_type_value_02.field_type_value != 'Event'
This is the full query:
SELECT node_field_data.langcode AS node_field_data_langcode, node__field_start.field_start_value AS node__field_start_field_start_value, node__field_end.field_end_value AS node__field_end_field_end_value, node_field_data.nid AS nid, 'events_and_news:block_3' AS view_name
FROM
{node_field_data} node_field_data
LEFT JOIN {node__field_type} node__field_type_value_0 ON node_field_data.nid = node__field_type_value_0.entity_id AND node__field_type_value_0.field_type_value = 'Event'
LEFT JOIN {node__field_end} node__field_end ON node_field_data.nid = node__field_end.entity_id AND node__field_end.deleted = '0'
LEFT JOIN {node__field_type} node__field_type_value_02 ON node_field_data.nid = node__field_type_value_02.entity_id AND node__field_type_value_02.field_type_value != 'Event'
LEFT JOIN {node__field_start} node__field_start ON node_field_data.nid = node__field_start.entity_id AND node__field_start.deleted = '0'
WHERE ((node__field_type_value_02.field_type_value = 'Event')) AND (((node_field_data.status = '1') AND (node_field_data.type IN ('news_article')) AND (node_field_data.langcode IN ('en')) AND (node__field_type_value_0.field_type_value = 'Event') AND (node__field_end.field_end_value IS NOT NULL) AND ((DATE_FORMAT(node__field_end.field_end_value, '%Y-%m-%d') >= DATE_FORMAT('2020-09-04T14:57:10', '%Y-%m-%d')))) OR ((node_field_data.status = '1') AND (node_field_data.type IN ('news_article')) AND (node_field_data.langcode IN ('en')) AND (node__field_end.field_end_value IS NULL) AND ((DATE_FORMAT(node__field_start.field_start_value, '%Y-%m-%d') >= DATE_FORMAT('2020-09-04T14:57:10', '%Y-%m-%d')))))
ORDER BY node__field_start_field_start_value ASC, node__field_end_field_end_value ASC