7

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?

17

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

depicting reduce duplicates tickbox

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.

  • 1
    This is helpful when you are adding filters like boolean fields, taxonomy terms, etc. Thanks! – geerlingguy Aug 6 '14 at 14:45
  • The "Reduce duplicates" option will not consider those nodes if they are not present in the joining field's table. So the hook_views_query_alter() solution seems more promising in this case. – Pratip Ghosh Nov 16 '17 at 6:58
10

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";
  }
}
  • Why do you want to use a views_query_alter when it can be done using the existing GUI? drupal.stackexchange.com/questions/52449/… – Gokul N K Feb 4 '15 at 13:09
  • this code made this error for me: Fatal error: Call to undefined method stdClass::build_join() in C:\xampp\htdocs\sarpoosh\sites\all\modules\views\plugins\views_plugin_query_default.inc on line 1313 – shekoufeh Jan 28 '16 at 8:45
  • 2
    @GokulNK Because not all views use Relationships (OP's view doesn't), and this UI option isn't available for joins that aren't Relationship-based. – CoreDumpError Sep 19 '16 at 21:21
4

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

enter image description here

  1. If you select this checkbox it means : Make an inner join
  2. If you uncheck this checkbox it means : Make a left join
  • I've just run into an issue like this. I thought the problem was the "Require this relationship", but altering it didn't fixed the issue. Checking the "reduce duplicates" fixed the issue for me. – ndvo Nov 1 '17 at 11:24
0

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.

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.