Changing an inner join to a left join in views 3.3?

Question

Using views 3.3, I have a view in Drupal 7 where I am showing two content types. I have a filter criteria based on the value of a field that is attached only to one of the content types. So long as I have that field criteria, views is doing an INNER JOIN, which causes it not to return views of the first content type which doesn't have that field attached.

I copied the query from the view, changed the INNER to a LEFT join, pasted it into the mysql shell, and I got all the rows that I expected to get. But I don't know how to make those changes in the view editor.

I was playing around in the 'relationship' block on the view editor, but I didn't see where I could change the join type.

Am I able to change how views 3.3 does joins?

Answer 1

I found clicking on the reduce duplicates checkbox converted my INNER JOINs to LEFT JOINs:

Before I was getting something akin to the following injected into my query:

INNER JOIN {field_data_field_random_field field_data_field_random_field} 
        ON node.nid = field_data_field_random_field.entity_id 
       AND (field_data_field_random_field.entity_type = 'node' 
       AND field_data_field_random_field.deleted = '0')

These inner joins were being caused by simple taxonomy filters, so sadly I didn't have any relationships to unrequire. Checking this box however doesn't seem to have altered my query in any other way, save for removing the inner joins, which is perfect for me; and may help someone else out there in this vast monkey adventure called life.

Answer 2

I used hook_views_query_alter() to change it. It takes a reference to the $view and $query objects. I checked the name of the view to see if it was the one I wanted, and then changed the join type in the query object for the table of interest:

function my_module_views_query_alter(&$view, &$query) {
  if($view->name == 'my_view') {
    $query->table_queue['field_data_field_my_field']['join']->type = "LEFT";
  }
}

Answer 3

When you go to Relationships tab in the views you will find a check box titled "Require this relationship".

1. If you select this checkbox it means : Make an inner join
2. If you uncheck this checkbox it means : Make a left join

Answer 4

Just adding some information for completeness for those using AND/OR groups in their view.

I have a Filter Criteria which if I switch the last two conditions it changes it between an inner join and a left join.

To explain my current Filter Criteria is basically this:

(Published = "Yes" AND Content Type = "Blog) AND ((Blog Type = "X") OR (Blog Type IS NULL))

Which uses an INNER JOIN and doesn't give me the results I want. If I just swap the last two conditions in the OR it changes the JOIN type:

(Published = "Yes" AND Content Type = "Blog) AND ((Blog Type IS NULL) OR (Blog Type = "X"))

This uses a LEFT JOIN. Go figure, but could be useful for someone else.

Answer 5

Depending on your view filters, you might need the patch at https://www.drupal.org/node/1766338