Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine
This tutorial has a new version, check it out!

ManyToMany Relationship

Keep on Learning!

If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.

Start your All-Access Pass
Buy just this tutorial for $10.00

With a Subscription, click any sentence in the script to jump to that part of the video!

Login Subscribe

We need the ability to add tags to each Article. And that means, we need a new relationship! Like always, we could add this by hand. But, the generator can help us. At your terminal, run:

php bin/console make:entity

But, hmm. Which entity should we update? We could add a new property to Article called tags or... I guess we could also add a new property to Tag called articles. That's really the same relationship, just viewed from two different sides.

And... yea! We could choose to update either class. The side you choose will make a subtle difference, and we'll learn about it soon. Let's update Article. For the property name, use tags. Remember, we need to stop thinking about the database and only think about our objects. In PHP, I want an Article object to have many Tag objects. So, the property should be called tags.

For type, use the fake relation type to activate the relationship wizard. We want to relate this to Tag and... perfect! Here is our menu of relationship options! I already hinted that this will be a ManyToMany relationship. But, let's look at the description to see if it fits. Each article can have many tags. And, each tag can relate to many articles. Yep, that's us! This is a ManyToMany relationship.

And just like last time, it asks us if we also want to map the other side of the relationship. This is optional, and is only for convenience. If we map the other side, we'll be able to say $tag->getArticles(). That may or may not be useful for us, but let's say yes. Call the field articles, because it will hold an array of Article objects.

And, that's it! Hit enter to finish.

Looking at the Generating Entities

Exciting! Let's see what changes this made. Open Article first:

... lines 1 - 15
class Article
{
... lines 18 - 68
/**
* @ORM\ManyToMany(targetEntity="App\Entity\Tag", inversedBy="articles")
*/
private $tags;
... lines 73 - 245
}

Yes! Here is the new tags property: it's a ManyToMany that points to the Tag entity. And, like we saw earlier with comments, whenever you have a relationship that holds many objects, in your constructor, you need to initialize that property to a new ArrayCollection:

... lines 1 - 15
class Article
{
... lines 18 - 73
public function __construct()
{
... line 76
$this->tags = new ArrayCollection();
}
... lines 79 - 245
}

The generator did that for us.

At the bottom, instead of a getter & setter, we have a getter, adder & remover:

... lines 1 - 15
class Article
{
... lines 18 - 220
/**
* @return Collection|Tag[]
*/
public function getTags(): Collection
{
return $this->tags;
}
public function addTag(Tag $tag): self
{
if (!$this->tags->contains($tag)) {
$this->tags[] = $tag;
}
return $this;
}
public function removeTag(Tag $tag): self
{
if ($this->tags->contains($tag)) {
$this->tags->removeElement($tag);
}
return $this;
}
}

There's no special reason for that: the adder & remover methods are just convenient.

Next, open Tag:

103 lines src/Entity/Tag.php
... lines 1 - 13
class Tag
{
... lines 16 - 35
/**
* @ORM\ManyToMany(targetEntity="App\Entity\Article", mappedBy="tags")
*/
private $articles;
public function __construct()
{
$this->articles = new ArrayCollection();
}
... lines 45 - 74
/**
* @return Collection|Article[]
*/
public function getArticles(): Collection
{
return $this->articles;
}
public function addArticle(Article $article): self
{
if (!$this->articles->contains($article)) {
$this->articles[] = $article;
$article->addTag($this);
}
return $this;
}
public function removeArticle(Article $article): self
{
if ($this->articles->contains($article)) {
$this->articles->removeElement($article);
$article->removeTag($this);
}
return $this;
}
}

The code here is almost identical: a ManyToMany pointing back to Article and, at the bottom, getter, adder & remover methods.

Owning Versus Inverse Sides

Great! But, which side is the owning side and which is the inverse side of the relationship? Open Comment:

... lines 1 - 10
class Comment
{
... lines 13 - 31
/**
* @ORM\ManyToOne(targetEntity="App\Entity\Article", inversedBy="comments")
* @ORM\JoinColumn(nullable=false)
*/
private $article;
... lines 37 - 94
}

Remember, with a ManyToOne / OneToMany relationship, the ManyToOne side is always the owning side of the relation. That's easy to remember, because this is where the column lives in the database: the comment table has an article_id column.

But, with a ManyToMany relationship, well, both sides are ManyToMany! In Article, ManyToMany. In Tag, the same! So, which side is the owning side?

The answer lives in Article. See that inversedBy="articles" config?

... lines 1 - 15
class Article
{
... lines 18 - 68
/**
* @ORM\ManyToMany(targetEntity="App\Entity\Tag", inversedBy="articles")
*/
private $tags;
... lines 73 - 245
}

That points to the articles property in Tag. On the other side, we have mappedBy="tags", which points back to Article:

103 lines src/Entity/Tag.php
... lines 1 - 13
class Tag
{
... lines 16 - 35
/**
* @ORM\ManyToMany(targetEntity="App\Entity\Article", mappedBy="tags")
*/
private $articles;
... lines 40 - 101
}

Here's the point: with a ManyToMany relationship, you choose the owning side by where the inversedBy versus mappedBy config lives. The generator configured things so that Article holds the owning side because that's the entity we chose to update with make:entity.

Remember, all of this owning versus inverse stuff is important because, when Doctrine saves an entity, it only looks at the owning side of the relationship to figure out what to save to the database. So, if we add tags to an article, Doctrine will save that correctly. But, if you added articles to a tag and save, Doctrine would do nothing. Well, in practice, if you use make:entity, that's not true. Why? Because the generated code synchronizes the owning side. If you call $tag->addArticle(), inside, that calls $article->addTag():

103 lines src/Entity/Tag.php
... lines 1 - 13
class Tag
{
... lines 16 - 82
public function addArticle(Article $article): self
{
if (!$this->articles->contains($article)) {
$this->articles[] = $article;
$article->addTag($this);
}
return $this;
}
... lines 92 - 101
}

Generating the Migration

Enough of that! Let's generate the migration:

php bin/console make:migration

Cool! Go open that file:

... lines 1 - 2
namespace DoctrineMigrations;
use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;
/**
* Auto-generated Migration: Please modify to your needs!
*/
class Version20180501143055 extends AbstractMigration
{
public function up(Schema $schema)
{
// this up() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('CREATE TABLE article_tag (article_id INT NOT NULL, tag_id INT NOT NULL, INDEX IDX_919694F97294869C (article_id), INDEX IDX_919694F9BAD26311 (tag_id), PRIMARY KEY(article_id, tag_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
$this->addSql('ALTER TABLE article_tag ADD CONSTRAINT FK_919694F97294869C FOREIGN KEY (article_id) REFERENCES article (id) ON DELETE CASCADE');
$this->addSql('ALTER TABLE article_tag ADD CONSTRAINT FK_919694F9BAD26311 FOREIGN KEY (tag_id) REFERENCES tag (id) ON DELETE CASCADE');
}
public function down(Schema $schema)
{
// this down() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('DROP TABLE article_tag');
}
}

Woh! It creates a new table! Of course! That's how you model a ManyToMany relationship in a relational database. It creates an article_tag table with only two fields: article_id and tag_id.

This is very different than anything we've seen so far with Doctrine. This is the first time - and really, the only time - that you will have a table in the database, that has no direct entity class. This table is created magically by Doctrine to help us relate tags and articles. And, as we'll see next, Doctrine will also automatically insert and delete records from this table as we add and remove tags from an article.

Now, run the migration:

php bin/console doctrine:migrations:migrate

Let's go tag some articles!

Leave a comment!

40
Login or Register to join the conversation
Remus M. Avatar
Remus M. Avatar Remus M. | posted 3 years ago

any idea howto fix this ?

Could not determine access type for property "ship" in class "App\Entity\Members": The property "ship" in class "App\Entity\Members" can be defined with the methods "addShip()", "removeShip()" but the new value must be an array or an instance of \Traversable, "App\Entity\Ships" given.

i have a ManyToMany relation between them two
in a form i get this error
even if i have the add & remove methods


// Members

public function addShip(Ships $ship): self
{
if (!$this->ship->contains($ship)) {
$this->ship[] = $ship;
}


return $this;
}


public function removeShip(Ships $ship): self
{
if ($this->ship->contains($ship)) {
$this->ship->removeElement($ship);
}


return $this;
}

1 Reply
Maik T. Avatar
Maik T. Avatar Maik T. | Remus M. | posted 3 years ago | edited

Hey Remus M.

I have an idea. Try syncing both sides when adding/removing items


public function addShip(Ships $ship): self
{
if (!$this->ship->contains($ship)) {
$this->ship[] = $ship;
$ship->addMemeber($this);
}

If it doesn't work I may have too give it a look to your code. Cheers!

1 Reply
Musa Avatar

Hey Ryan!
I'm back with another brain itcher that's been consuming most of my afternoon.

I'm curios why doctrine truncates my select query when using multiple join selects with queryBuilder.
My query is as follows:


$data = $this->createQueryBuilder('f')
->leftJoin('f.customer', 'c')
->leftJoin('c.accountManager', 'user')
->leftJoin('user.managedTeams', 'teams')
->addSelect(c)
->addSelect(user)
->addSelect(teams)
->getQuery()->getArrayResult();


When i run this query i get ALL the data from the included entities.
However, if i swap to for example:


$data = $this->createQueryBuilder('f')
->leftJoin('f.customer', 'c')
->leftJoin('c.accountManager', 'user')
->leftJoin('user.managedTeams', 'teams')
->addSelect(c.id, c.foo, c.bar)
->addSelect(user.name, user.otherFoo)
->addSelect(teams)
->getQuery()->getArrayResult();


The result gets split as expected into the subselects, but any child full select gets truncated.

What i want and expect:


[
0 => [f...]
id => value,
foo => value,
bar => value,
name => value,
otherFoo => value,
teams => [
team1...
team2...
team3...
]
]

What i get:


[
0 => [f...]
id => value,
foo => value,
bar => value,
name => value,
otherFoo => value
]

When i instead take this query and dump the sql with:


->getQuery()->getSql()


then run this sql in my sql editor, i get the expected result, teams are included and all the team names listed.

For reference the relations are:
f -> customer (ManyToOne) customer->user (accountmanager) (ManyToOne) user->teams (oneToMany)

So why does doctrine truncate child relations when you choose to pick specific columns from a parent join?

I've only run into one stackoverflow post asking about this, and he had the same problem but got no response, only people telling him to use addSelect, with him rebutting that it only works if you fetch the entirety of all related entities.

Reply

Hey Musa!

This is really interesting! Unfortunately, I don't have a great answer :/. Your expected result certainly makes sense to me... my best guess is that some limitation or design decision inside how Doctrine maps the results is causing the problem. In general... I think it's just not meant to be used in this way (though, again, your expectation makes sense to me). So... I can't give you a workaround: my guess is that this just isn't possible (or if it is, I don't know the answer). In my code, I don't usually do things like this... but part of that is a subjective decision. I have no problem grabbing ALL of the data from all of the entities: it makes my life a lot simpler. Is it a performance problem to grab a extra columns from tables that I'm not going to use? I don't know! I use Blackfire to profile, and I have yet to identify this type of situation as a *real*, material performance problem (the exception being if I'm doing some HUGE batch processing... or exporting a GIANT CSV file or something. In those cases, I'll select precisely the columns I need since we're grabbing so many rows).

So my advice would be to select everything :). But it IS a bummer that this doesn't work. It's an area of Doctrine that I don't know as well as other areas.

Sorry I can't give you a more satisfying answer!

Reply
Dhrubajyoti D. Avatar
Dhrubajyoti D. Avatar Dhrubajyoti D. | posted 1 year ago

Trying to update operation with many to many:

I have two entites

PromoCode Entity


namespace App\Entity;

use App\Repository\PromoCodeRepository;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

/**
* @ORM\Entity(repositoryClass=PromoCodeRepository::class)
*/
class PromoCode
{
/**
* @ORM\Id
* @ORM\GeneratedValue
* @ORM\Column(type="integer")
*/
private $id;

/**
* @ORM\Column(type="string", length=32)
*/
private $code;

/**
* @ORM\Column(type="string", length=128)
*/
private $heading;

/**
* @ORM\Column(type="string", length=255)
*/
private $content;

/**
* @ORM\Column(type="date", nullable=true)
*/
private $validFromDate;

/**
* @ORM\Column(type="date", nullable=true)
*/
private $validToDate;

/**
* @ORM\Column(type="integer", nullable=true, options={"unsigned"=true})
*/
private $validOnMinimumPurchase = 0;

/**
* @ORM\Column(type="boolean")
*/
private $isApplicableToAllProducts;

/**
* @ORM\ManyToMany(targetEntity=Category::class, mappedBy="promoCodes")
*/
private $categories;

/**
* @ORM\ManyToMany(targetEntity=Product::class, mappedBy="promoCodes")
*/
private $products;

/**
* @ORM\Column(type="boolean")
*/
private $isPercentageValue;

/**
* @ORM\Column(type="integer")
*/
private $percentageValue = 0;

/**
* @ORM\Column(type="integer")
*/
private $monetaryValue = 0;

public function getValue(): int
{
return $this->getIsPercentageValue()
? $this->getPercentageValue()
: $this->getMonetaryValue();
}

public function __construct()
{
$this->categories = new ArrayCollection();
$this->products = new ArrayCollection();
}

public function getId(): ?int
{
return $this->id;
}

public function getHeading(): ?string
{
return $this->heading;
}

public function setHeading(string $heading): self
{
$this->heading = $heading;

return $this;
}

public function getContent(): ?string
{
return $this->content;
}

public function setContent(string $content): self
{
$this->content = $content;

return $this;
}

public function getValidFromDate(): ?\DateTimeInterface
{
return $this->validFromDate;
}

public function setValidFromDate(?\DateTimeInterface $validFromDate): self
{
$this->validFromDate = $validFromDate;

return $this;
}

public function getValidToDate(): ?\DateTimeInterface
{
return $this->validToDate;
}

public function setValidToDate(?\DateTimeInterface $validToDate): self
{
$this->validToDate = $validToDate;

return $this;
}

public function getValidOnMinimumPurchase(): ?int
{
return $this->validOnMinimumPurchase;
}

public function setValidOnMinimumPurchase(?int $validOnMinimumPurchase): self
{
$this->validOnMinimumPurchase = $validOnMinimumPurchase;

return $this;
}

public function getIsApplicableToAllProducts(): ?bool
{
return $this->isApplicableToAllProducts;
}

public function setIsApplicableToAllProducts(bool $isApplicableToAllProducts): self
{
$this->isApplicableToAllProducts = $isApplicableToAllProducts;

return $this;
}

/**
* @return Collection|Category[]
*/
public function getCategories(): Collection
{
return $this->categories;
}

public function addCategory(Category $category): self
{
if (!$this->categories->contains($category)) {
$this->categories[] = $category;
$category->addPromoCode($this);
}

return $this;
}

public function removeCategory(Category $category): self
{
if ($this->categories->removeElement($category)) {
$category->removePromoCode($this);
}

return $this;
}

/**
* @return Collection|Product[]
*/
public function getProducts(): Collection
{
return $this->products;
}

public function addProduct(Product $product): self
{
if (!$this->products->contains($product)) {
$this->products[] = $product;
$product->addPromoCode($this);
}

return $this;
}

public function removeProduct(Product $product): self
{
if ($this->products->removeElement($product)) {
$product->removePromoCode($this);
}

return $this;
}

public function __toString(): string
{
return $this->heading;
}

public function getIsPercentageValue(): ?bool
{
return $this->isPercentageValue;
}

public function setIsPercentageValue(bool $isPercentageValue): self
{
$this->isPercentageValue = $isPercentageValue;

return $this;
}

public function getPercentageValue(): ?int
{
return $this->percentageValue;
}

public function setPercentageValue(int $percentageValue): self
{
$this->percentageValue = $percentageValue;

return $this;
}

public function getMonetaryValue(): ?int
{
return $this->monetaryValue;
}

public function setMonetaryValue(int $monetaryValue): self
{
$this->monetaryValue = $monetaryValue;

return $this;
}

public function getCode(): ?string
{
return $this->code;
}

public function setCode(string $code): self
{
$this->code = $code;

return $this;
}

public function isDateValid()
{
if ($this->getValidFromDate() == null) {
return true;
}
$today = new \DateTime();
if ($this->getValidToDate() == null && $this->getValidFromDate() <= $today) {
return true;
}
return ($this->getValidFromDate() <= $today) && ($today <= $this->getValidToDate());
}
}

and Category Entity


namespace App\Entity;

use App\Repository\CategoryRepository;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\Common\Collections\Criteria;
use Doctrine\ORM\Mapping as ORM;

/**
* @ORM\Entity(repositoryClass=CategoryRepository::class)
*/
class Category
{
/**
* @ORM\Id
* @ORM\GeneratedValue
* @ORM\Column(type="integer")
*/
private $id;

/**
* @ORM\Column(type="string", length=128)
*/
private $heading;

/**
* @ORM\Column(type="string", length=164, unique=true)
*/
private $slug;

/**
* @ORM\Column(type="smallint", options={"unsigned": true})
*/
private $position = 0;

/**
* @ORM\ManyToOne(targetEntity=Category::class, inversedBy="children")
*/
private $parent;

/**
* @ORM\OneToMany(targetEntity=Category::class, mappedBy="parent", fetch="EAGER")
*/
private $children;

/**
* @ORM\OneToMany(targetEntity=Product::class, mappedBy="category", orphanRemoval=true)
* @ORM\OrderBy({"position" = "ASC"})
*/
private $products;

/**
* @ORM\ManyToMany(targetEntity=PromoCode::class, inversedBy="categories")
*/
private $promoCodes;

public function __construct()
{
$this->children = new ArrayCollection();
$this->products = new ArrayCollection();
$this->promoCodes = new ArrayCollection();
}

public function getId(): ?int
{
return $this->id;
}

public function getHeading(): ?string
{
return $this->heading;
}

public function setHeading(string $heading): self
{
$this->heading = $heading;

return $this;
}

public function getSlug(): ?string
{
return $this->slug;
}

public function setSlug(string $slug): self
{
$this->slug = $slug;

return $this;
}

public function getPosition(): ?int
{
return $this->position;
}

public function setPosition(int $position): self
{
$this->position = $position;

return $this;
}

public function getParent(): ?self
{
return $this->parent;
}

public function setParent(?self $parent): self
{
$this->parent = $parent;

return $this;
}

/**
* @return Collection|self[]
*/
public function getChildren(): Collection
{
return $this->children;
}

public function addChild(self $child): self
{
if (!$this->children->contains($child)) {
$this->children[] = $child;
$child->setParent($this);
}

return $this;
}

public function removeChild(self $child): self
{
if ($this->children->removeElement($child)) {
// set the owning side to null (unless already changed)
if ($child->getParent() === $this) {
$child->setParent(null);
}
}

return $this;
}

/**
* @return Collection|Product[]
*/
public function getProducts(): Collection
{
return $this->products;
}

public function addProduct(Product $product): self
{
if (!$this->products->contains($product)) {
$this->products[] = $product;
$product->setCategory($this);
}

return $this;
}

public function removeProduct(Product $product): self
{
if ($this->products->removeElement($product)) {
// set the owning side to null (unless already changed)
if ($product->getCategory() === $this) {
$product->setCategory(null);
}
}

return $this;
}

public function __toString(): string
{
return $this->getParent()->getHeading().' / '.$this->getHeading();
}

/**
* @return Collection|PromoCode[]
*/
public function getPromoCodes(): Collection
{
return $this->promoCodes;
}

public function addPromoCode(PromoCode $promoCode): self
{
if (!$this->promoCodes->contains($promoCode)) {
$this->promoCodes[] = $promoCode;
}

return $this;
}

public function removePromoCode(PromoCode $promoCode): self
{
$this->promoCodes->removeElement($promoCode);

return $this;
}

public function getProductsSortedBy($criteria): Collection
{
$currentCriteria = Criteria::create()
->orderBy($criteria['attr']);

return $this->getProducts()->matching($currentCriteria);

}
}

When creating and updating (CRUD) from symfony default controller for category entity the promocodes relation are working fine. But when updating (CRUD) from PromoCode the categories relation do not update properly.

I am doing this in PromoCodeController manually


namespace App\Controller;

use App\Entity\PromoCode;
use App\Form\PromoCodeType;
use App\Repository\PromoCodeRepository;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;

/**
* @Route("/backend-promo-code-crud", name="promo_code_crud_")
*/
class PromoCodeCrudController extends AbstractController
{
/**
* @Route("/", name="index", methods={"GET"})
*/
public function index(PromoCodeRepository $promoCodeRepository): Response
{
return $this->render('promo_code_crud/index.html.twig', [
'promo_codes' => $promoCodeRepository->findBy([], ['id' => 'desc']),
]);
}

/**
* @Route("/new", name="new", methods={"GET","POST"})
*/
public function new(Request $request): Response
{
$promoCode = new PromoCode();
$form = $this->createForm(PromoCodeType::class, $promoCode);
$form->handleRequest($request);

if ($form->isSubmitted() && $form->isValid()) {
if (!empty($products = $promoCode->getProducts())) {
foreach($products as $product) {
$product->addPromoCode($promoCode);
}
}

if (!empty($promoCode->getCategories())) {
foreach($promoCode->getCategories() as $category) {
$category->addPromoCode($promoCode);
}
}
$entityManager = $this->getDoctrine()->getManager();
$entityManager->persist($promoCode);
$entityManager->flush();

return $this->redirectToRoute('promo_code_crud_index', [], Response::HTTP_SEE_OTHER);
}

return $this->renderForm('promo_code_crud/new.html.twig', [
'promo_code' => $promoCode,
'form' => $form,
]);
}

/**
* @Route("/{id}", name="show", methods={"GET"})
*/
public function show(PromoCode $promoCode): Response
{
return $this->render('promo_code_crud/show.html.twig', [
'promo_code' => $promoCode,
]);
}

/**
* @Route("/{id}/edit", name="edit", methods={"GET","POST"})
*/
public function edit(Request $request, PromoCode $promoCode, PromoCodeRepository $promoCodeRepository): Response
{
$form = $this->createForm(PromoCodeType::class, $promoCode);
if ($request->isMethod('POST')) {
foreach($promoCode->getProducts() as $product) {
$product->removePromoCode($promoCode);
}
foreach($promoCode->getCategories() as $category) {
$category->removePromoCode($promoCode);
}
$this->getDoctrine()->getManager()->flush();
$form->handleRequest($request);

if ($form->isSubmitted() && $form->isValid()) {
if (!empty($products = $promoCode->getProducts())) {
foreach($products as $product) {
$product->addPromoCode($promoCode);
}
}

if (!empty($promoCode->getCategories())) {
foreach($promoCode->getCategories() as $category) {
$category->addPromoCode($promoCode);
}
}

$this->getDoctrine()->getManager()->flush();
}
return $this->redirectToRoute('promo_code_crud_index', [], Response::HTTP_SEE_OTHER);
}

return $this->renderForm('promo_code_crud/edit.html.twig', [
'promo_code' => $promoCode,
'form' => $form,
]);
}

/**
* @Route("/{id}", name="delete", methods={"POST"})
*/
public function delete(Request $request, PromoCode $promoCode): Response
{
if ($this->isCsrfTokenValid('delete'.$promoCode->getId(), $request->request->get('_token'))) {
$entityManager = $this->getDoctrine()->getManager();
$entityManager->remove($promoCode);
$entityManager->flush();
}

return $this->redirectToRoute('promo_code_crud_index', [], Response::HTTP_SEE_OTHER);
}
}

PromoCodeType


namespace App\Form;

use App\Entity\Category;
use App\Entity\PromoCode;
use App\Repository\CategoryRepository;
use Symfony\Bridge\Doctrine\Form\Type\EntityType;
use Symfony\Component\Form\AbstractType;
use Symfony\Component\Form\Extension\Core\Type\MoneyType;
use Symfony\Component\Form\Extension\Core\Type\PercentType;
use Symfony\Component\Form\FormBuilderInterface;
use Symfony\Component\OptionsResolver\OptionsResolver;
use Symfony\Component\Validator\Constraints\NotBlank;

class PromoCodeType extends AbstractType
{
private $categoryRepository;

public function __construct(CategoryRepository $categoryRepository)
{
$this->categoryRepository = $categoryRepository;
}

public function buildForm(FormBuilderInterface $builder, array $options)
{
$builder
->add('code', null, [
'empty_data' => '',
'constraints' => [
new NotBlank([
'normalizer' => 'trim'
])
]
])
->add('heading', null, [
'empty_data' => '',
'constraints' => [
new NotBlank([
'normalizer' => 'trim'
])
]
])
->add('content', null, [
'empty_data' => '',
'constraints' => [
new NotBlank([
'normalizer' => 'trim'
])
]
])
->add('isPercentageValue', null, [
'attr' => ['data-action' => 'promo-crud#toggle']
])
->add('percentageValue', PercentType::class, [
'required' => false,
'type' => 'integer'
])
->add('monetaryValue', MoneyType::class, [
'required' => false,
'currency' => 'INR',
'divisor' => 100
])
->add('validFromDate', null, [
'widget' => 'single_text'
])
->add('validToDate', null, [
'widget' => 'single_text'
])
->add('validOnMinimumPurchase', MoneyType::class, [
'required' => false,
'currency' => 'INR',
'divisor' => 100
])
->add('isApplicableToAllProducts')
->add('categories', EntityType::class, [
'required' => false,
'expanded' => true,
'multiple' => true,
'class' => Category::class,
'choices' => $this->categoryRepository->findAllChildren()
])
->add('products', null, [
'required' => false,
'expanded' => true,
])
;
}

public function configureOptions(OptionsResolver $resolver)
{
$resolver->setDefaults([
'data_class' => PromoCode::class,
]);
}
}

The promocode controller update code is really ugly but works fine. What can I do to improve the update code ? Please guide me.

Reply

Hey Dhrubajyoti D.!

Ok, I might have a simple answer for you :). If i remember correctly, in your form, you need a by_reference => false option on each of your EntityType that are set to multiple => true. Without this, the form system calls getCategories(), then modifies this "Collection" object directly to add/remove items, which doesn't work for the inverse side of your relation. By setting by_reference to false, it forces it to use your addCategory() and removeCategory() methods, which have the code needed to synchronize the owning side of the relation: https://symfonycasts.com/sc...

Let me know if that helps :).

Cheers!

Reply
Dhrubajyoti D. Avatar
Dhrubajyoti D. Avatar Dhrubajyoti D. | weaverryan | posted 1 year ago

Thanks. Yes that works fine. Great tutorial, i just watched the whole seris. and I must the older tutorials also because the concepts works on symfony 5. Your teaching are so simple to understand and follow. JavaScript serise is also great. Please, make some tutorial on using native sql and cte sql query in doctrine

Reply

Hey Dhrubajyoti D.

Many thanks for your feedback! We are so happy that you like it and we will do our best to keep it simple and understandable!
BTW! I registered your request about Native SQL queries in doctrine so keep watching some day it will be recorded. :)

Cheers!

Reply
mega Avatar

Hello.

Is there a way to add extra fields to the relational table?
For example:

I have company table and employee table, and company_employee which represents a ManyToMany relationship between Company entity and Employee entity. So, the same employee can belong to multiple companies and a company can have multiple employees. But an employee X for company A can be an administrator, and for company B just a non-administrator employee.
My question is, can i add "isAdministrator" to company_employee relational table and how can i do it using doctrine/symfony make tool, since there is no entity for the relational table?

Reply
MolloKhan Avatar MolloKhan | SFCASTS | mega | posted 1 year ago | edited

Hey mega

As soon as you need to store extra data on a ManyToMany relationship you have to create an Entity class for the relational table and switch to a ManyToOne on both sides. At the end of this chapter Ryan talks more about how to do so https://symfonycasts.com/sc...

If you still have questions, please let us know. Cheers!

Reply
Mike P. Avatar
Mike P. Avatar Mike P. | posted 2 years ago

Goal:
Just a simpel "left join" for a ManyToMany relation (Many Articles have Many Categories)

Code (minified to just reproduce the error):


class ArticleRepository extends ServiceEntityRepository
...
public function getTest()
{
$qb = $this->createQueryBuilder('a')
->leftJoin('a.categories', 'c')
->addSelect('c')
->andWhere('a.categories IN (:categories)')
->setParameter('categories', $this->categoryRepository->findAll()) // Just for testing purposes I dependency injected the categoryRepository
;
dd($qb
->getQuery()
->getResult())
;
}

Problem:
This code results in a:
[Semantical Error] line 0, col 70 near 'categories IN': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.

$this->categoryRepository->findAll() is definitely returning an array of Category Entitys.

getDQL() returns the correct query:

"SELECT a, c FROM App\Entity\Article a LEFT JOIN a.categories c WHERE a.categories IN (:categories)"


So strange... can you help?

UPDATE://
If I change the Repo to UserRepository and set a.author IN (:authors) with $userRepo->findAll(), its working.
Conclusion: The problem only occurs with my category entity / repository.

Reply
Mike P. Avatar

UPDATE2://
The reason seems to be that its a ManyToMany relationship.
(And not a ManyToOne Relationship, like in the working a.author column.)

Unsuccessful code:


$qb
->leftJoin('r.categories', 'c')
->addSelect('c');
->andWhere('a.categories IN (:categories)')
->setParameter('categories', $this->categoryRepository->findAll()) // Just for testing purposes I dependency injected the categoryRepository

Output:
[Semantical Error] line 0, col 70 near 'categories IN': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.

Successful code:

A.)


$qb
->leftJoin('r.categories', 'c')
->addSelect('c');
->andWhere(':categoriesId MEMBER OF a.categories')
->setParameter('categoriesId', $this->categoryRepository->findAll())

B.)


$qb
->leftJoin('r.categories', 'c')
->addSelect('c');
->andWhere('c.id IN (:categoriesId)')
->setParameter('categoriesId', $categories);

Final questions:
1.) But why does the new code work and the old doesn't?
Because of the ManyToMany connection instead of ManyToOne? I don't understand why I have to explicitly select the .id instead of giving just the array of category entitys.
2.) If the user visits: "./categories/cat1,cat2", he should get every article that has cat1 AND cat2.
The IN query above is not suitable (because it is treating like cat1 OR cat2). I've tried another method:


foreach ($categories as $key => $category) {
$qb->andWhere('c.id = :categoryId'.$key)
->setParameter('categoryId'.$key, $category);
}


But this doesn't work as well (it querys in the article_category ManyToMany table for rows that have category_id = cat1 AND category_id = cat2, which of course would NEVER happen, because it should instead look for article_id's which has both category_id's (so it should output only articles with 2 datasets(one dataset for each category)))
Do you know what kind of query is necessary to do exactly this, to show articles that have BOTH categories set on it? (ManyToMany of Articles and Categories)

UPDATE3://
I've found an answer to question 2.):


foreach ($categories as $key => $category) {
$qb->andWhere(':categoriesId'.$key.' MEMBER OF a.categories')
->setParameter('categoriesId'.$key, $category)
;
}

So only 1.) is open :)

1 Reply

Hey Mike,

Wow, a lot of updates :D Yes, good catch! That;'s because of ManyToMany relation, it's a tricky relation... technically you're operating 2 entities, but behind the scene there're 3 tables, one of them is auxiliary table. Probably there're some technical reasons behind the scene, but I'm not sure what exactly. I bet it might be related to the code complexity. Anyway, I'm glad you figured out the working solution for you by yourself

Cheers!

Reply

Hi! I'm doing a custom query to select from manytomany, I have articles and tags like you have but I would like when i search a word to check if the word is a category or not.
I have done that: in controller i pass :term which is the word to:

/**
* @param string|null $term
* @param int|null $id
* @return QueryBuilder
*/
public function getQueryBuilderSearchAll(?string $term, ?int $id)
{
$qb = $this->createQueryBuilder('a');
if ($term && $id) {
$qb->andWhere('a.content LIKE :term OR a.title LIKE :term OR a.tag = :id')
->setParameters(array('term' => '%' . $term . '%', 'id' => $id))
->orderBy('a.date', 'DESC');
;
}elseif($term && !($id)){
$qb->andWhere('a.content LIKE :term OR a.title LIKE :term')
->setParameter('term', '%' . $term . '%')
->orderBy('a.date', 'DESC');
;
}
return $qb;
}

And the id from controller of the corresponding tag for the :term if exist. It seems ok to me but I get the error:
[Semantical Error] line 0, col 89 near 'tag = :id ORDER': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.

Can't understand why

Reply

Hey Gballocc7

Doctrine is expecting an entity object instead of an ID. Do that change and try again :)

Cheers!

Reply

I have tried also giving it a object, check it out:

Controller:
$repositoryt = $em->getRepository(Tag::class);

$q = $request->query->get('search') ?? null;
/** @var Tag $searchedTag */
$searchedTag = $repositoryt->findOneBy(array('name' => $q));

$queryBuilder = $repository->getQueryBuilderSearchAll($q, $searchedTag);

Repository:
/**
* @param string|null $term
* @param Tag|null $tag
* @return QueryBuilder
*/
public function getQueryBuilderSearchAll(?string $term, ?Tag $tag)
{
$qb = $this->createQueryBuilder('a');
if ($term && $tag) {
$qb->andWhere('a.content LIKE :term OR a.title LIKE :term OR a.tag LIKE :tag')
->setParameters(array('term' => '%' . $term . '%', 'tag' => $tag))
->orderBy('a.date', 'DESC');
;
}elseif($term && !($tag)){
$qb->andWhere('a.content LIKE :term OR a.title LIKE :term')
->setParameter('term', '%' . $term . '%')
->orderBy('a.date', 'DESC');
;
}
return $qb;
}

I get:
[Semantical Error] line 0, col 89 near 'tag LIKE :tag': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

Either using equal or like

Reply

Ok, so the error changed and it's related to this problem: https://stackoverflow.com/a...

What do you with the QB after calling $queryBuilder = $repository->getQueryBuilderSearchAll($q, $searchedTag);?

1 Reply

I use the paginator with the query builder, but it doesn't work same error, is because i'm not using a join? How can i do that?

Reply

Hey Gballocc7

You got interesting error, I'm not quite sure, but you can't use LIKE expression with objects or ids. If you want to use ID of tag, than try OR a.tag_id = :id or if you have tag object than a.tag = :tag also you can use JOINs but it's not necessary.

Cheers!

1 Reply

Thanks but doesn't work, I have tried both with passing object or id, I still get this:
[Semantical Error] line 0, col 89 near 'tag = :tag ORDER': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.
doing:
/**
* @param string|null $term
* @param int|null $tag
* @return QueryBuilder
*/
public function getQueryBuilderSearchAll(?string $term, ?int $tag)
{
$qb = $this->createQueryBuilder('a');

$qb->andWhere('a.content LIKE :term OR a.title LIKE :term OR a.tag = :tag')
->setParameters(array('term' => '%' . $term . '%', 'tag' => $tag))
->orderBy('a.date', 'DESC');

return $qb;
}

and passing a object same, I get semantical error.

Reply

Hey Gballocc7

I think this use case is complex for doing it with Doctrine ORM, have you tried writing a raw sql? I think it will be easier. If you have problems hydrating the results what you can do is to fetch first all the matching ids and then in a second query fetch just those id objects

Cheers!

1 Reply

Nice idea! But I solved, for whom is interested:
/**
* @param string|null $term
* @param Tag|null $tag
* @return QueryBuilder
*/
public function getQueryBuilderSearchAll(?string $term, ?Tag $tag)
{
$qb = $this->createQueryBuilder('a');

$qb->leftJoin('a.tag', 'at')
->andWhere('a.content LIKE :term OR a.title LIKE :term OR at = :tag_id')
->setParameter('term', '%' . $term . '%')
->setParameter('tag_id', $tag)
->orderBy('a.date', 'DESC');
;

return $qb;
}

That's the working query, now I'm going to do a research with date intervall, type article selection, tag and text together, I'll probably use a raw query for this.

I have one question in my mind right know,how can I set a entitytype default value like: ALL in the form?

I'm using:
->add('tag', EntityType::class, [
'label' => 'Tag di Ricerca',
'class' => Tag::class,
'choices' => $this->tagRepository->findAllTagsAlphabetical(),
'choice_label' => 'name',
'by_reference' => false,

]);

But I can't find online a way to put as default ALL

Reply

That's great, you did it, congrats!

About your "ALL" option question... I'm not sure about your intention but what you can do is to add a placeholder that says "All options" and then, on submit, you check if that field is empty so you don't add any filters. I hope it makes any sense to you

Cheers!

Reply

Sorry i didn't express myself in a correct way. Check how it is:
http://i68.tinypic.com/e9x8...

The goal is to have in that entitytype field as default the value all, that means: 'I don't care about the tag search no matter what's the tag'.

How can I do that?

Reply

Ok, so, I still believe by adding a placeholder and making the field optional you can accomplish it. Otherwise I'm not sure what you can do, probably add a "All" tag record into the database, and explicitly look for it when handling your form

1 Reply

Lovely your first answer is perfect and it's what I'm trying to do. How can I do? I load from the datebase the values , can't find an option to set the default value empty

Reply

You mean the "placeholder" way, right? If that's the case you only have to set up the "placeholder" option :)
Here is an example: https://symfony.com/doc/cur...
Just don't forget to make that field optional

Cheers!

Reply

Could you please check what exact type of $tag variable. I think I got it, you have Many-To-Many relation here? so you need leftJoin for search, something like

->leftJoin('a.tag', 'at')->andWhere('a.content LIKE :term OR a.title LIKE :term OR at.id = :tag_id')

not sure about example, but something like this.

Cheers!

1 Reply

I use the paginator:

/**@var Tags $tags*/
$tags = $repositoryt->findAllTagsAlphabetical();

$pagination = $paginator->paginate(
$queryBuilder, /* query NOT result */
$request->query->getInt('page', 1), /*page number*/
5 /*limit per page*/
);

return $this->render('articles/allarticles.twig', [
'title' => 'Ricerca',
'articles' => $pagination,
'tags' => $tags
]);

Reply
rumentab Avatar
rumentab Avatar rumentab | posted 3 years ago

Hello all,
I have the same problem like cybernet2u's
My situation is as follows: I have an Offer entity with the following relations
Offer -> Country - ManyToOne
Offer->City - ManyToOne
Offer->Category - ManyToMany

I also have a relation ManyToMany between the Country and City entities.

I have a search form created with the form builder. I made the city field dependant on the selected country value. If i mark the category element in the FormType as 'mapped' => false, everything with the dependant fields works ok but when I submit the form, the form data for the category field is null as exected.
When I remove the mapped => false part for the category element in the FormType, when I change the country the ajax call is submitted but the result gives me the same exception as the one cybernet2u's got.

Could not determine access type for property "category" in class "App\Entity\Offer": The property "category" in class "App\Entity\Offer" can be defined with the methods "addCategory()", "removeCategory()" but the new value must be an array or an instance of \Traversable, "NULL" given.


I have both the methods in the Offer entity (all entities I've made with the make:entity)

Any ideas how to fix?
Thanks in addvance.

Reply

Hey rumentab

It's really difficult to say something without code example. Sounds like your form tries to save NULL value to "category" field for "Offer" entity. There could be several solutions, probably form field definition cannot process ManyToMany relation, or it's misconfigured. Or you should create a DataTransformer for it. You can check how it's working here https://symfonycasts.com/sc... or look in Symfony Docs.

Cheers!

Reply
rumentab Avatar

Thanks Vladimir.

Data Transformer sounds like an option. My situation is a little bit different. I have an offer search form on the main page and all it's fields are not obligatory. That's why Ajax call submits null when no category is selected. I made it like I said in the post marking all the fields as mapped false in the Form Type and take what I need in the controller from the Request object.
But I will try with the data transformation too.

Thanks a lot and cheers!

Reply
Jeffrey C. Avatar
Jeffrey C. Avatar Jeffrey C. | posted 3 years ago

Hey,

When i try to migrate i get the following error which is weird cause there shouldn't go anything wrong.
"1091 Can't DROP 'slug'; check that column/key exists"

The following code is what is in my migration file and i dont see anything wrong with it.


public function up(Schema $schema) : void
{
// this up() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

$this->addSql('CREATE TABLE article_tag (article_id INT NOT NULL, tag_id INT NOT NULL, INDEX IDX_919694F97294869C (article_id), INDEX IDX_919694F9BAD26311 (tag_id), PRIMARY KEY(article_id, tag_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
$this->addSql('ALTER TABLE article_tag ADD CONSTRAINT FK_919694F97294869C FOREIGN KEY (article_id) REFERENCES article (id) ON DELETE CASCADE');
$this->addSql('ALTER TABLE article_tag ADD CONSTRAINT FK_919694F9BAD26311 FOREIGN KEY (tag_id) REFERENCES tag (id) ON DELETE CASCADE');
$this->addSql('DROP INDEX slug ON tag');
$this->addSql('DROP INDEX slug_2 ON tag');
$this->addSql('ALTER TABLE tag CHANGE slug slug VARCHAR(255) NOT NULL');
}

public function down(Schema $schema) : void
{
// this down() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

$this->addSql('DROP TABLE article_tag');
$this->addSql('ALTER TABLE tag CHANGE slug slug VARCHAR(191) NOT NULL COLLATE utf8mb4_unicode_ci');
$this->addSql('CREATE UNIQUE INDEX slug ON tag (slug)');
$this->addSql('CREATE INDEX slug_2 ON tag (slug)');
}

Reply

Hey Jeffrey C.

This line is the problem $this->addSql('DROP INDEX slug ON tag'); Are you sure the "slug" INDEX exists on the Tag table?
I believe what you want to drop is the field "slug", so you would need to add a SQL code like this:


ALTER TABLE your_table
DROP COLUMN column_name

Cheers!

Reply
Jeffrey C. Avatar

*UPDATE*
I have found the problem. I don't know why but apperently the index was UNIQ_389B783989D9B62 instead of slug, that was the reason why i gave an error :)

Cheers!

Reply

Cool, I'm glad to know that you could fix your problem :)

Reply
Jeffrey C. Avatar

Hey Diego,

I do have a column slug and when i try to run your code i get the same error :(

Cheers!

Reply
Default user avatar
Default user avatar Larry Lu | posted 3 years ago

This video explains better than the one in the Doctrine document. Their examples are terrible and the explanation does not make sense:
https://www.doctrine-projec....

Though there's are one thing here I see here that doesn't get explained. Why is there a $article->addTag() call in the addArticle method of the Tag entity but not vise versa in the Article entity?

Reply

Hey Larry,

Thank you for your feedback! We're glad you understand our explanation better, though it's sad official docs do it bad, probably someone will create a PR to tweak it and make it more understandable for newcomers. Maybe you're that guy who can suggest some improvements in those docs. It's always tricky to write a good docs, many edge cases, but it always open for improvements.

About your question, it's all about "owning vs inverse sides", you always have an owning and an inverse sides, we explain it here: https://symfonycasts.com/sc... . So basically, because of Tag entity is an inverse side of Article-Tag relation, you need to call that extra "$article->addTag()". In other words, Doctrine just ignores any changes on inverse side.

I hope it helps!

Cheers!

Reply

Oh, and to add one more thing! It's not important (for Doctrine) to set the "inverse" side... but we *could* still do this technically, just to keep all our code "in sync". The reason we don't do this (well, technically, the reason that MakerBundle does not do this) is that calling $tag->addArticle() would cause Doctrine to query for all of the Articles for this Tag so that it could then add the Article to that collection. In other words, "synchronizing" the inverse side could have some performance impacts - so we avoid it.

Cheers!

1 Reply
Cat in space

"Houston: no signs of life"
Start the conversation!

The course is built on Symfony 4, but the principles still apply perfectly to Symfony 5 - not a lot has changed in the world of relations!

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": "^7.1.3",
        "ext-iconv": "*",
        "composer/package-versions-deprecated": "^1.11", // 1.11.99
        "knplabs/knp-markdown-bundle": "^1.7", // 1.7.0
        "knplabs/knp-paginator-bundle": "^2.7", // v2.7.2
        "knplabs/knp-time-bundle": "^1.8", // 1.8.0
        "nexylan/slack-bundle": "^2.0,<2.2.0", // v2.0.0
        "php-http/guzzle6-adapter": "^1.1", // v1.1.1
        "sensio/framework-extra-bundle": "^5.1", // v5.1.4
        "stof/doctrine-extensions-bundle": "^1.3", // v1.3.0
        "symfony/asset": "^4.0", // v4.0.4
        "symfony/console": "^4.0", // v4.0.14
        "symfony/flex": "^1.0", // v1.17.6
        "symfony/framework-bundle": "^4.0", // v4.0.14
        "symfony/lts": "^4@dev", // dev-master
        "symfony/orm-pack": "^1.0", // v1.0.6
        "symfony/twig-bundle": "^4.0", // v4.0.4
        "symfony/web-server-bundle": "^4.0", // v4.0.4
        "symfony/yaml": "^4.0", // v4.0.14
        "twig/extensions": "^1.5" // v1.5.1
    },
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.0", // 3.0.2
        "easycorp/easy-log-handler": "^1.0.2", // v1.0.4
        "fzaninotto/faker": "^1.7", // v1.7.1
        "symfony/debug-bundle": "^3.3|^4.0", // v4.0.4
        "symfony/dotenv": "^4.0", // v4.0.14
        "symfony/maker-bundle": "^1.0", // v1.4.0
        "symfony/monolog-bundle": "^3.0", // v3.1.2
        "symfony/phpunit-bridge": "^3.3|^4.0", // v4.0.4
        "symfony/profiler-pack": "^1.0", // v1.0.3
        "symfony/var-dumper": "^3.3|^4.0" // v4.0.4
    }
}