Doctrine ManyToOne with join table - filter results

Question

I have a Customer entity and User entity with a JOIN relationship table customers_users.

In the Customer entity I have this.

/**
 * @var User
 * 
 * @ORM\ManyToMany(targetEntity="Entity\User")
 * @ORM\JoinTable(name="customers_users",
 *     joinColumns={@ORM\JoinColumn(name="customer_id", referencedColumnName="id")},
 *     inverseJoinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id", unique=true)}
 * )
 * 
 */
private $users;

I want that when a customer is created I can assign the users to it and actually it works well, however I want filter the users to show only those that don't are assigned to none customer.

For example, I have two customers and two users and the first customer has assigned two users; then when I edit the second customer, two users appear to be assigned to it but when I select these and I send the form, it throws the following exception:

An exception occurred while executing 'INSERT INTO customers_users (customer_id, user_id) VALUES (?, ?)' with params [2, 1]:

SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "uniq_2763c6cca76ed395"
DETAIL: Key (user_id)=(1) already exists.

Answer 1

What I actually did in this situation, is:

// UserAdmin.php that extends SonataAdminBundle's User admin

/**
 * {@inheritdoc}
 */
public function createQuery($context = 'list')
{
    /** @var QueryBuilder $query */
    $query = parent::createQuery();

    $request = Request::createFromGlobals();

    // Could not find another way to detect that admin list is created from
    // sonata_type_model_list parent.
    // Of course, this applies only to sonata_type_model_list field type.

    if ($request->query->get('pcode') == 'demo.admin.customer') {
        $alias = $query->getRootAliases()[0];

        $query->leftJoin($alias . '.customers', 'c');
        // $query-> ... do filtering here
    }

    return $query;
}

This way I could filter Users in any way I wanted.

Answer 2

/**
 * @var User
 * 
 * @ORM\ManyToMany(targetEntity="Entity\User", inversedBy="customers")
 * @ORM\JoinTable(name="customers_users",
 *     joinColumns={@ORM\JoinColumn(name="customer_id", referencedColumnName="id")},
 *     inverseJoinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id", unique=true)}
 * )
 * 
 */
private $users;

and in the User entity, in customers annotation you should put:

 * @ORM\ManyToMany(targetEntity="Entity\Customer", inversedBy="users")
(...)