Question 40274 in PHP Question posted in
Official manual can be found here.

Symfony Doctrine Mongo ODM queryBuilder only returning the first document – PHP

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search