I'm upgrading a site from Drupal 6 to Drupal 7, and a I'm having trouble reproducing my view.

It appears that in Drupal 7 I cannot use the same field for both a filter (VALUE IS NOT ONE OF X) and a contextual filter (VALUE IS ONE OF Y) at the same time, due to some strange LEFT JOIN magic that is happening in the resulting query.

Here's the Query from Drupal 6 that works as intended:

SELECT DISTINCT(node.nid) AS nid_1,
node.nid AS nid,
node.vid AS node_vid,
node.title AS node_title
FROM node node 
LEFT JOIN content_field_school_location node_data_field_school_location 
  ON node.vid = node_data_field_school_location.vid
WHERE (node.status = 1) 
  AND (node.type in ('school')) 
  AND (UPPER(node_data_field_school_location.field_school_location_value) != UPPER('ONLINE')) 
  AND (node_data_field_school_location.field_school_location_value = 'CA')
ORDER BY node_title ASC

Here's the Query from Drupal 7 that returns no results:

SELECT node.title AS node_title, 
  node.nid AS nid
FROM  {node} node
LEFT JOIN {field_data_field_text} field_data_field_text 
  ON node.nid = field_data_field_text.entity_id 
  AND field_data_field_text.field_text_value = 'ONLINE'
WHERE (( (field_data_field_text.field_text_value = 'CA') )
  AND(( (node.status = '1') 
  AND (field_data_field_text.field_text_value IS NULL ) )))

I think the problem is the AND in the LEFT JOIN, it looks like that would return only records where the value is the opposite of what I want. I'm not sure what the following WHERE IS NULL on that same column will do, or why those two things in combination are expected to get the same results.

Here's a Query (for Drupal 7) that does get the intended results:

SELECT node.title AS node_title, 
    node.nid AS nid
  FROM  node node
  LEFT JOIN field_data_field_text field_data_field_text 
    ON node.nid = field_data_field_text.entity_id 
    AND field_data_field_text.field_text_value <> 'ONLINE'
  WHERE (( (field_data_field_text.field_text_value = 'CA') )
    AND(( (node.status = '1') )))

...But I'm not sure how to change views to get this happening instead...

Steps to reproduce:

1) Add a text field to a content type
1a) Set the number of values to unlimited, and the Allowed values to something like

ONLINE|Online
AL|Alabama
AK|Alaska
AZ|Arizona
AR|Arkansas
CA|California
CO|Colorado
CT|Connecticut

2) Create three pieces of content:
2a) one: check both CA and Online
2b) two: check CO
2c) three: check CA

3) Create a view of node titles.
3a) Add a filter for the text field, set the operator to "Is none of" and select the value "Online"
3b) save the view.

4) preview the view results at the bottom of the page.
4a) confirm that the two nodes without Online are showing

5) Update the view
5a) Add a contextual filter for the text field, set to "Display all results for the specified field " when no value is present.

6) preview the view results at the bottom of the page.
6a) confirm that the two nodes without Online are showing
6b) enter "CA" as a contextual filter
Note that no results are returned instead of the one record with CA
6b) enter "CO" as a contextual filter
Note that no results are returned instead of the one record with CO

Comments

jenlampton created an issue. See original summary.

jenlampton’s picture

Issue summary: View changes
jenlampton’s picture

Title: View with same field used for filter and contextual filter returns no results » View with same field used for IS NONE OF filter and contextual filter returns no results

While testing, I noticed this problem does not occur when both filters are using the IS ONE OF operator, I think the problem is only with the IS NONE OF operator.

jippie1948’s picture

First some history:
- some years ago I imported Drupal 6 views into Drupal 7. Everything worked fine.
- recently I needed to change a display in a view. The master kept working, but the display failed.
- two days ago I reconstructed this working view master into a new view.

Results in short: as long as I didn't save the view, the query worked. Saving it killed off its working!
Saving a view seems to create the problem where the same field (here Date) is used in the filter.

Here are the Queries:
=== WORKS AFTER SAVING
SELECT node.nid AS nid, node.title AS node_title, node.language AS node_language, field_data_field_event_dates.field_event_dates_value AS field_data_field_event_dates_field_event_dates_value, 'node' AS field_data_field_event_dates_node_entity_type, 'node' AS field_data_body_node_entity_type
FROM
{node} node
LEFT JOIN {field_data_field_event_dates} field_data_field_event_dates ON node.nid = field_data_field_event_dates.entity_id AND (field_data_field_event_dates.entity_type = 'node' AND field_data_field_event_dates.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN ('event')) ))
ORDER BY field_data_field_event_dates_field_event_dates_value ASC
LIMIT 16 OFFSET 0

=== WORKS AFTER SAVING with another filter: language select that is not in the fields.
SELECT node.nid AS nid, node.title AS node_title, node.language AS node_language, field_data_field_event_dates.field_event_dates_value AS field_data_field_event_dates_field_event_dates_value, 'node' AS field_data_field_event_dates_node_entity_type, 'node' AS field_data_body_node_entity_type
FROM
{node} node
LEFT JOIN {field_data_field_event_dates} field_data_field_event_dates ON node.nid = field_data_field_event_dates.entity_id AND (field_data_field_event_dates.entity_type = 'node' AND field_data_field_event_dates.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN ('event')) AND (node.tnid = 0
OR (node.language = 'en')
OR (node.language = 'en' AND
0 = (SELECT count(lmfh_node.nid)
FROM {node} AS lmfh_node
WHERE lmfh_node.tnid = node.tnid AND
((lmfh_node.language = 'en'))))
OR (node.nid = node.tnid AND
0 = (SELECT count(lmfh_node.nid)
FROM {node} AS lmfh_node
WHERE lmfh_node.tnid = node.tnid AND
((lmfh_node.language = 'en') OR (lmfh_node.language = 'en'))))) ))
ORDER BY field_data_field_event_dates_field_event_dates_value ASC
LIMIT 16 OFFSET 0

=== THIS Date (both in fields and filters) QUERY WORKS IN THE PREVIEW BEFORE SAVING, BUT NOT AFTER SAVING
SELECT node.nid AS nid, node.title AS node_title, node.language AS node_language, field_data_field_event_dates.field_event_dates_value AS field_data_field_event_dates_field_event_dates_value, 'node' AS field_data_field_event_dates_node_entity_type, 'node' AS field_data_body_node_entity_type
FROM
{node} node
LEFT JOIN {field_data_field_event_dates} field_data_field_event_dates ON node.nid = field_data_field_event_dates.entity_id AND (field_data_field_event_dates.entity_type = 'node' AND field_data_field_event_dates.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN ('event')) AND (node.tnid = 0
OR (node.language = 'en')
OR (node.language = 'en' AND
0 = (SELECT count(lmfh_node.nid)
FROM {node} AS lmfh_node
WHERE lmfh_node.tnid = node.tnid AND
((lmfh_node.language = 'en'))))
OR (node.nid = node.tnid AND
0 = (SELECT count(lmfh_node.nid)
FROM {node} AS lmfh_node
WHERE lmfh_node.tnid = node.tnid AND
((lmfh_node.language = 'en') OR (lmfh_node.language = 'en'))))) ))
ORDER BY field_data_field_event_dates_field_event_dates_value ASC
LIMIT 16 OFFSET 0

Hence I think the original of the issue is appropriate :)

I am very much looking forward to a solution.

Best wishes, JiP

jippie1948’s picture

Priority: Normal » Critical
MustangGB’s picture

jippie1948’s picture

Eventually I found the cause of the problem.

It was a corrupted (infected?) picture URL in two of the nodes. As long as these two nodes would not be part of the 'preview' sql, the views display work. There was also a different symtom: the cron failed to run, because the cron would trigger the search module to index nodes. This would would fail because of these two corrupted nodes blocked this indexing, resulting in a failed cron run.

This means that my issue has actually nothing to do with the issue raised by jenlampton.

Best wishes, jippie

jippie1948’s picture

Priority: Critical » Normal

That is why I moved the issue's priority back to 'normal'. :)