Problem/Motivation
There are situations in which you want to query whether an entity has one of a pair of properties (for example it's a turqoise-triangle or a red-rectangle). This can be achieved by creating an outer OR group and then adding a condition of an AND group for the color and shape.
When this is done, even though the fields are the same and doing this by hand would just result in a massive WHERE clause, Drupal instead adds a LEFT JOIN for every condition added to the OR (i.e. every 2 fields in an AND). In cases where there's a large set of data that you might want to query from the database this runs into database limits.
I've removed some duplication from the below error message to help you scroll less. I hope the pattern is still clear.
Drupal\Core\Database\DatabaseExceptionWrapper : SQLSTATE[HY000]: General error: 1 too many FROM clause terms, max: 200: SELECT "base_table"."vid" AS "vid", "base_table"."nid" AS "nid"
FROM
"test86723002"."node" "base_table"
LEFT JOIN "test86723002"."node_field_data" "node_field_data" ON "node_field_data"."nid" = "base_table"."nid"
LEFT JOIN "test86723002"."node_field_data" "node_field_data_2" ON "node_field_data_2"."nid" = "base_table"."nid"
LEFT JOIN "test86723002"."node_field_data" "node_field_data_3" ON "node_field_data_3"."nid" = "base_table"."nid"
LEFT JOIN "test86723002"."node_field_data" "node_field_data_4" ON "node_field_data_4"."nid" = "base_table"."nid"
LEFT JOIN "test86723002"."node_field_data" "node_field_data_5" ON "node_field_data_5"."nid" = "base_table"."nid"
LEFT JOIN "test86723002"."node_field_data" "node_field_data_6" ON "node_field_data_6"."nid" = "base_table"."nid"
LEFT JOIN "test86723002"."node_field_data" "node_field_data_7" ON "node_field_data_7"."nid" = "base_table"."nid"
LEFT JOIN "test86723002"."node_field_data" "node_field_data_8" ON "node_field_data_8"."nid" = "base_table"."nid"
LEFT JOIN "test86723002"."node_field_data" "node_field_data_9" ON "node_field_data_9"."nid" = "base_table"."nid"
LEFT JOIN "test86723002"."node_field_data" "node_field_data_10" ON "node_field_data_10"."nid" = "base_table"."nid"
......
LEFT JOIN "test86723002"."node_field_data" "node_field_data_n" ON "node_field_data_n"."nid" = "base_table"."nid"
.....
LEFT JOIN "test86723002"."node_field_data" "node_field_data_200" ON "node_field_data_200"."nid" = "base_table"."nid"
WHERE (("node_field_data"."nid" = :db_condition_placeholder_0) and ("node_field_data"."title" LIKE :db_condition_placeholder_1 ESCAPE '\')) or (("node_field_data_2"."nid" = :db_condition_placeholder_2) and ("node_field_data_2"."title" LIKE :db_condition_placeholder_3 ESCAPE '\')) or (("node_field_data_3"."nid" = :db_condition_placeholder_4) and ("node_field_data_3"."title" LIKE :db_condition_placeholder_5 ESCAPE '\')) or (("node_field_data_4"."nid" = :db_condition_placeholder_6) and ("node_field_data_4"."title" LIKE :db_condition_placeholder_7 ESCAPE '\')) or (("node_field_data_5"."nid" = :db_condition_placeholder_8) and ("node_field_data_5"."title" LIKE :db_condition_placeholder_9 ESCAPE '\')) or (("node_field_data_6"."nid" = :db_condition_placeholder_10) and ("node_field_data_6"."title" LIKE :db_condition_placeholder_11 ESCAPE '\')) or (("node_field_data_7"."nid" = :db_condition_placeholder_12) and ("node_field_data_7"."title" LIKE :db_condition_placeholder_13 ESCAPE '\')) or (("node_field_data_8"."nid" = :db_condition_placeholder_14) and ("node_field_data_8"."title" LIKE :db_condition_placeholder_15 ESCAPE '\')) or (("node_field_data_9"."nid" = :db_condition_placeholder_16) and ("node_field_data_9"."title" LIKE :db_condition_placeholder_17 ESCAPE '\')) or (("node_field_data_10"."nid" = :db_condition_placeholder_18) and ("node_field_data_10"."title" LIKE :db_condition_placeholder_19 ESCAPE '\')) or
...... (("node_field_data_n"."nid" = :db_condition_placeholder_m) ......... or (("node_field_data_200"."nid" = :db_condition_placeholder_398) and ("node_field_data_200"."title" LIKE :db_condition_placeholder_399 ESCAPE '\')); Array
(
[:db_condition_placeholder_0] => 0
[:db_condition_placeholder_1] => 0
[:db_condition_placeholder_2] => 1
[:db_condition_placeholder_3] => 1
[:db_condition_placeholder_4] => 2
[:db_condition_placeholder_5] => 2
[:db_condition_placeholder_6] => 3
[:db_condition_placeholder_7] => 3
[:db_condition_placeholder_8] => 4
[:db_condition_placeholder_9] => 4
[:db_condition_placeholder_10] => 5
[:db_condition_placeholder_11] => 5
[:db_condition_placeholder_12] => 6
[:db_condition_placeholder_13] => 6
[:db_condition_placeholder_14] => 7
[:db_condition_placeholder_15] => 7
[:db_condition_placeholder_16] => 8
[:db_condition_placeholder_17] => 8
[:db_condition_placeholder_18] => 9
[:db_condition_placeholder_19] => 9
.......
[:db_condition_placeholder_m] => n
[:db_condition_placeholder_m+1] => n
)
/app/html/core/lib/Drupal/Core/Database/ExceptionHandler.php:66
/app/html/core/lib/Drupal/Core/Database/Connection.php:822
/app/html/core/lib/Drupal/Core/Database/Query/Select.php:525
/app/html/core/lib/Drupal/Core/Entity/Query/Sql/Query.php:271
/app/html/core/lib/Drupal/Core/Entity/Query/Sql/Query.php:84
/app/html/core/modules/node/tests/Kernel/NodeQueryTest.php:50
/app/vendor/phpunit/phpunit/src/Framework/TestResult.php:728
Steps to reproduce
The following test can will generate the above error.
declare(strict_types=1);
namespace Drupal\Tests\node\Kernel;
use Drupal\KernelTests\KernelTestBase;
class NodeNestedConditionTest extends KernelTestBase {
/**
* {@inheritdoc}
*/
protected static $modules = [
'text',
'user',
'node',
];
/**
* {@inheritdoc}
*/
protected function setUp(): void {
parent::setUp();
$this->installEntitySchema('user');
$this->installEntitySchema('node');
}
/**
* Test that nested conditions on the entity don't cause too many joins.
*/
public function testNestedCondition() : void {
$storage = $this->container
->get('entity_type.manager')
->getStorage('node');
$query = $storage->getQuery()->accessCheck(FALSE);
$or = $query->orConditionGroup();
for ($i = 0; $i < 200; $i++) {
$or->condition(
$query->andConditionGroup()
->condition('nid', $i)
->condition('title', $i)
);
}
$query->condition($or);
$query->execute();
}
}
Proposed resolution
Remaining tasks
User interface changes
API changes
Data model changes
Release notes snippet