Views: Show all terms of taxonomy with or without containing nodes

Question

I have a view which shows all terms of a specific taxonomy (pages). My terms have a relationship with containing nodes. My nodes are grouped by taxonomy terms.

I get this result with no date filter:

taxonomy term 1
    - node 1 (2016-06)
    - node 2 (2016-07)

taxonomy term 2
    - node 3 (2016-06)

taxonomy term 3

taxonomy term 4

taxonomy term 5

taxonomy term 6
    - node 4 (2016-06)

Problem

I have an exposed date filter to get nodes for different months. But when I use this filter (2016-06), my terms with no matching nodes are not shown in my result.

taxonomy term 1
    - node 1 (2016-06)

taxonomy term 2
    - node 3 (2016-06)

taxonomy term 6
    - node 4 (2016-06)

This is the result I'm looking for, when I use my date filter (2016-06):

taxonomy term 1
    - node 1 (2016-06)

taxonomy term 2
    - node 3 (2016-06)

taxonomy term 3

taxonomy term 4

taxonomy term 5

taxonomy term 6
    - node 4 (2016-06)

So my question is:

How can I show all taxonomy terms in my view with or without containing nodes, if my filter is active?

---

UPDATE

This is my generated SQL from views when my exposed filter is active. The second where condition removes my terms.

SELECT DISTINCT 
    node_taxonomy_index.nid AS node_taxonomy_index_nid, 
    taxonomy_term_data.name AS taxonomy_term_data_name, 
    taxonomy_term_data.vid AS taxonomy_term_data_vid, 
    taxonomy_term_data.tid AS tid, 
    taxonomy_vocabulary.machine_name AS taxonomy_vocabulary_machine_name, 
    'node' AS field_data_field_position_node_entity_type, 
    'node' AS field_data_field_seite_node_entity_type, 
    'node' AS field_data_field_sebepla_status_node_entity_type, 
    'node' AS field_data_field_typ_node_entity_type, 
    'node' AS field_data_field_ausgabenzeitraum_node_entity_type
FROM 
    {taxonomy_term_data} taxonomy_term_data
LEFT JOIN {taxonomy_index} taxonomy_index 
    ON taxonomy_term_data.tid = taxonomy_index.tid
LEFT JOIN {node} node_taxonomy_index 
    ON taxonomy_index.nid = node_taxonomy_index.nid
LEFT JOIN {taxonomy_vocabulary} taxonomy_vocabulary 
    ON taxonomy_term_data.vid = taxonomy_vocabulary.vid
LEFT JOIN {field_data_field_ausgabenzeitraum} node_taxonomy_index__field_data_field_ausgabenzeitraum 
    ON node_taxonomy_index.nid = node_taxonomy_index__field_data_field_ausgabenzeitraum.entity_id AND 
        (node_taxonomy_index__field_data_field_ausgabenzeitraum.entity_type = 'node' AND 
        node_taxonomy_index__field_data_field_ausgabenzeitraum.deleted = '0')
WHERE 
    (( (taxonomy_vocabulary.machine_name IN  ('sebepla_seiten')) AND 
    (DATE_FORMAT(node_taxonomy_index__field_data_field_ausgabenzeitraum.field_ausgabenzeitraum_value, '%Y-%m') = '2016-06') ))

When I use following SQL, I get correct results:

SELECT DISTINCT 
    node.nid AS node_taxonomy_index_nid, 
    taxonomy_term_data.name AS taxonomy_term_data_name, 
    taxonomy_term_data.vid AS taxonomy_term_data_vid,
    taxonomy_term_data.tid AS tid, 
    taxonomy_vocabulary.machine_name AS taxonomy_vocabulary_machine_name,
    'node' AS field_data_field_position_node_entity_type, 
    'node' AS field_data_field_seite_node_entity_type, 
    'node' AS field_data_field_sebepla_status_node_entity_type, 
    'node' AS field_data_field_typ_node_entity_type, 
    'node' AS field_data_field_ausgabenzeitraum_node_entity_type
FROM 
    taxonomy_term_data
LEFT JOIN taxonomy_index taxonomy_index 
    ON taxonomy_term_data.tid = taxonomy_index.tid
LEFT JOIN 
    ( 
        SELECT 
            node.nid
        FROM   
            field_data_field_ausgabenzeitraum node_taxonomy_index__field_data_field_ausgabenzeitraum, 
            node
        WHERE  
            node.nid = node_taxonomy_index__field_data_field_ausgabenzeitraum.entity_id AND 
            node_taxonomy_index__field_data_field_ausgabenzeitraum.entity_type = 'node' AND 
            node_taxonomy_index__field_data_field_ausgabenzeitraum.deleted = '0' AND 
            DATE_FORMAT(node_taxonomy_index__field_data_field_ausgabenzeitraum.field_ausgabenzeitraum_value, '%Y-%m') = '2016-06'
    ) node ON taxonomy_index.nid = node.nid
LEFT JOIN 
    taxonomy_vocabulary taxonomy_vocabulary 
        ON taxonomy_term_data.vid = taxonomy_vocabulary.vid
WHERE (( (taxonomy_vocabulary.machine_name IN  ('sebepla_seiten')) ))

Now my new question:

How to implement this query in views? I know that hook_views_query_alter is the key. If someone could explain how to write a left join select with my conditions I've used in views object query, it would help me a lot! :)

Answer 1

From looking at your SQL, it looks like you're trying to build a LEFT JOIN off a subquery. This is one of those cases where you really have to get into in internals of views.

There're a few components you're looking at here:

1. Building a db_select() subquery that you're joining.
2. Utilizing views_join() to build the join to the view.
3. Using views_plugin_query_default::add_table() to insert your joined subquery table into the view.

An incomplete example would look something like this:

function mymodule_views_query_alter(&$view, &$query) {
  //$query->addJoin(j)

  // Build subquery to match expression
  $sub_query = db_select('field_data_field_ausgabenzeitraum', 'node_taxonomy_index__field_data_field_ausgabenzeitraum');
  $sub_query->fields('n', array('nid'));

  // INNER JOIN is the same as "SELECT * FROM table1, table2"
  $sub_query->addJoin('INNER', 'node', 'n');
  $sub_query->conditions('n.nid', 'node_taxonomy_index__field_data_field_ausgabenzeitraum.entity_id');
  // @TODO: Add Remaining Conditions & utilize addExpression() for DATE_FORMAT()

  // Build subquery JOIN.
  $join = new views_join();
  $join->type = 'LEFT';
  $join->definition = array('table formula' => $sub_query);
  $join->left_table = 'taxonomy_index';
  $join->left_field = 'nid';
  $join->field = 'nid';

  // Add the joined subquery table
  $query->add_table('node', NULL, $join, 'node');
}