ManyToMany Relationship

any idea howto fix this ?
<br />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.<br />

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;

}
`

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!

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.

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!

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.

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/screencast/collections/by-reference-adder-remover#the-by-reference-form-option

Let me know if that helps :).

Cheers!

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

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!

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?

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!

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

<b>Code</b> (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())
            ;
    }```


<b>Problem:</b>
This code results in a:
<b>[Semantical Error] line 0, col 70 near 'categories IN': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected. 
</b>
$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?

<b>UPDATE://</b>
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.

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

<b>Unsuccessful code:
</b>`

$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.

<b>Successful code:
</b>
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);`

<b>Final questions:</b>
1.) <b>But why does the new code work and the old doesn't?</b>
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)))
<b>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)</b>

<b><u>UPDATE3://</u></b>
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 :)

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!

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

Hey Gballocc7

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

Cheers!

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

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

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

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?

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!

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.

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!

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

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!

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?

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

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

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!

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
<br />->leftJoin('a.tag', 'at')->andWhere('a.content LIKE :term OR a.title LIKE :term OR at.id = :tag_id') <br />

not sure about example, but something like this.

Cheers!

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
]);

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.

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!

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!

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)');
}

`

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!

*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!

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

Hey Diego,

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

Cheers!

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?

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!

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!