I have a large array of names and surnames, I need to know which of these differ from the current ones in the database.
For example:
$largeList = largeList();
$storage = $this->entityTypeManager->getStorage("mi_entity");
$query = $storage->getQuery();
foreach ($largeList as $item) {
$andGroup = $query->andConditionGroup();
$andGroup
->condition('name', $item->getName(), "<>")
->condition('surname', $item->getSurname(), "<>");
$query->condition($andGroup);
}
$ids = $query->execute();
The idea is to compare on the first and last names, for example the part of the sql condition should look something similar to this:
SELECT
"base_table"."id" AS "id",
"base_table"."id" AS "base_table_id"
FROM
"entity_table" "base_table"
INNER JOIN
"entity_table" "entity_table"
ON "entity_table"."id" = "base_table"."id"
WHERE
(("entity_table"."name" NOT LIKE 'NAME 1' ESCAPE '\\') and ("entity_table"."surname" NOT LIKE 'LAST NAME 1' ESCAPE '\\')) AND
(("entity_table"."name" NOT LIKE 'NAME 2' ESCAPE '\\') and ("entity_table"."surname" NOT LIKE 'LAST NAME 2' ESCAPE '\\')) AND ......
However, the code above as an example, generates an additional join for each item in the array like this:
SELECT
"base_table"."id" AS "id",
"base_table"."id" AS "base_table_id"
FROM
"entity_table" "base_table"
INNER JOIN
"entity_table" "entity_table"
ON "entity_table"."id" = "base_table"."id"
INNER JOIN
"entity_table" "entity_table_2"
ON "entity_table_2"."id" = "base_table"."id"
INNER JOIN
"entity_table" "entity_table_3"
ON "entity_table_3"."id" = "base_table"."id"
INNER JOIN
"entity_table" "entity_table_4"
ON "entity_table_4"."id" = "base_table"."id"
WHERE
(("entity_table"."name" NOT LIKE 'NAME 1' ESCAPE '\\') and ("entity_table"."surname" NOT LIKE 'LAST NAME 1' ESCAPE '\\')) AND
(("entity_table_2"."name" NOT LIKE 'NAME 2' ESCAPE '\\') and ("entity_table_2"."surname" NOT LIKE 'LAST NAME 2' ESCAPE '\\')) AND
(("entity_table_3"."name" NOT LIKE 'NAME 3' ESCAPE '\\') and ("entity_table_3"."surname" NOT LIKE 'LAST NAME 3' ESCAPE '\\')) AND
(("entity_table_4"."name" NOT LIKE 'NAME 4' ESCAPE '\\') and ("entity_table_4"."surname" NOT LIKE 'LAST NAME 4' ESCAPE '\\'))
In this excerpt from the query, I only show 4 conditions that generate 4 joins, but if I have 500, it generates 500 joins.
What would be the correct way to write the condition to avoid these unnecessary joins ?