Comparison support for many-to-many associations

[foaly-nr1]

I believe that since https://github.com/doctrine/doctrine2/pull/885, Criteria supports many-to-many associations (@bakura10, @beberlei, @teohhanhui). I created two entities and test queries in a symfony standard edition branch to illustrate this:

/**
 * @ORM\Entity
 */
class Post
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\ManyToMany(targetEntity="Tag")
     */
    private $tags;
}

/**
 * @ORM\Entity
 */
class Tag
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;
}

However, there seem to be several problems:

// Throws exception:
// Cannot match on AppBundle\Entity\Post::tags with a non-object value. Matching objects by id is not compatible with matching on an in-memory collection, which compares objects by reference.
$criteria
    ->where($criteria->expr()->contains('tags', $tag->getId()))
;

// Throws exception:
// Object of class AppBundle\Entity\Tag could not be converted to string
$criteria
    ->where($criteria->expr()->contains('tags', $tag))
;

I added a method __toString() returning the id to see what happens. Now we're closer to the objective, but the SQL is missing a join:

SELECT COUNT(*)
FROM post t0
WHERE post_tag.tag_id LIKE '%1%';

Desired query:

SELECT COUNT(*)
FROM post t0
JOIN post_tag ON post_tag.post_id = t0.id
WHERE post_tag.tag_id = 1;

This problem was previously reported in https://github.com/doctrine/doctrine2/issues/5951 (@Michal-sk).

[mlocati]

@foaly-nr1 did you find a workaround for this issue?

[foaly-nr1]

@mlocati you can use a DQL query as a workaround.

[mlocati]

@foaly-nr1 I have a few problems writing the DQL...

$entityManager->createQuery(<<<'EOT'
    SELECT t0
    FROM Post t0
    INNER JOIN Tag t1 WITH ?????
    WHERE t1.id = :tagID
EOT
)->execute(['tagID' => 1]);

What should I write instead of ????? ? Any hint?

[mlocati]

I found the solution:

$entityManager->createQuery(<<<'EOT'
    SELECT t0
    FROM Post t0
    INNER JOIN Post.tags t1
    WHERE t1.id = :tagID
EOT
)->execute(['tagID' => 1]);