CODEWITHSUNDEEP
collectionsdoctrine-ormwhere-clause
mike
mike

Reputation: 392

doctrine 2 select member of using collection

I have two entities with oneToMany and manyToOne relations

Article has oneToMany tags

ArticleTag has manyToOne article

$articleTags = $em->getRepository('Model\ArticleTag')
    ->findBy(array('article' => $articleId));

$qb->select('a')
    ->from('\\Model\\Article', 'a')
    ->where(':tags MEMBER OF a.tags')
    ->setParameter('tags', $articleTags);

that query return error:

An exception occurred while executing
    SELECT .. FROM article a0_ WHERE EXISTS (SELECT 1 FROM article_tag a1_ WHERE a0_.id = a1_.article_id AND a1_.id = ?, ?, ?)' with params {"1":8,"2":9,"3":10}
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 9, 10)' at line 1

It there any way for using 'in' instead '=' in expression 'a1_.id = ?, ?, ?'

Upvotes: 4

Views: 6467

Answers (2)

lordjancso
lordjancso

Reputation: 145

This question is old, but here is an answer:

You can use IN if you use array as input, like this:

...  
->where( 'a.id IN (:tag_ids)' )  
->setParameter( 'tag_ids', $articleTags )  
...

Upvotes: 4

Will B.
Will B.

Reputation: 18416

As lordjansco said, old question, but I wanted to expand upon it a bit to anyone else who found it.

To expand on lordjancso's answer, since a.id refers to the Article Id and not the Tag Ids. You would need to perform an Inner Join with an IN on a.tags to retrieve the Articles from their associated tags.

Like so.

$articleTags = $em->getRepository('Model\\ArticleTag')
   ->findBy(array('article' => $articleId));

$qb = $em->createQueryBuilder();
$query = $qb->select('a')
   ->from('\\Model\\Article', 'a')
   ->innerJoin('a.tags', 't', 'WITH', 't.id IN (:tags)')
   ->setParameter('tags', $articleTags)
   ->getQuery();
$result = $query->getResult();

However since you know the article ID already, there's no need of creating another query to retrieve the article from the tags.

If you're using Doctrine2 ORM and your entities are set up as ManyToOne you should be able to just call getTags from the article instead. 

$article = $em->getRepository('Model\\Article')->findOneById($articleId);
$articleTags = $article->getTags();

or if needed you could also iterate over each tag.

$articleTags = $em->getRepository('Model\\ArticleTag')
    ->findBy(array('article' => $articleId));

foreach ($articleTags as $articleTag) {
   $article = $articleTag->getArticle();
}

Ensure you have a Bidirectional One-To-Many association configured for your entities.

http://doctrine-orm.readthedocs.org/en/latest/reference/association-mapping.html#one-to-many-bidirectional

Like so:

use \Doctrine\Common\Collections\ArrayCollection;

/** @Entity **/
class Article
{
    /**
     * @var ArrayCollection|ArticleTags[]
     * @ORM\OneToMany(targetEntity="ArticleTags", mappedBy="article")
     */
    private $tags;

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

    /**
     * @return ArticleTags[]|ArrayCollection
     */
    public function getTags()
    {
        return $this->tags;
    }
}

/** @Entity **/
class ArticleTags
{
    /**
     * @var Article
     * @ORM\ManyToOne(targetEntity="Article", inversedBy="tags")
     * @ORM\JoinColumn(name="article", referencedColumnName="id")
     */
    private $article;
}

Upvotes: 2

Related Questions