0

Scenario

  1. One hierarchical vocabulary called Countries with 300 taxonomy terms, one for each country, indenting like:

    • America (level 1)
      • Anguila (level 2)
      • Antigua y barbuda (level 2)
      • ... (level 2)
    • Europe (level 1)
      • Austria (level 2)
      • Armenia (level 2)
      • ... (level 2)
  2. One content type called Office, then you add a field called Country, and its type is taxonomy reference, related to you recent created vocabulary called Countries.

  3. After that, you create a few nodes: the first is called 'My secret office', checking Afghanistan in its country field. The second node is called 'My beautiful office', checking Albania.

Now you think it should be interesting if you could have a page where you can see a list of all your offices. The finest way to achieve this is by creating a view. To do so, you create a view page with this configuration:

  • Filter criteria: Content: Content type (= Office)
  • Page settings: Path: /offices

Then you visit your new page /offices and you can see 2 offices: My secret office and My beautiful office. Everything works as expected.

Then you add a country filter in you page. Going back to views configuration:

  • Filter criteria: Content: Has taxonomy term (exposed, Show as hierarchical) related to Countries.

You return to visit your page, and you can see a filter listing 300 countries and your 2 offices. You are on fire, so you decide to test your brand new filter:

  • You check Afghanistan option, and click Search; then you only see My secret office. Great.
  • You check Albania option, and click Search; then you only see My beautiful office. Awesome.
  • You now check Afghanistan, Albania, Algeria, Andorra, Angola and so on until you have checked all your 300 countries. The correct output should be your 2 offices, since you have no more; so you click search and BAAAM: White Screen of Death. But you are smart, and you have allowed PHP yo show the error log. You see that MySQL had a failure:

SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join

It follows the entire query Drupal is trying to execute. You notice everything is fine EXCEPT for this:

LEFT JOIN {taxonomy_index} taxonomy_index_value_0 ON node_field_data.nid = taxonomy_index_value_0.nid AND taxonomy_index_value_0.tid = :views_join_condition_1

LEFT JOIN {taxonomy_index} taxonomy_index_value_1 ON node_field_data.nid = taxonomy_index_value_0.nid AND taxonomy_index_value_1.tid = :views_join_condition_2

LEFT JOIN {taxonomy_index} taxonomy_index_value_2 ON node_field_data.nid = taxonomy_index_value_0.nid AND taxonomy_index_value_2.tid = :views_join_condition_3

LEFT JOIN {taxonomy_index} taxonomy_index_value_3 ON node_field_data.nid = taxonomy_index_value_0.nid AND taxonomy_index_value_4.tid = :views_join_condition_4

enter image description here

and so on. Drupal is using the left join technique to access the database. The problem is that Drupal is using one left join for each taxonomy termed checked, meaning the first one is related to Afghanistan, the second one to Albania, the third is for Algeria, and so on.

¿How can I make a search using more than 61 options?

I think it is not a crazy thing that an user could search for offices in America and Europe, resulting this in more than 100 countries being checked. While looking for a solution, I have wrote a litle Javascript code that prevents the user from marking more than 50 countries so he doesn't face the WSoD.

Setup:

  • Drupal 8.8.1 (core and all its modules updated)
  • I'm not using any contributed module related to taxonomy or views
  • PHP 7.1
  • MySQL 5.5
  • I am reopening this when I get off mobile. Edit makes it a good question. I am really baffled why that isn’t an IN query, which is how I thought that filter plugin worked. – mpdonadio Jan 23 at 22:36
  • Could you provide a capture of your view design (like other filters, arguments, etc)? Because perhaps this is related to other components/modules that may be affecting your view query. – d70rr3s Jan 24 at 9:51
3

UPDATE

As discussed here, this issue was caused by some missconfiguration at view level and normally should not occur. Read the full post to get the full picture on how you can step on this error.

ORIGINAL ANSWER

I'm adding my findings as answer since comments have limitations both on styles and length, but by all means THIS IS NOT an actual answer to the question.

I've tried to reproduce the describe behavior using an exisitng Drupal site that I'm working on:

  1. I've a content A with a er field targeting vocabulary B
  2. I've generated over 300 terms with hierarchy
  3. Added 2 nodes tagged with 2 of the generated terms
  4. Created a view as described in the question, but with a slight change: I've one display using "Has term" filter an another display "Has term (with depth)" filter; I explain below why I did this.

The reason using two displays with these two filters is because I saw that "Has taxonomy term" filter (plugin id taxonomy_index_tid) is implemented by \Drupal\taxonomy\Plugin\views\filter\TaxonomyIndexTid which extends from \Drupal\views\Plugin\views\filter\ManyToOne. Reviewing the code I saw that neither the ManyToOne base filter or the TaxonomyIndexTid implement JOIN queries. On the other hand \Drupal\taxonomy\Plugin\views\filter\TaxonomyIndexTidDepth extends from TaxonomyIndexTid and has this on it's ::query() method

public function query() {
  ...
  // Now build the subqueries.
  $subquery = Database::getConnection()->select('taxonomy_index', 'tn');
  $subquery->addField('tn', 'nid');
  $where = (new Condition('OR'))->condition('tn.tid', $this->value, $operator);
  $last = "tn";

  if ($this->options['depth'] > 0) {
    $subquery->leftJoin('taxonomy_term__parent', 'th', "th.entity_id = tn.tid");
    $last = "th";
    foreach (range(1, abs($this->options['depth'])) as $count) {
      $subquery->leftJoin('taxonomy_term__parent', "th$count", "$last.parent_target_id = th$count.entity_id");
      $where->condition("th$count.entity_id", $this->value, $operator);
      $last = "th$count";
    }
  }
  elseif ($this->options['depth'] < 0) {
    foreach (range(1, abs($this->options['depth'])) as $count) {
      $field = $count == 1 ? 'tid' : 'entity_id';
      $subquery->leftJoin('taxonomy_term__parent', "th$count", "$last.$field = th$count.parent_target_id");
      $where->condition("th$count.entity_id", $this->value, $operator);
      $last = "th$count";
    }
  }

  $subquery->condition($where);
  $this->query->addWhere($this->options['group'], "$this->tableAlias.$this->realField", $subquery, 'IN');
}

As shown it does LEFT JOINS to work with the selected depth and hierachy. All though after selecting all 300 terms in both displays I was unable to reproduce the described error (see screenshots below).

Has taxonmy term

Has taxonomy term exposed filter query output +300 terms

Has taxonomy term (with depth)

Has taxonomy term (with depth exposed filter query output +300 terms

Also as commented @mpdonadio the filter is using WHERE IN to filter the terms and delegates to LEFT JOIN when dealing with hierarchy.

Maybe these can shed some light on your error.

| improve this answer | |
  • 2
    You setup is correct, so I tried to figure out the differences between your configuration and mine. And I've found my mistake, since my filter criteria is 'Has taxonomy tem' insteand of 'Has taxonomy term with depth'. I've missed that because at the time I built the view's configuration that taxonomy was not hierarchical and it changed later in time. I'm marking your answer as the good one but I would recomment to edit it to clarify it was a wrong view configuration (just in case someone with a failure in its brain like me ends in this question). Many thanks and love to all of you. – devcatalarubio Jan 24 at 9:52
  • Anyway it's a litle weird that Drupal allows you to check 'Show as hierarchical' in the 'Has taxonomy term' filter criteria because it can lead you a situation like this. IMHO, that feature should be only be visible in the 'Has taxonomy term with depth'. I'll try to raise a bug in the Views core module Drupal's offical page. – devcatalarubio Jan 24 at 12:19
  • I tested using "Display hierarchy" with "Has taxonomy term" and works (is the first screenshot). I've tested this with 8.7.11, perhaps is a problem with 8.8 branch? The view was an old that you edited recently? Have you update core recently? – d70rr3s Jan 24 at 12:28
  • That Drupal site started developing on August 2019, on 8.7 version, so I think it is not that old. The core was updated 1 week ago and I can't tell you if the bug existed before that. – devcatalarubio Jan 26 at 20:59
  • I'm starring this question since it's a weird behavior and I have a lot of sites using taxonomy filtered views so maybe me or my collagues can step on this anytime soon. It will be great if we can narrow it down to the root source and get it reported as an issue and maybe get it fixed :). Thanks for the information provided, your patience and your time. – d70rr3s Jan 26 at 21:07

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.