I get an SQL syntax error:
An exception occurred while executing 'INSERT INTO group (name) VALUES (?)' with params [\"Entwickler\"]:\n\nSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'group (name) VALUES ('Entwickler')' at line 1
while I try to persist an entity with a ManyToMany relation.
Scenario
I have an entity called folder and an entity called group. There is a ManyToMany relationship between these entities, because one group can have multiple folders, and one folder can have multiple groups.
There is already one folder with id 1. Now I want to create a group and want to add the folder with id 1 to this group.
Group Entity
<?php
namespace App\Entity;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
/**
* @ORM\Entity(repositoryClass="App\Repository\GroupRepository")
*/
class Group
{
/**
* @ORM\Id()
* @ORM\GeneratedValue()
* @ORM\Column(type="integer")
*/
private $id;
/**
* @ORM\Column(type="string", length=255)
*/
private $name;
/**
* @ORM\ManyToMany(targetEntity="App\Entity\Folder", inversedBy="groups")
* @ORM\JoinTable(
* name="group_folder",
* joinColumns={
* @ORM\JoinColumn(name="group_id", referencedColumnName="id")
* },
* inverseJoinColumns={
* @ORM\JoinColumn(name="folder_id", referencedColumnName="id")
* }
* )
*/
private $folder;
public function __construct()
{
$this->folder = new ArrayCollection();
}
public function getId()
{
return $this->id;
}
public function getName(): ?string
{
return $this->name;
}
public function setName(string $name): self
{
$this->name = $name;
return $this;
}
/**
* @return Collection|Folder[]
*/
public function getFolder(): Collection
{
return $this->folder;
}
public function addFolder(Folder $folder): self
{
if (!$this->folder->contains($folder)) {
$this->folder[] = $folder;
}
return $this;
}
public function removeFolder(Folder $folder): self
{
if ($this->folder->contains($folder)) {
$this->folder->removeElement($folder);
}
return $this;
}
}
Folder Entity
<?php
namespace App\Entity;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;
/**
* @ORM\Entity(repositoryClass="App\Repository\FolderRepository")
*/
class Folder
{
/**
* @ORM\Id()
* @ORM\GeneratedValue()
* @ORM\Column(type="integer")
*/
private $id;
/**
* @var string
*
* @ORM\Column(type="string", length=45)
* @Assert\NotBlank()
*/
private $name;
/**
* @var Secret[]
* @ORM\OneToMany(targetEntity="App\Entity\Secret", mappedBy="folder")
*/
private $secrets;
/**
* @var Folder[]
*
* @ORM\OneToMany(targetEntity="Folder", mappedBy="parent")
*/
private $children;
/**
* @var Folder
*
* @ORM\ManyToOne(targetEntity="Folder", inversedBy="children")
* @ORM\JoinColumn(name="parent", referencedColumnName="id")
*/
private $parent;
/**
* @ORM\ManyToMany(targetEntity="App\Entity\Group", mappedBy="folder")
*/
private $groups;
public function __construct()
{
$this->children = new ArrayCollection();
$this->secrets = new ArrayCollection();
$this->groups = new ArrayCollection();
}
public function getId()
{
return $this->id;
}
/**
* @return string
*/
public function getName()
{
return $this->name;
}
/**
* @param string $name
*/
public function setName($name)
{
$this->name = $name;
}
/**
* @return Secret[]|ArrayCollection
*/
public function getSecrets()
{
return $this->secrets;
}
/**
* @return Folder[]|ArrayCollection
*/
public function getChildren()
{
return $this->children;
}
/**
* @param Folder $children
*/
public function addChildren($children)
{
$this->children[] = $children;
$children->setParent($this);
}
/**
* @return Folder
*/
public function getParent()
{
return $this->parent;
}
/**
* @param Folder $parent
*/
public function setParent($parent)
{
$this->parent = $parent;
}
/**
* @return Collection|Group[]
*/
public function getGroups(): Collection
{
return $this->groups;
}
public function addGroup(Group $group): self
{
if (!$this->groups->contains($group)) {
$this->groups[] = $group;
$group->addFolder($this);
}
return $this;
}
public function removeGroup(Group $group): self
{
if ($this->groups->contains($group)) {
$this->groups->removeElement($group);
$group->removeFolder($this);
}
return $this;
}
}
GroupType Form
<?php
namespace App\Form;
use App\Entity\Folder;
use App\Entity\Group;
use Symfony\Bridge\Doctrine\Form\Type\EntityType;
use Symfony\Component\Form\AbstractType;
use Symfony\Component\Form\FormBuilderInterface;
use Symfony\Component\OptionsResolver\OptionsResolver;
class GroupType extends AbstractType
{
public function buildForm(FormBuilderInterface $builder, array $options)
{
$builder
->add('name')
->add('folder', EntityType::class, [
'class' => Folder::class,
'multiple' => true
])
;
}
public function configureOptions(OptionsResolver $resolver)
{
$resolver->setDefaults([
'data_class' => Group::class,
]);
}
}
Controller Action which I send the POST Request to
/**
* @param Request $request
* @return View
*
* @Rest\Post("/api/groups", name="api_group_create")
*/
public function CreateGroupAction(Request $request)
{
$group = new Group();
$form = $this->createForm(GroupType::class, $group);
$form->handleRequest($request);
if ($form->isSubmitted() && $form->isValid()) {
$em = $this->getDoctrine()->getManager();
$em->persist($group);
$em->flush();
return View::create($group, 201);
}
return View::create($form, 400);
}
The request body I send to /api/groups
{
"group": {
"name": "Entwickler",
"folder": [
1
]
}
}
I tried many diffenrent ways to solve that problem, but nothing works as i expected. That drives me nuts for hours now.
Thanks to Mathieu Dormeval, that was the solution.
I altered the group entity definition from:
/**
* @ORM\Entity(repositoryClass="App\Repository\GroupRepository")
*/
class Group
To:
/**
* @ORM\Entity(repositoryClass="App\Repository\GroupRepository")
* @ORM\Table(name="groups")
*/
class Group