Doctrine Out of Memory in Entity with to much entities in ManyToMany connection

Question

I have a memory issue with getting the collection of entities - Attachments. I am using PHP7.4, Symfony 5.4, Doctrine 2.7.5, APIPlatform 2.7

Class Attachment have this field:

/**
 * @ORM\ManyToMany(targetEntity="App\Entity\Message", inversedBy="attachment")
 */
private Collection $messages;

With classic getters and setters.

in my QueryCollectionExtension in Doctrine, ApiPlatform I have added:

private function addWhere(QueryBuilder $queryBuilder, string $resourceClass): void
    $queryBuilder
        ->andWhere('a.messages is not empty')
// breaks when preparing to execute this query 

Then I get this error

Error: Allowed memory size of 1073741824 bytes exhausted (tried to allocate 249856 bytes)" at /vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php line 138 
{"exception":"[object] (Symfony\\Component\\ErrorHandler\\Error\\OutOfMemoryError(code: 0)

My suspicion is that there are attachments that are sent in thousands of messages. when you try to access those attachments, you get an Entity with thousands of Message entities in $attachment->getMessages()

And that in a query where it breaks looks like this:

AND (
            SELECT
              COUNT(*)
            FROM
              attachment_message p4_
            WHERE
              p4_.attachment_id = p2_.id
          ) > ?

Without that part, the query runs slowly, but without any problem.

Does anyone have some solution on how to optimize this without restructuring entities?

Answer 1

Doctrine can't cover every use case for you out of the box. Especially when you have huge amount of data in many-to-many relationship.

But you can solve the problem by self. There are several methods to do so. And also you need to optimize your DB, if you haven't done it yet. For example, you need indexes on attachment_id and message_id. I hope Doctrine generated them in migrations but it's better to check just in case.

Some possible solutions:

1. Limit Hydration: instead of fully hydrating entities, consider using partial hydration or even a select to only fetch the columns you actually need. This can save a lot of memory when dealing with large collections.
2. Use the getArrayResult() method instead of getResult(). This will give you arrays instead of objects, which can be more memory efficient.
3. Use pagination. Do you really need all the data at this point?
4. And the most flexible solution — use native queries. You can set up own hydration for native query to fill the entities with data. For example you can use query like this:

SELECT a.*
FROM attachment a
INNER JOIN attachment_message am ON a.id = am.attachment_id
GROUP BY a.id
HAVING COUNT(am.message_id) > 0

It will not fetch messages at all, just attachments.