Doctrine ManyToMany relation with join condition

Question

I've got a Many-To-Many relation between two tables but I want to add a join condition on the join table.

/**
 * @ManyToMany(targetEntity="Company", inversedBy="Accounts")
 * @JoinTable(name="account_company",
 *      joinColumns = { @JoinColumn(name="account_id", referencedColumnName="id") },
 *      inverseJoinColumns = { @JoinColumn(name="company_id", referencedColumnName="id") }
 * )
 */
protected $companies;

I would have a condition like "account_company.is_deleted = 0", how can I make it ?

Answer 1

Yes, you have the choice to hydrate your object and its ManyToMany collection using custom dql:

make a repository method that adds conditions on your join:

// repository class:
public function getAccountWithNonDeletedCompanies($id)
{
    return $this->createQueyBuilder('account')
        ->leftJoin('account.companies', 'companies', 'WITH', 'companies.deleted = 0')
        ->andWhere('account.id = :id')
        ->setParameter('id', $account)
        ->getQuery()
        ->getOneOrNullResult()
    ;
}

From what I can understand, you never want to display deleted companies ? (soft delete).

In this case, you may want to use SQL Filters: http://blog.loftdigital.com/doctrine-filters-and-soft-delete

Answer 2

Have dealt with this before and the only solution I got was creating a custom function that will make the query.

So on your Entity create a custom function getCompanies().