I'm not too sure if this one is a bug, so please excuse me if I misunderstand something.

Description

A relationship has been created between a content type and another using a standard node reference field.

The same field is used in the two content types.

I created exposed filters on both instances of the field, one direct and one via the relationship, both in an OR group.

When using one filter or the other, everything's fine : the view returns the values matching the criteria.

But when using both filters with the same value as before, the query doesn't return anything, where it should return results matching any of the criteria.

Wanabe analysis

Looking at the query, it appears the field doesn't get aliased two times, like if Views would consider it beeing always the same instance.

I tried to add a second relationship to the second content type, and then recreate the exposed filter using the second relationship, but the result is the same.

SQL Query

Here is the query produced when using both filters with the view setup with two relationships :

SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.language AS node_language, node_field_data_field_formation_certification.nid AS node_field_data_field_formation_certification_nid, node_field_data_field_formation_greta.nid AS node_field_data_field_formation_greta_nid, taxonomy_term_data_field_data_field_dipl_formacode.tid AS taxonomy_term_data_field_data_field_dipl_formacode_tid, node_field_data_field_formation_certification_1.nid AS node_field_data_field_formation_certification_1_nid, 'node' AS field_data_field_formation_certification_node_entity_type, 'node' AS field_data_field_formation_greta_node_entity_type, 'node' AS field_data_field_formation_lieu_node_entity_type, 'node' AS field_data_field_dipl_formacode_node_entity_type, 'node' AS field_data_field_dipl_formacode2_node_entity_type, 'node' AS field_data_field_formation_tag_node_entity_type, 'node' AS field_data_field_greta_territoire_node_entity_type, 'node' AS field_data_field_dipl_domaine_node_entity_type, 'taxonomy_term' AS field_data_field_formacode_rome_taxonomy_term_entity_type
FROM 
{node} node
LEFT JOIN {field_data_field_formation_certification} field_data_field_formation_certification ON node.nid = field_data_field_formation_certification.entity_id AND (field_data_field_formation_certification.entity_type = 'node' AND field_data_field_formation_certification.deleted = '0')
LEFT JOIN {node} node_field_data_field_formation_certification ON field_data_field_formation_certification.field_formation_certification_nid = node_field_data_field_formation_certification.nid
LEFT JOIN {field_data_field_formation_certification} field_data_field_formation_certification2 ON node.nid = field_data_field_formation_certification2.entity_id AND (field_data_field_formation_certification2.entity_type = 'node' AND field_data_field_formation_certification2.deleted = '0')
LEFT JOIN {node} node_field_data_field_formation_certification_1 ON field_data_field_formation_certification2.field_formation_certification_nid = node_field_data_field_formation_certification_1.nid
LEFT JOIN {field_data_field_formation_greta} field_data_field_formation_greta ON node.nid = field_data_field_formation_greta.entity_id AND (field_data_field_formation_greta.entity_type = 'node' AND field_data_field_formation_greta.deleted = '0')
LEFT JOIN {node} node_field_data_field_formation_greta ON field_data_field_formation_greta.field_formation_greta_nid = node_field_data_field_formation_greta.nid
LEFT JOIN {field_data_field_dipl_formacode} field_data_field_dipl_formacode ON node.nid = field_data_field_dipl_formacode.entity_id AND (field_data_field_dipl_formacode.entity_type = 'node' AND field_data_field_dipl_formacode.deleted = '0')
LEFT JOIN {taxonomy_term_data} taxonomy_term_data_field_data_field_dipl_formacode ON field_data_field_dipl_formacode.field_dipl_formacode_tid = taxonomy_term_data_field_data_field_dipl_formacode.tid
LEFT JOIN {field_data_field_dipl_formacode2} field_data_field_dipl_formacode2 ON node.nid = field_data_field_dipl_formacode2.entity_id AND (field_data_field_dipl_formacode2.entity_type = 'node' AND field_data_field_dipl_formacode2.deleted = '0')
LEFT JOIN {taxonomy_term_data} taxonomy_term_data_field_data_field_dipl_formacode2 ON field_data_field_dipl_formacode2.field_dipl_formacode2_tid = taxonomy_term_data_field_data_field_dipl_formacode2.tid
LEFT JOIN {field_data_field_dipl_formacode} node_field_data_field_formation_certification__field_data_field_dipl_formacode ON node_field_data_field_formation_certification.nid = node_field_data_field_formation_certification__field_data_field_dipl_formacode.entity_id AND (node_field_data_field_formation_certification__field_data_field_dipl_formacode.entity_type = 'node' AND node_field_data_field_formation_certification__field_data_field_dipl_formacode.deleted = '0')
LEFT JOIN {taxonomy_term_data} taxonomy_term_data_field_data_field_dipl_formacode_1 ON node_field_data_field_formation_certification__field_data_field_dipl_formacode.field_dipl_formacode_tid = taxonomy_term_data_field_data_field_dipl_formacode_1.tid
LEFT JOIN {field_data_field_dipl_formacode2} node_field_data_field_formation_certification__field_data_field_dipl_formacode2 ON node_field_data_field_formation_certification.nid = node_field_data_field_formation_certification__field_data_field_dipl_formacode2.entity_id AND (node_field_data_field_formation_certification__field_data_field_dipl_formacode2.entity_type = 'node' AND node_field_data_field_formation_certification__field_data_field_dipl_formacode2.deleted = '0')
LEFT JOIN {taxonomy_term_data} taxonomy_term_data_field_data_field_dipl_formacode2_1 ON node_field_data_field_formation_certification__field_data_field_dipl_formacode2.field_dipl_formacode2_tid = taxonomy_term_data_field_data_field_dipl_formacode2_1.tid
LEFT JOIN {field_data_field_formation_tag} field_data_field_formation_tag ON node.nid = field_data_field_formation_tag.entity_id AND (field_data_field_formation_tag.entity_type = 'node' AND field_data_field_formation_tag.deleted = '0')
LEFT JOIN {taxonomy_term_data} taxonomy_term_data_field_data_field_formation_tag ON field_data_field_formation_tag.field_formation_tag_tid = taxonomy_term_data_field_data_field_formation_tag.tid
LEFT JOIN {field_data_field_dipl_domaine} node_field_data_field_formation_certification_1__field_data_field_dipl_domaine ON node_field_data_field_formation_certification_1.nid = node_field_data_field_formation_certification_1__field_data_field_dipl_domaine.entity_id AND (node_field_data_field_formation_certification_1__field_data_field_dipl_domaine.entity_type = 'node' AND node_field_data_field_formation_certification_1__field_data_field_dipl_domaine.deleted = '0')
LEFT JOIN {taxonomy_term_data} taxonomy_term_data_field_data_field_dipl_domaine ON node_field_data_field_formation_certification_1__field_data_field_dipl_domaine.field_dipl_domaine_tid = taxonomy_term_data_field_data_field_dipl_domaine.tid
INNER JOIN {field_data_field_dipl_domaine} field_data_field_dipl_domaine ON node.nid = field_data_field_dipl_domaine.entity_id AND (field_data_field_dipl_domaine.entity_type = 'node' AND field_data_field_dipl_domaine.deleted = '0')
INNER JOIN {field_data_field_dipl_domaine} node_field_data_field_formation_certification_1__field_data_field_dipl_domaine2 ON node_field_data_field_formation_certification_1.nid = node_field_data_field_formation_certification_1__field_data_field_dipl_domaine2.entity_id AND (node_field_data_field_formation_certification_1__field_data_field_dipl_domaine2.entity_type = 'node' AND node_field_data_field_formation_certification_1__field_data_field_dipl_domaine2.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN  ('actions')) )AND( (field_data_field_dipl_domaine.field_dipl_domaine_tid = '36635') OR (node_field_data_field_formation_certification_1__field_data_field_dipl_domaine2.field_dipl_domaine_tid = '36679') ))
ORDER BY node_title ASC
CommentFileSizeAuthor
#14 views-fix_broken_or-1766338-14.patch1.53 KBlaryn
#12 views-fix_broken_or-1766338-12.patch1.46 KBjenlampton
#10 views-fixes_broken_inner_join-1428158-10.patch930 bytesjenlampton
#7 inner_join_produces_unexpected_results-1428158-7.patch1.07 KBtmin
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Countzero’s picture

Countzero CreditAttribution: Countzero commented
Category: bug » support

Based on dereine advice in another issue, I'm trying to code a handler for this use case.

Goal

Use a taxonomy multiselect exposed filter to query two different entity types which happen to be related to each other. The field to query is the same in the two entity types, with different instances. The result should be a dataset with entities of the first type : those where the field matches the criteria, those which are related to an entity of the second type where the field matches the criteria .

Done so far

The handler is recognized and works OK :

 // my_module.views.inc
function numerizen_views_data_alter(&$data) {
  $data['field_data_field_dipl_domaine']['domaine'] = array(
    'title' => t('Domaine Actions et Diplômes'),
    'group' => 'My Module',
    'help' => t('Le domaine'),
   'real field' => 'field_dipl_domaine_tid',
   'filter' => array(
      'handler' => 'my_module_handler_filter_domaines',
    ),    
  );

 // my_module_handler_filter_domaines.inc
class my_module_handler_filter_domaines extends views_handler_filter_term_node_tid {
  function query($group_by = FALSE) {
      parent::ensure_my_table();
      $this->query->add_where($this->options['group'], "$this->table_alias.$this->real_field", NULL, 'IS NULL');
    $this->helper->add_filter();
  }
}

Selecting X number of terms in the filter returns the correct rows.

It correctly adds a clause : field_data_field_dipl_domaine.field_dipl_domaine_tid IN ('36635', '36642')

What's missing

I could use advice about the way to rewrite the query method of the handler to add the criteria on the other entity type. The WHERE clause would look like field_data_field_dipl_domaine.field_dipl_domaine_tid IN ('36635', '36642') OR field_data_field_dipl_domaine2.field_dipl_domaine_tid IN ('36635', '36642')
where field_data_field_dipl_domaine2 would be an alias toward the field instance on the second entity type.

Of course, the query would have to join correctly the second entity to the first, so I guess it would involve doing something more in views_data_alter (a relationship ?) but can't figure what as of now.

Any help would be welcome.

Countzero’s picture

Countzero CreditAttribution: Countzero commented
Category: support » bug
Priority: Minor » Normal

OK, I really think there is a bug here. I created a simplified version of the view, to make sure nothing interferes.

This query returns one row : 

SELECT node.title AS node_title, node.nid AS nid, node.language AS node_language, node_field_data_field_formation_certification.nid AS node_field_data_field_formation_certification_nid, node.created AS node_created, 'node' AS field_data_field_dipl_domaine_node_entity_type, 'node' AS field_data_field_formation_certification_node_entity_type
FROM 
{node} node
LEFT JOIN {field_data_field_formation_certification} field_data_field_formation_certification ON node.nid = field_data_field_formation_certification.entity_id AND (field_data_field_formation_certification.entity_type = 'node' AND field_data_field_formation_certification.deleted = '0')
LEFT JOIN {node} node_field_data_field_formation_certification ON field_data_field_formation_certification.field_formation_certification_nid = node_field_data_field_formation_certification.nid
INNER JOIN {field_data_field_dipl_domaine} field_data_field_dipl_domaine ON node.nid = field_data_field_dipl_domaine.entity_id AND (field_data_field_dipl_domaine.entity_type = 'node' AND field_data_field_dipl_domaine.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN  ('actions')) )AND( (field_data_field_dipl_domaine.field_dipl_domaine_tid = '36635') ))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0

This one too :

SELECT node.title AS node_title, node.nid AS nid, node.language AS node_language, node_field_data_field_formation_certification.nid AS node_field_data_field_formation_certification_nid, node.created AS node_created, 'node' AS field_data_field_dipl_domaine_node_entity_type, 'node' AS field_data_field_formation_certification_node_entity_type
FROM 
{node} node
LEFT JOIN {field_data_field_formation_certification} field_data_field_formation_certification ON node.nid = field_data_field_formation_certification.entity_id AND (field_data_field_formation_certification.entity_type = 'node' AND field_data_field_formation_certification.deleted = '0')
LEFT JOIN {node} node_field_data_field_formation_certification ON field_data_field_formation_certification.field_formation_certification_nid = node_field_data_field_formation_certification.nid
INNER JOIN {field_data_field_dipl_domaine} node_field_data_field_formation_certification__field_data_field_dipl_domaine ON node_field_data_field_formation_certification.nid = node_field_data_field_formation_certification__field_data_field_dipl_domaine.entity_id AND (node_field_data_field_formation_certification__field_data_field_dipl_domaine.entity_type = 'node' AND node_field_data_field_formation_certification__field_data_field_dipl_domaine.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN  ('actions')) )AND( (node_field_data_field_formation_certification__field_data_field_dipl_domaine.field_dipl_domaine_tid = '36679') ))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0

But this one doesn't return anything, where it should return the two previous selected rows :

SELECT node.title AS node_title, node.nid AS nid, node.language AS node_language, node_field_data_field_formation_certification.nid AS node_field_data_field_formation_certification_nid, node.created AS node_created, 'node' AS field_data_field_dipl_domaine_node_entity_type, 'node' AS field_data_field_formation_certification_node_entity_type
FROM 
{node} node
LEFT JOIN {field_data_field_formation_certification} field_data_field_formation_certification ON node.nid = field_data_field_formation_certification.entity_id AND (field_data_field_formation_certification.entity_type = 'node' AND field_data_field_formation_certification.deleted = '0')
LEFT JOIN {node} node_field_data_field_formation_certification ON field_data_field_formation_certification.field_formation_certification_nid = node_field_data_field_formation_certification.nid
INNER JOIN {field_data_field_dipl_domaine} field_data_field_dipl_domaine ON node.nid = field_data_field_dipl_domaine.entity_id AND (field_data_field_dipl_domaine.entity_type = 'node' AND field_data_field_dipl_domaine.deleted = '0')
INNER JOIN {field_data_field_dipl_domaine} node_field_data_field_formation_certification__field_data_field_dipl_domaine ON node_field_data_field_formation_certification.nid = node_field_data_field_formation_certification__field_data_field_dipl_domaine.entity_id AND (node_field_data_field_formation_certification__field_data_field_dipl_domaine.entity_type = 'node' AND node_field_data_field_formation_certification__field_data_field_dipl_domaine.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN  ('actions')) )AND( (field_data_field_dipl_domaine.field_dipl_domaine_tid = '36635') OR (node_field_data_field_formation_certification__field_data_field_dipl_domaine.field_dipl_domaine_tid = '36679') ))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0

The third query is simply the first two put together, and I can't see any reason why it doesn't work.

Changing the two last INNER JOIN to LEFT JOIN seems to make the query work.

Countzero’s picture

Countzero CreditAttribution: Countzero commented

Confirmed : doing a $view->query->table_queue[$alias]['join']->type = 'LEFT'; in the proper places makes the query work.

I'm not sure this qualifies as a bug, but it's definitely counter intuitive, so I think it deserves a bit of attention.

Countzero’s picture

Countzero CreditAttribution: Countzero commented
Title: Wrong aliases when same field used in related entity ? » INNER joins produces unexpected results

Changing the title.

amanire’s picture

amanire CreditAttribution: amanire commented

Thank you Countzero, for documenting this issue. I found that the left join solution also worked for the following case:

node type A
- or -
node type B -AND- taxonomy term C

Since an inner join on the taxonomy table is used, matching nodes of type A are excluded.

Honza Pobořil’s picture

Honza Pobořil CreditAttribution: Honza Pobořil for Czech Radio commented

Maybe duplication of #1766338: Incorrect filter group OR behavior, LEFT JOIN changed to INNER JOIN

tmin’s picture

tmin CreditAttribution: tmin at Lawspot commented
Status: Active » Needs review
FileSize
inner_join_produces_unexpected_results-1428158-7.patch1.07 KB

This issue is an actual bug that needs to be patched every time that the Views module is updated.

Moving the patch found here to this topic so that it can be swiftly reviewed and added in the next version of Views.

Credit goes to @john-pitcairn for the patch.

Pinging @jenlampton

John Pitcairn’s picture

John Pitcairn CreditAttribution: John Pitcairn commented
Status: Needs review » Needs work

My original patch in that thread was just to revert the many-to-one handler commit that caused all this. It's only useful for sites that made no use of the changed handler behavior.

Given how long ago that was, I'd consider it unsafe to blindly use this patch without understanding the possible consequences. Committing it will surely break a lot of sites.

@jenlampton did work on a more comprehensive patch later in the thread, from around #69 with subsequent updates. I'm not sure whether that included any mitigation for sites that have come to depend on the changed handler behavior (I have not been using that patch).

tmin’s picture

tmin CreditAttribution: tmin at Lawspot commented

@john-pitcairn :

@jenlampton did work on a more comprehensive patch later in the thread, from around #69 with subsequent updates.

Going through the original thread, the only replies by @jenlampton that I saw were the ones that are mentioning whether your patch (comment #94) applies cleanly to every new version of Views. This is the exact same patch with #69 by @jenlampton and the only thing that it does is change the join from INNER to LEFT (changing only the location of the change since subsequent versions of Views have moved code around).

My original patch in that thread was just to revert the many-to-one handler commit that caused all this. It's only useful for sites that made no use of the changed handler behavior.

So, basically, the story here is that we introduced a regression, nobody noticed it, people started working around it by using "hacks" in their Views' configuration and now we can never fix the regression since people depend on the workarounds.

By the way, I think we can all agree that we are talking about an actual bug since an OR does not behave as an OR.

sites that made no use of the changed handler behavior

Do we have an example of a case where the changed handler behavior would be used? If we find that this is a common case, we might as well keep updating this issue once every a new version of Views is released with the state of the patch against it.

However, after scanning through the comments in the core issue to see how people are working around it, the one thing that I noticed is that it always comes down to finding an indirect way of changing the INNER join to a LEFT join (which would not be affected by this patch anyways).

For example, the comment that seems to be mentioned the most as a workaround (#36) says:

For the issue with INNER JOIN instead of LEFT JOIN, without adding the patch in #7, you can select the Reduce duplicates checkbox from the first field in OR group filter and will transform your query to LEFT JOIN.
For more info check handlers.inc at line 890:
if ($this->handler->operator == 'or' && empty($this->handler->options['reduce_duplicates']))
which makes your query use INNER JOIN, so checking the reduce duplicates option takes you to line 960, using LEFT JOIN.

The original regression mentions "performance reasons" in the relevant comment and I can't think of a way to use the INNER join that the LEFT join wouldn't cover.

jenlampton’s picture

jenlampton
she / her
CreditAttribution: jenlampton at Jeneration Web Development for Backdrop CMS commented
Status: Needs work » Needs review
FileSize
views-fixes_broken_inner_join-1428158-10.patch930 bytes

I'd also like to see this fixed... So here's a slightly safer approach based on this patch which only changes it to LEFT if it's not using the grouped filters. 

// INNER joins are slightly faster, so use them when we know we can.
$group = isset($this->handler->options['group']) ? $this->handler->options['group'] : FALSE;
if (isset($join) && ($group === FALSE || $this->handler->query->where[$group]['type'] == 'AND')) {
  $join->type = 'INNER';
}
John Pitcairn’s picture

John Pitcairn CreditAttribution: John Pitcairn commented
Status: Needs review » Needs work

The patch at #94 in the original issue works for me. The patch at #10 in this issue does not.

jenlampton’s picture

jenlampton
she / her
CreditAttribution: jenlampton at Jeneration Web Development for Backdrop CMS commented
FileSize
views-fix_broken_or-1766338-12.patch1.46 KB

Interesting. @John Pitcairn does your Drupal 7 view use the taxonomy filter, or another one that allows you to select "Reduce duplicates"?

Here's a new a new patch based off your #94 in the original issue but also including the optimization, and the hard-coded exception for the `taxonomy_index_tid` plugin. I wonder if it will work any better?

John Pitcairn’s picture

John Pitcairn CreditAttribution: John Pitcairn commented

No, the view has no reduce duplicates options available. Thanks, will test in the next day or so.

laryn’s picture

laryn
he/him
CreditAttribution: laryn at Backdrop CMS commented
Status: Needs work » Needs review
FileSize
views-fix_broken_or-1766338-14.patch1.53 KB

Here's a revision to @jenlampton's patch that I just submitted in the Backdrop queue. It should help here as well. The only change from her patch, is checking to make sure that $plugin_id is set before using it.

skylord’s picture

skylord CreditAttribution: skylord commented

Works fine with 3.25, thanks!