Symfony Doctrine Mongo ODM queryBuilder only returning the first document

Question

I have this entity called Employee where one of the field is an ArrayCollection which contains another entity called Invoice stored as the invoice id.

Its worth mentioning here is that Employee and Invoice has many-to-many relationship. That is 1 employee document can have multiple different invoices and one invoice can have multiple different employees.

So now I want to build a query with which it will return all the employees that matches a certain invoice.

In my database, I have these two employees which have the same invoice stored as id in the array collection.

Employee collection:

{
  id: 'employee1',
  invoices: [
    ObjectId('invoice1')
  ]
}

{
  id: 'employee2',
  invoices: [
    ObjectId('invoice1')
  ]
}

Invoice collection:

{
  id: 'invoice1',
  employees: [
    ObjectId('employee1'),
    ObjectId('employee2')
  ]
}

When I run the query I only get the first employee instead of the two. Here's what I did:

public function findEmployeeByInvoiceQuery(Invoice $invoice) {
  $dm    = $this->getDocumentManager();
  $query = $dm->createQueryBuilder(Employee::class)->field('invoices')->in([$invoice]);
  
  return $query->getQuery()->execute();
}

Please note that when I check the debugger I saw invoices field as PersistenceCollection instead of ArrayCollection.

Answer 1

Can you show us the SQL code for this request via $query->getQuery()->getSQL() ?

I usually join the two table (with ManyToMany relation) to have exactly what I want (from a repository) :

$query = $this->createQueryBuilder('employee')
->innerJoin('employee.invoices', 'inv')
->andWhere('inv.id = :invoiceId')
->setParameter('invoiceId', $invoice);

Source for QueryBuilder : https://www.doctrine-project.org/projects/doctrine-orm/en/current/reference/query-builder.html

Answer 2

You need to use query builder's includesReferenceTo method to deal with querying ManyToMany.

$query = $dm->createQueryBuilder(Employee::class)->field('invoices')->includesReferenceTo($invoice);

Documentation: https://www.doctrine-project.org/projects/doctrine-mongodb-odm/en/2.3/reference/working-with-objects.html#by-reference

Offhand, why not use $invoice->getEmployees() as you already have it in place?