2

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! :)

  • Hi. I think you should do that programmatically... Is that an option for you? – Djouuuuh Jun 28 '16 at 14:04
  • Yeah this is an option for me. But I don't know where to start. I'll update my question. – Huelfe Jun 28 '16 at 14:29
  • There's a slight logical error/priority conflict here: "I want to show a filtered taxonomy list, but I want to show ALL items!". – Beebee Jun 28 '16 at 15:19
  • 1
    How can I show all taxonomy terms in my view with or without containing nodes, if my filter is active? might as well leave it unfiltered... – No Sssweat Jun 29 '16 at 5:43
  • I've updated the question. Hope my question is now clear. :) – Huelfe Jun 29 '16 at 7:07
2
+200

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');
}
  • Thank you for this answer! Now I get the correct results. :) – Huelfe Jul 4 '16 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.