This is one of the more bizarre bugs that I've seen with Views- I'll describe it as best I can and would really appreciate if anyone could offer help.

Basically, I have two content types (ct_plus_project and ct_plus_task) that both have a selectable text field (field_ct_plus_status). One of the content types (ct_plus_task) can reference the other (ct_plus_project) via a nodererence field (field_ct_plus_project).

I want to view all tasks whose status is 'Open' and whose referenced project's status is also 'Open'. Sounds simple enough, right? Create a relationship for field_ct_plus_project, then add two filters on field_ct_plus_status, and assign one the relationship.

Here's the bizarre part: if you select 'Open' for both status filters, no tasks are returned (even though most tasks and their parent projects are Open)! However, if you select multiple values for the task status filter, you do get all of the appropriate tasks returned.

Here's the query in the case where you just select Open for both filters:

SELECT node.nid AS nid,
   node.title AS node_title
 FROM node node 
 LEFT JOIN content_type_ct_plus_task node_data_field_ct_plus_project ON node.vid = node_data_field_ct_plus_project.vid
 INNER JOIN node node_node_data_field_ct_plus_project ON node_data_field_ct_plus_project.field_ct_plus_project_nid = node_node_data_field_ct_plus_project.nid
 INNER JOIN content_field_ct_plus_status node_data_field_ct_plus_status ON node.vid = node_data_field_ct_plus_status.vid
 LEFT JOIN content_field_ct_plus_status node_node_data_field_ct_plus_project_node_data_field_ct_plus_status ON node_node_data_field_ct_plus_project.vid = node_node_data_field_ct_plus_project_node_data_field_ct_plus_status.vid AND node_node_data_field_ct_plus_project_node_data_field_ct_plus_status.field_ct_plus_status_value != 'Open'
 WHERE (node.type in ('ct_plus_task')) AND (node_data_field_ct_plus_status.field_ct_plus_status_value = 'Open') AND (node_node_data_field_ct_plus_project_node_data_field_ct_plus_status.field_ct_plus_status_value = 'Open')

I suspect that field_ct_plus_status_value != 'Open' on the left join is what's preventing any results.

If I select both 'Open' and 'Resolved', I get the following query, which seems to mostly work:

SELECT node.nid AS nid,
   node.title AS node_title
 FROM node node 
 LEFT JOIN content_type_ct_plus_task node_data_field_ct_plus_project ON node.vid = node_data_field_ct_plus_project.vid
 INNER JOIN node node_node_data_field_ct_plus_project ON node_data_field_ct_plus_project.field_ct_plus_project_nid = node_node_data_field_ct_plus_project.nid
 INNER JOIN content_field_ct_plus_status node_data_field_ct_plus_status ON node.vid = node_data_field_ct_plus_status.vid
 LEFT JOIN content_field_ct_plus_status node_node_data_field_ct_plus_project_node_data_field_ct_plus_status ON node_node_data_field_ct_plus_project.vid = node_node_data_field_ct_plus_project_node_data_field_ct_plus_status.vid AND node_node_data_field_ct_plus_project_node_data_field_ct_plus_status.field_ct_plus_status_value != 'Resolved'
 WHERE (node.type in ('ct_plus_task')) AND (node_data_field_ct_plus_status.field_ct_plus_status_value IN ('Open', 'Resolved')) AND (node_node_data_field_ct_plus_project_node_data_field_ct_plus_status.field_ct_plus_status_value = 'Open')

Notice that now the Left join condition is on field_ct_plus_status_value != 'Resolved'

If you would like to see the offending View or reproduce this yourself, download this feature and check out the ct_plus_tasks view. But the example query here is simpler and still produces the bug.

Comments

iamjon’s picture

Dane Powell,
Did you ever find a solution to your issue? If yes please post it so others may benefit.
I tried your link but it didn't work. Please upload the feature directly or provide replication instructions you can consult the submission guidelines for what kind of information to include. http://drupal.org/node/571990

Best regards

iamjon’s picture

Status: Active » Closed (won't fix)

Closed from a lack of activity.

danepowell’s picture

Version: 6.x-2.11 » 6.x-2.12
Status: Closed (won't fix) » Active

Wow, I'm terribly sorry- I don't know how I let this issue slip by. No, I have not found any resolution. I currently tell users to always be sure to select at least two statuses- a horrible "solution" if you can call it that.

You can see this for yourself if you install http://drupal.org/project/ct_plus .

merlinofchaos’s picture

Can you please attach a view export?

The problem is occurring because of this, as you point out:

LEFT JOIN content_field_ct_plus_status node_node_data_field_ct_plus_project_node_data_field_ct_plus_status ON node_node_data_field_ct_plus_project.vid = node_node_data_field_ct_plus_project_node_data_field_ct_plus_status.vid AND node_node_data_field_ct_plus_project_node_data_field_ct_plus_status.field_ct_plus_status_value != 'Open'

Now, the reason that is likely happening is because it thinks that the two fields are the same. My initial guess is that you have checked the "Allow multiple arguments to work together." setting. In this case, that setting would be wrong since if they're operating on different relationships, they're not really the same field, but the code that handles that checkbox will think they are.

danepowell’s picture

Here is the export of the view and relevant display. Note that it's only a partial export, as there's something like 7 different displays, which I don't figure need to all be posted here...

I couldn't find the "Allow multiple arguments to work together" setting that you mentioned. Note that this view by default (as well as the display I've included here) does not take arguments.

EDIT: There's something wrong with the code filter, so I will attach the View export in a follow-up comment

merlinofchaos’s picture

I think your attach failed.

danepowell’s picture

StatusFileSize
new79.08 KB
new15.29 KB

Yeah, sorry about that.
The first attached file (...badview.txt) contains only the relevant View snipped from the full export (...default.inc_.txt)

Thanks for your time and help.

jmaties’s picture

I know it's not the best solution but it works.

/**
 * Implementation of hook_views_default_views_alter().
 */
function MODULENAME_views_query_alter(&$view, &$query) {
//print 'Vista '.$view->name;
    if ($view->name == 'ct_plus_tasks') {
        $valor = $query->table_queue['node_node_data_field_ct_plus_project_node_data_field_ct_plus_status']['join']->extra[0]['value'];
        $operador = $query->table_queue['node_node_data_field_ct_plus_project_node_data_field_ct_plus_status']['join']->extra[0]['operator'];
        if ($operador=='!=' && $valor =='Open'){
            $query->table_queue['node_node_data_field_ct_plus_project_node_data_field_ct_plus_status']['join']->extra[0]['operator']='=';
        }
    }
}
mustanggb’s picture

Status: Active » Closed (won't fix)