4

I'm stuck trying to reduce the number of database queries on a web api. My database has 3 collections : playground, widget, token

One playground has many widgets, one widget has one token. Each relationship uses referencesOne/referenceMany.

So here are my simplified models

/**
 * @MongoDB\Document()
 */
class Widget
{
    /**
     * @MongoDB\ReferenceOne(targetDocument="Token", inversedBy="widgets")
     */
    protected $token;

    /**
     * @MongoDB\ReferenceOne(targetDocument="Playground", inversedBy="widgets")
     */
    protected $playground;
}

/**
 * @MongoDB\Document()
 */
class Playground
{
    /**
     * @MongoDB\ReferenceMany(targetDocument="Widget", mappedBy="playground")
     */
    protected $widgets;
}

/**
 * @MongoDB\Document()
 */
class Token
{
    /**
     * @MongoDB\ReferenceMany(targetDocument="Widget", mappedBy="token")
     */
    protected $widgets;
}

I need to use the full playground with all its widgets and tokens but by default, Doctrine does too many queries : one to get the playground (ok), one to get all widgets of the mapping (ok) and for each widget, one query to get the token (not ok). Is there a way to query all tokens at once instead of getting them one by one ?

I've looked at prime but it does not seem to solve my problem...

Is there a way other than using the query builder and manually hydrate all objects to reduce the query count ?

Edit : As I added in my comment, what I'm looking for is get the playground and all its dependencies as a big object, json encode it and return it into the response.

What I do for now is query the playground and encode it but Doctrine populates the dependencies in a non efficient way : first there is the query to get the playgroung, then, there is one more query to get the related widgets and there is one query for each widget to get its token.

As one playground can have hundreds of widgets, this leads to hundreds of database queries.

What I'm looking for is a way to tell Doctrine to get all this data using only 3 queries (one to get the playgroung, one to get the widgets and one to get the tokens).

ᴄʀᴏᴢᴇᴛ
  • 2,939
  • 26
  • 44
  • I don't really understand the problem here. You want ALL `Token` in your DB ? You could do `$tokenRepository->findAll();` to have all `Token` and `$tokenRepository->findBy(array $criteria);` with `$criteria` your query params. – Etshy Mar 15 '19 at 14:43
  • @Etshy no, i'm not looking for a findAll on tokens, I want one playground and all its dependencies and return the document as json. Doctrine does not query the db in an efficient way : what it does is 1) get the playground, 2) get the related widgets, 3) for each widget, get the related token. As one playground can have hundreds of widgets, this leads to hundreds of database queries. I'm looking for a way to tell doctrine to loop over all playground widgets to get the related token ids and then get all tokens at once. This way no matter the number of widgets there will always be 3 queries. – ᴄʀᴏᴢᴇᴛ Mar 15 '19 at 15:18
  • Oh Ok. I'm not sure how you can do that. You could try something like `findBy(['widgets.id' => $widgetsIds])` with `$widgetsIds` being an array of ids. I'm really not sure `findBy` could work with array like that though. – Etshy Mar 15 '19 at 15:35
  • Have you considered using [aggregate](https://www.doctrine-project.org/projects/doctrine-mongodb-odm/en/1.2/reference/aggregation-builder.html#creating-an-aggregation-builder)? because to me it looks like all you need is an aggregate pipeline here. – lovubuntu Mar 26 '19 at 19:37
  • It may be simpler and more performant to simply write a custom query. – Dan Mar 27 '19 at 20:49
  • @lovubuntu That's what I planned to use but as i'm using DBRef to store relations, I'm not able to use `$lookup` (see here https://medium.com/@alcaeus/introducing-doctrine-mongodb-odm-1-2-bd85d6c8261a ) unless there is a way to migrate all existing documents to the new ref format ? – ᴄʀᴏᴢᴇᴛ Mar 29 '19 at 11:01

2 Answers2

1

update: since the ArrayCollection in the $playground should contain all the widgets at least as proxy objects (or should get loaded when accessed), the following should work to fetch all required tokens...

Since the document manager keeps all managed objects, it should prevent additional queries from occuring. (Notice the omitted assignment from the execute).

$qb = $dm->createQueryBuilder('Token')->findBy(['widget' => $playground->getWidgets()]);
$qb->getQuery()->execute();

inspired by this page on how to avoid doctrine orm traps - point 5

old/original answer

I'm not quite familiar with mongodb, tbh, but according to doctrine's priming references, you should be able to somewhat comfortably hydrate your playground by:

$qb = $dm->createQueryBuilder('Widget')->findBy(['playground' => $playground]);
$qb->field('token')->prime(true);
$widgets = $qb->getQuery()->execute();

however, I might be so wrong.

Jakumi
  • 8,043
  • 2
  • 15
  • 32
  • I already tried this but it does not work because i'm using the inverse side of the relation – ᴄʀᴏᴢᴇᴛ Apr 01 '19 at 08:00
  • @ᴄʀᴏᴢᴇᴛ this is probably a stupid question: the Widget in mongodb doesn't contain the ids of neither the playground nor the token? i wonder if the reversal of my logic works (see update). – Jakumi Apr 01 '19 at 09:07
  • I read your answer too fast, yes I can prime the tokens from the widgets (the widget has the token ID). This is more or less what I've ended up doing but I don't really like this solution because with this, I have the playground in one var and the widgets + tokens in an other var. So I cannot use `$playground->getWidgets()` (otherwise doctrine will run a db query as there is no result cache) or I have to manually set the widgets into the playground which I don't think is a good practice... anyway, it seems that there are no better options... perhaps, the problem is in the DB design – ᴄʀᴏᴢᴇᴛ Apr 01 '19 at 12:32
  • this would be the needed query : `$playgroundRepository->createQueryBuilder()->field('widgets.token')->prime()->field('id')->equals($playgroundId);` but unfortunately, I cannot prime deep relations – ᴄʀᴏᴢᴇᴛ Apr 01 '19 at 12:42
  • 1
    @ᴄʀᴏᴢᴇᴛ I believe you underestimate the DocumentManager. I'd say when you already load all the tokens, that it will prevent the "n+1" queries from occuring. please check (by just going over `$playground->getWidgets()` and then `$widget->getToken()`) – Jakumi Apr 01 '19 at 12:50
  • you're right, it works fine :) I still have a duplicate query on the widgets (no big deal) but no more n+1 problem – ᴄʀᴏᴢᴇᴛ Apr 01 '19 at 13:20
  • @ᴄʀᴏᴢᴇᴛ glad I could help ;o) – Jakumi Apr 01 '19 at 13:23
-1

What about that:

class Foo
{
    /** @var \Doctrine\ORM\EntityManagerInterface */
    private $entityManager;

    public function __construct(\Doctrine\ORM\EntityManagerInterface $entityManager)
    {
        $this->entityManager = $entityManager;
    }

    public function getAll(): array {
        $qb = $this->entityManager->createQueryBuilder();
        return $qb
            ->select('p,t,w')
            ->from(Playground::class, 'p')
            ->join(Widget::class, 'w')
            ->join(Token::class, 't')
            ->getQuery()
            ->getResult();
    }
}

At least with mysql backend, this solves the "n+1 problem". Not sure about MongoDB, though.

Thomas L.
  • 104
  • 8