Doctrine Join Many To Many without association

Question

I have: two entities with undirectional M:M association.

class ShareInfo
{
    // ...

    /**
     * @ORM\ManyToMany(targetEntity="Item")
     * @ORM\JoinTable(name="share_info_items",
     *      joinColumns={@ORM\JoinColumn(name="share_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="item_id", referencedColumnName="id")})
     *
     * @var Item[]
     */
    private $items;
}

class Item
{
    // ...

    // This entity has no association with ShareInfo,
    // because M:M is undirectional and defined in ShareInfo entity
}

What I want: Select data from items table (Item entity), where at least one M:M record between Item and ShareInfo exists.

My suggestion which doesn't work (I've got a semantic error):

$queryBuilder
    ->select('i')
    ->from(Item::class, 'i')
    ->innerJoin(ShareInfo::class, 'shareInfo', 'WITH', 'shareInfo.items = i');

In pure SQL I'd do something like this:

SELECT i.*
FROM items i
INNER JOIN share_info_items shareInfo
    ON shareInfo.item_id = i.id

Can't believe there is no DQL analog for this. The only solution I can imagine is to split undirectional M:M association into bi-directional

P.S. This question has no duplicates, I checked well.

Patio

Have you found a solution to this problem? – Succussion

@Succussion I don't remember to be honest. But now I'd use a MtM relationship with an intermediate table. See #15616657 Looks like there is no solution for one-directional association. – Patio

Answer 1

Late to the party but I was dealing with the same problem and found a working solution. You need to add ShareInfo::class using alias, join the ShareInfo::class items using this alias and then filter the Item::class ids using where():

$queryBuilder
    ->select('i')
    ->from(Item::class, 'i')
    ->from(ShareInfo::class, 'shareInfo')
    ->innerJoin('shareInfo.items', 'items')
    ->where('items.id = i.id');

Meemeece

Answer 2

The way to achieve this is through a subquery:

$em=$this->getDoctrine()->getManager();
$queryBuilder1=$em->createQueryBuilder();
$queryBuilder1->select(array('DISTINCT i.id'))
          ->from('AppBundle:ShareInfo', 'share_info')
          ->innerJoin('share_info.items', 'i');
$queryBuilder=$em->createQueryBuilder();
$queryBuilder->select('i')
         ->from('AppBundle:items', 'i')
         ->where($queryBuilder->expr()
         ->in('i.id',$queryBuilder1->getDql()));

Flameproof