How do I query MongoDB collection based on related documents (in Doctrine)

Question

I have a few related collections in my Doctrine ODM project…

# Contract.mongodb.yml
Project\Contract\Domain\Contract:
  type:             document
  repositoryClass:  Project\SymfonyBundle\ContractBundle\Repository\ContractRepository
  collection:       Contracts
  fields:
    id:
      type:     id
      id:       true
      strategy: UUID
    slug:
      type:   string
      length: 128
      unique: true
      gedmo:
        slug:
          separator:  -
          style:      default
          fields:
            - refNo
            - name
    name:
      type: string
    refNo:
      type:   string
    purpose:
      type: string
    budgetAmount:
      type: int
    budgetCurrency:
      type:   string
      length: 3
    startDate:
      type: date_immutable
    endDate:
      type: date_immutable
    provider:
      type: string
  referenceOne:
    provider:
      targetDocument: Project\Contract\Domain\Provider
      cascade:
        - persist
        - merge
        - detach
        - refresh
  referenceMany:
    reports:
      targetDocument: Project\Report\Domain\Report
      cascade:
        - all

# Provider.mongodb.yml
Project\Contract\Domain\Provider:
  type:             document
  repositoryClass:  Project\SymfonyBundle\ContractBundle\Repository\ProviderRepository
  collection:       Providers
  fields:
    id:
      type:     id
      id:       true
      strategy: UUID
    slug:
      type:   string
      length: 128
      unique: true
      gedmo:
        slug:
          separator:  -
          style:      default
          fields:
            - name
    name:
      type:   string
      unique: true
  referenceMany:
    users:
      targetDocument: Project\User\Domain\User
      cascade:        []

# User.mongodb.yml
Project\User\Domain\User:
  type:             document
  repositoryClass:  Project\SymfonyBundle\UserBundle\Repository\UserRepository
  collection:       Users
  fields:
    id:
      type:     id
      id:       true
      strategy: UUID

What I want to do is get the contracts for a given user, but I can't work out how to query the Contracts collection based on a user. Do I need to make 2 queries? 1 to get the user's providers & then a second to query for contracts that link to one of the providers?

If you're able to advise how I do this in the console as well as Doctrine, I'd appreciate the knowledge.

Thanks in advance for your help :o)

Answer 1

You can use the aggregation pipeline and use the $lookup operator to join the document, See - https://docs.mongodb.com/v3.2/reference/operator/aggregation/lookup/

However if this is common, I'd consider re-modeling your documents.