Search code examples
mysqldoctrine-ormmany-to-manysymfony

Are join-tables from a ManyToMany association available for querying?

I am trying to create a query in my Invoice repository to return all Invoices that don't have any Payments associated to it.

The two entities have a ManyToMany association between them (note the invoice_payment join table) as in the following extracts of their entity definitions:

Class Invoice:

/**
 * @ORM\ManyToMany(targetEntity="Payment", inversedBy="invoice_ids")
 * @ORM\JoinTable(   name="invoice_payment",
 *            joinColumns={@ORM\JoinColumn(name="invoice_ids", referencedColumnName="id")},
 *     inverseJoinColumns={@ORM\JoinColumn(name="payment_ids", referencedColumnName="id")} )
 */
protected $payment_ids;

Class Payment:

    /**
     * @ORM\ManyToMany(targetEntity="Invoice", mappedBy="payment_ids")
     */
    protected $invoice_ids;

I'm conscious that if I can access the join table invoice_payment it should be a very simple lookup - however I haven't found anything in Symfony/Doctrine that discusses accessing non-entity database tables. Is this possible?

Solution

  • Give this a try.

    $qb = $this->createQueryBuilder('i');
$qb->leftJoin('i.payments_ids', 'p')
   ->andWhere('p is null');

    on a sidenote, your field names are misleading. payments_ids should be called payments - and same for invoice_ids as inoices. They dont return an id, they return a collection of objects.