I faced with problem when try to load entities using loadByProperties method. If I set two and more properties I don't get any results.
So, I create a new content type test_content_type with two fields: field_first - stirng, limited (1) and field_second - string, unlimited.
I create nodes of this content type
and I try to load that content with the next command
$res = Drupal::entityTypeManager()
->getStorage('node')
->loadByProperties([
'field_first' => '12345',
'field_second' => 'test_value_1'
]);
I have nothing in result. I investigate the problem and found next issue in the method translateCondition of the Drupal\Core\Entity\Query\Sql\pgsql\Condition class. In the project we use PostgradeSQL database.
In line #21 there is the base code, in line #22 my fix. As we can see in case when $condition['value'] is array placeholders starts from $n = 1. And this method builds placeholders :value1, :value2 and etc for each parameter in one property.
But if we set two properties, we will have next situation. The compile() method of the class Drupal\Core\Entity\Query\Sql\Condition runs translateCondition foreach element (searching property) in array that passed to loadByProperties. In my case for field_first and field_second.
So, as result we have placeholder :value1 as for field_first and as for field_second. I debug SQL query and there is next:
SELECT base_table.vid AS vid, base_table.nid AS nid
FROM node base_table
INNER JOIN node__field_first node__field_first ON node__field_first.entity_id = base_table.nid
INNER JOIN node__field_second node__field_second ON node__field_second.entity_id = base_table.nid
INNER JOIN node_field_data node_field_data ON node_field_data.nid = base_table.nid
WHERE ((LOWER(node__field_first.field_first_value) IN (LOWER('12345')))) AND ((LOWER(node__field_second.field_second_value) IN (LOWER('12345')))) AND (node_field_data.default_langcode IN ('1'))
As we can see for both fields node__field_first.field_first_value and node__field_second.field_second_value query use one searching value 12345. It because in line #21 of translateCondition method each condition starts from number 1. It clearly seen at the kint debug of condition:
If I use next command:
$res = Drupal::entityTypeManager()
->getStorage('node')
->loadByProperties([
'field_first' => ['12345', '45678']
'field_second' => 'test_value_1'
]);
The SQL query will be:
SELECT base_table.vid AS vid, base_table.nid AS nid
FROM node base_table
INNER JOIN node__field_first node__field_first ON node__field_first.entity_id = base_table.nid
INNER JOIN node__field_second node__field_second ON node__field_second.entity_id = base_table.nid
INNER JOIN node_field_data node_field_data ON node_field_data.nid = base_table.nid
WHERE ((LOWER(node__field_first.field_first_value) IN (LOWER('12345'),LOWER('45678')))) AND ((LOWER(node__field_second.field_second_value) IN (LOWER('12345')))) AND (node_field_data.default_langcode IN ('1'))
And kint debug:
We see, that for the field_first query has two placeholders :value1 and :value2, but for field_second again used placeholder :value1 instead of :value3. And as result we search `12345` in field_second instead of `test_value_1`.
If I uncomented line #22 and hide code in line #21
//$n = 1;
$n = count($sql_query->getArguments()) + 1;
And call to loadByProperties method I get next results. I display case when first field has multiple values.
SQL query become correct:
SELECT base_table.vid AS vid, base_table.nid AS nid
FROM node base_table
INNER JOIN node__field_first node__field_first ON node__field_first.entity_id = base_table.nid
INNER JOIN node__field_second node__field_second ON node__field_second.entity_id = base_table.nid
INNER JOIN node_field_data node_field_data ON node_field_data.nid = base_table.nid WHERE ((LOWER(node__field_first.field_first_value) IN (LOWER('12345'),LOWER('45678')))) AND ((LOWER(node__field_second.field_second_value) IN (LOWER('test_value_1')))) AND (node_field_data.default_langcode IN ('1'))
The field node__field_second.field_second_value has correct searching value test_value_1
And in condition kint debug we see that all placeholders are correct:
Please review described issue and if there is relevant fix it. Because it's very uncomfortable lose functionality of loadByProperties method.