How can I filter my doctrine request by the values of an array?

Question

I am loading data from my database with doctrine:

$data = $this->em->getRepository('App\\Entity\\Data')->allData();

That creates an array with a lot of data:

PagesGenerator.php on line 259:
array:1696 [▼
  0 => Data^ {#5430 ▼
    -id: 2298
    -uuid: "426441e418"
    -content: "two"
    -document_id: "93b7dc32f4"
  }
  1 => Data^ {#5434 ▼
    -id: 2299
    -uuid: "c1474a87a9"
    -content: ""
    -document_id: "93b7dc32f4"
  }
  2 => Data^ {#5413 ▼
    -id: 2300
    -uuid: "8bfd9b4893"
    -content: "three"
    -document_id: "edb8fa9647"
  }
  3 => Data^ {#5427 ▶}
  4 => Data^ {#5428 ▶}
  5 => Data^ {#5433 ▶}
  6 => Data^ {#5431 ▶}
  7 => Data^ {#5429 ▶}

I have another array $docIds:

array:96 [▼
  0 => "3019b7a82f"
  1 => "b1a4c67080"
  2 => "82cf4392d7"
  3 => "55768e0955"
  4 => "74c6217001"
  5 => "71fc4c3ebf"
  6 => "35e2d3a1c6"
  7 => "50c940b223"
  8 => "2561ff454d"
  9 => "bb447530e1"

To get a fast performance I like to prefilter the array $data to contain only entries with document_ids that are values in my array $docIds;

This is my approach:

$data = $this->em->getRepository('App\\Entity\\Data')->filterByDocID($docIds);

In my Data Repository:

public function filterByDocID($array)
{
  $query = $this->getEntityManager()
  ->createQuery(
    "SELECT a
    FROM App\Entity\Data a
    WHERE document_id IN ('".$array."')");
    try {
      return $query->getResult();
    } catch (\Doctrine\ORM\NoResultException $e) {
      return null;
    }
  }

But I get the error message:

Notice: Array to string conversion

php.net/manual/en/function.implode.php or symfony may have it's own way. — AbraCadaver
– AbraCadaver, Commented Jan 28, 2020 at 13:58
PHP/the query can't convert your array to a string properly, but you can before passing it to the query. — AbraCadaver
– AbraCadaver, Commented Jan 28, 2020 at 14:00
doctrine-project.org/projects/doctrine-dbal/en/latest/reference/… — AbraCadaver
– AbraCadaver, Commented Jan 28, 2020 at 14:06

spyro95 · Accepted Answer · 2020-01-28 14:44:35Z

1

There are two possiblities.

As they already wrote, you can implode your array.

WHERE document_id IN ('".implode(',', $array)."')");

or you're binding the array.

$stmt->bindValue(0, array(123, 234, 345, 456, 567, 678));

I would prefer binding the array, since this would be much cleaner than imploding and parsing as string.

E:

Since youre using strings as id, try the following:

WHERE document_id IN ('".implode("', '", $array)."')");

Otherwise try using LIKE

edited Jan 28, 2020 at 14:44

answered Jan 28, 2020 at 14:14

spyro95

1561 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

fala Over a year ago

Thank you, my error is gone, but now I get another error [Semantical Error] line 0, col 54 near 'document_id IN': Error: 'document_id' is not defined.

spyro95 Over a year ago

try a.document_id

fala Over a year ago

Ok, now all error messages are gone but the output of dump($data) is empty []. I tried only your implode version, because the other suggestion I am not sure where exaclty in my query I need to make the binding

spyro95 Over a year ago

Updated my answer

Collectives™ on Stack Overflow

How can I filter my doctrine request by the values of an array?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related