Doctrine2 in Symfony3 ManyToMany association query

Question

Hello and Welcome Stackoverflowers? Flowers?! Nevermind:)

Some time ago I started to learn intensively Symfony3 and up to now the questions you asked here were sufficient to me. But today the day has come and I personally have a question.

So let's sketch out the situation:

I'm working over a simple tagging functionality for my small CMS. I would like my articles(and maybe later other content too) to contain some tags I attach to them. In order to do that I created with Doctrine a set of two objects: Article & Tag (some code on them below):

Article model

  namespace AppBundle\Entity;

  use Doctrine\ORM\Mapping as ORM;
  use Doctrine\Common\Collections\ArrayCollection;

/**
 * Article
 *
 * @ORM\Table(name="article")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\ArticleRepository")
 */

class Article extends ContentItem
{
/**
 * @var string
 *
 * @ORM\Column(name="content", type="text")
 */
private $content;

/**
 *
 * @ORM\ManyToOne(targetEntity="ContentTypeDict", inversedBy="articles")
 * @ORM\JoinColumn(name="type_id", referencedColumnName="id", nullable=false)
 */
private $type;

/**
 * @ORM\ManyToMany(targetEntity="Tag", inversedBy="articles")
 * @ORM\JoinTable(name="tags_of_articles")
 */

protected $tags;

public function __construct()
{

    $this->tags = new ArrayCollection();

}
/**
 * Get id
 *
 * @return int
 */
public function getId()
{
    return $this->id;
}

/**
 * Set type
 *
 * @param boolean $type
 *
 * @return Article
 */
public function setType($type)
{
    $this->type = $type;

    return $this;
}

/**
 * Get type
 *
 * @return bool
 */
public function getType()
{
    return $this->type;
}

/**
 * Set content
 *
 * @param string $content
 *
 * @return Article
 */
public function setContent($content)
{
    $this->content = $content;

    return $this;
}

/**
 * Get content
 *
 * @return string
 */
public function getContent()
{
    return $this->content;
}

/**
 * Add tag
 *
 * @param \AppBundle\Entity\tag $tag
 *
 * @return Article
 */
public function addTag(\AppBundle\Entity\tag $tag)
{
    $this->tags[] = $tag;

    return $this;
}

/**
 * Remove tag
 *
 * @param \AppBundle\Entity\tag $tag
 */
public function removeTag(\AppBundle\Entity\tag $tag)
{
    $this->tags->removeElement($tag);
}

/**
 * Get tags
 *
 * @return \Doctrine\Common\Collections\Collection
 */
public function getTags()
{
    return $this->tags;
}
}

Tag model

namespace AppBundle\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\ORM\Mapping as ORM;

/**
 * Tag
 *
 * @ORM\Table(name="tag")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\TagRepository")
 */
class Tag
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255, unique=true)
     */
    private $name;

    /**
     * @var int
     *
     * @ORM\Column(name="popularity", type="integer")
     */
    private $popularity;

    /**
     *
     * @ORM\ManyToMany(targetEntity="Article", mappedBy="tags")
     */
    private $articles;

    /**
     *
     * @ORM\ManyToMany(targetEntity="Asset", inversedBy="tags")
     * @ORM\JoinTable(name="assets_tags")
     */
    private $assets;


    public function __construct()
    {
        $this->articles = new ArrayCollection();
        $this->assets   = new ArrayCollection();
    }

    /**
     * Get id
     *
     * @return int
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set name
     *
     * @param string $name
     *
     * @return Tag
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * Set popularity
     *
     * @param integer $popularity
     *
     * @return Tag
     */
    public function setPopularity($popularity)
    {
        $this->popularity = $popularity;

        return $this;
    }

    /**
     * Get popularity
     *
     * @return int
     */
    public function getPopularity()
    {
        return $this->popularity;
    }

    /**
     * Add content
     *
     * @param \AppBundle\Entity\ContentItem $content
     *
     * @return Tag
     */
    public function addContent(\AppBundle\Entity\ContentItem $content)
    {
        $this->content[] = $content;

        return $this;
    }

    /**
     * Remove content
     *
     * @param \AppBundle\Entity\ContentItem $content
     */
    public function removeContent(\AppBundle\Entity\ContentItem $content)
    {
        $this->content->removeElement($content);
    }

    /**
     * Get content
     *
     * @return \Doctrine\Common\Collections\Collection
     */
    public function getContent()
    {
        return $this->content;
    }

    /**
     * Add article
     *
     * @param \AppBundle\Entity\ContentItem $article
     *
     * @return Tag
 */
public function addArticle(\AppBundle\Entity\ContentItem $article)
{
    $this->articles[] = $article;

    return $this;
}

/**
 * Remove article
 *
 * @param \AppBundle\Entity\ContentItem $article
 */
public function removeArticle(\AppBundle\Entity\ContentItem $article)
{
    $this->articles->removeElement($article);
}

/**
 * Get articles
 *
 * @return \Doctrine\Common\Collections\Collection
 */
public function getArticles()
{
    return $this->articles;
}

/**
 * Add asset
 *
 * @param \AppBundle\Entity\ContentItem $asset
 *
 * @return Tag
 */
public function addAsset(\AppBundle\Entity\ContentItem $asset)
{
    $this->assets[] = $asset;

    return $this;
}

/**
 * Remove asset
 *
 * @param \AppBundle\Entity\ContentItem $asset
 */
public function removeAsset(\AppBundle\Entity\ContentItem $asset)
{
    $this->assets->removeElement($asset);
}

/**
 * Get assets
 *
 * @return \Doctrine\Common\Collections\Collection
 */
public function getAssets()
{
    return $this->assets;
}
}

These objects are related as you can see with an association ManyToMany with a table named 'tags_of_articles'

Now with these two object I would like to be able to get an article by providing a tag or (maybe later) a bunch of tags.

For doing that I wrote a short function in an ArticleController

 /**
 * @Route("/showArticlesByTag/{tag}")
 */
public function showArticlesByTag($tag)
{
    $em = $this->getDoctrine()->getManager();
    $tagRepo = $em->getRepository('AppBundle:Tag');
    $tagData = $tagRepo->findOneBy(array('name' => $tag,));
    $dataRepo = $em->getRepository('AppBundle:Article');
    $data = $dataRepo->findOneBy(array('tags' => $tagData->getId(),));

    var_dump($data);
    //return $this->render('AppBundle:Article:show_article.html.twig', array( ));
}

And as a result of execution of, let's say

localhost/~user/MyCMS/web/app_dev.php/showArticlesByTag/Architecto

(a tag Architecto exists in a tag table)

I get an exception:

An exception occurred while executing 'SELECT t0.content AS content_1, t0.id AS id_2, t0.title AS title_3, t0.description AS description_4, t0.enabled AS enabled_5, t0.type_id AS type_id_6 FROM article t0 WHERE tags_of_articles.tag_id = ? LIMIT 1' with params [3]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tags_of_articles.tag_id' in 'where clause'

At the ending I attach the table structures I have in SQL from doctrine:

- --------------------------------------------------------

--
-- Table structure for table `article`
--

CREATE TABLE IF NOT EXISTS `article` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `type_id` int(11) NOT NULL,
 `content` longtext COLLATE utf8_unicode_ci NOT NULL,
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `description` longtext COLLATE utf8_unicode_ci NOT NULL,
 `enabled` tinyint(1) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `IDX_23A0E66C54C8C93` (`type_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Table structure for table `tag`
--

CREATE TABLE IF NOT EXISTS `tag` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `popularity` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQ_389B7835E237E06` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

-- --------------------------------------------------------

--
-- Table structure for table `tags_of_articles`
--

CREATE TABLE IF NOT EXISTS `tags_of_articles` (
  `tag_id` int(11) NOT NULL,
  `article_id` int(11) NOT NULL,
  KEY `IDX_D429AC71BAD26311` (`tag_id`),
  KEY `IDX_D429AC717294869C` (`article_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

And now the question is: What do I do wrong? Or maybe, what have I missed here?

A suggestion if you are just learning about Doctrine, I recommend not using `JoinTable`, doctrine will make a table by itself, and you don't need to worry about how its working. Same for `name` on the `Table` and `Column` annotations. — mcfedr, Mar 24 '16 at 12:31
Here is the same problem with other solutions : http://stackoverflow.com/questions/22872876/doctrine-manytomany-query#22872985 — freezix, Oct 07 '16 at 13:19

score 0 · Answer 1 · answered Mar 24 '16 at 09:21

There may be other ways to achieve that, but I would do it by adding a method findByTags to your ArticleRepository:

Add a method to your ArticleRepository like this (you don't have a custom repository? Check this: Custom Repository Classes):

class ArticleRepository extends EntityRepository
{
    // ...

    public function findyByTags(array $tags)
    {
        $qb = $this->createQueryBuilder('a');
        $qb->join('a.tags', 't');

        for($i = 0; $i < count($tags); $i++) {
            $qb->andWhere($qb->expr()->eq('t.name', ':tag' . $i));
            $qb->setParameter('tag' . $i, $tags[$i]);
        }
        return $qb->getQuery()->getResult();
    }
}

In your controller you can use it like this:

public function showArticlesByTag($tag)
{
    $dataRepo = $this->getDoctrine()->getRepository('AppBundle:Article');
    $data = $dataRepo->findByTags(array($tag));

    // ...
}

If you pass multiple tags to this method an article must have all of them to be returned. There could be errors in this code, because I could not test it of course. But I hope it gets my point across. ;-)

score 0 · Answer 2 · answered Mar 24 '16 at 20:14

0

I seems that you need to change the line

$data = $dataRepo->findOneBy(array('tags' => $tagData->getId(),));

as following:

$data = $dataRepo->findBy(array('tag' => $tagData));

answered Mar 24 '16 at 20:14

Iaroslav Gashuk

191
6

Doctrine2 in Symfony3 ManyToMany association query

2 Answers2