Delete link between two table with a query builder

Question

I have made a queryBuilder inside an entity repository to delete a link between two table.

I have this two entities

Domain :

/**
 * @var int
 *
 * @ORM\Column(type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var string
 *
 * @ORM\Column(type="string", length=64)
 * @Assert\NotBlank
 * @Assert\Length(max="64")
 * @AppAssert\DomainName
 */
private $name;

// Some other fields

/**
 * @var SshKey[]|ArrayCollection
 *
 * @ORM\ManyToMany(targetEntity="AppBundle\Entity\SshKey", inversedBy="domains")
 * @ORM\JoinTable(name="domain_sshkey",
 *   joinColumns={@ORM\JoinColumn(referencedColumnName="id")},
 *   inverseJoinColumns={@ORM\JoinColumn(name="key_id", referencedColumnName="id")}
 * )
 */
private $sshKeys;

And SshKeys :

/**
 * @var int
 *
 * @ORM\Column(type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var \DateTime
 *
 * @Gedmo\Timestampable(on="create")
 * @ORM\Column(type="datetime")
 */
private $createdAt;

// Other fields

/**
 * @var Domain[]|ArrayCollection
 *
 * @ORM\ManyToMany(targetEntity="AppBundle\Entity\Domain", mappedBy="sshKeys")
 */
private $domains;

I am trying to delete links between this two tables when SshKeys id is in sshKeys field inside domain table.

So I made this query builder in my DomainRepository

public function deleteSshkeyDomainLink($invalidSshkey)
{
    $qb = $this->createQueryBuilder('d');

    $qb->delete()
       ->where($qb->expr()->in('ssh.id', ':ssh_keys_id'))
       ->setParameter('ssh_keys_id', $invalidSshkey)
       ->join('d.sshKeys', 'ssh')
    ;

    return $qb->getQuery()->execute();
}

But this QB return this error

[Doctrine\ORM\Query\QueryException]
[Semantical Error] line 0, col 39 near 'ssh.id IN(:s': Error: 'ssh' is not defined.

[Doctrine\ORM\Query\QueryException]
DELETE AppBundle\Entity\Domain d WHERE ssh.id IN(:ssh_keys_id)

I don't understand why this is returning ssh is not defined because I have made a join with this alias.

This query builder should work ? I really don't know how too fix this.

Thanks for your help.

Denis Alimov Denis Alimov · Accepted Answer · 2016-05-16T11:44:38

why do you want to delete Domain when you just need to delete sshKey from Domain (link between them)?

In Domain entity you can define method removeSshKey like this for example

public function removeSshKey(SshKey $key)
{
    $this->sshKeys->removeElement($key);

    return $this;
}

Then in controller where you want to delete the link between entities you should call it something like this

$domain = $this->getDoctrine()->getRepository('Domain')->find($domainId);
foreach ($domain->getSshKeys() as $sshKey)
{
    if ($sshKey->getId() == $invalidSshKeyId)
    {
        $domain->removeSshKey($sshKey);
    }   
}
$em = $this->getDoctrine()->getManager();
$em->flush();

this should delete the link

Delete link between two table with a query builder

1 Answers