Symfony : many-to-many with where clause on child

Question

Many users can have many items. But I want to get a list of specific items owned by specifics users.

I have two entities :

User Entity :

class User
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * Many Users have many items
     * @ORM\ManyToMany(targetEntity="AppBundle\Entity\Item", fetch="EAGER")
     * @ORM\JoinTable(name="users_items",
     *      joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="item_id", referencedColumnName="id")}
     *      )
     */
    private $items;

}

Item Entity :

class Item
{
    /**
     * @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=10)
     */
    private $name;

}

Doctrine then created three tables :

items
users
users_items

I want to get all the items named "Pencil" owned by userId 11.

How can I achieve that ?

Thank you.

Answer 1

I hope that you realize that you are using an UNIdirectional many-to-many relation between User and Item. User 'knows' what items belong to him but Item does not know which users use this item. Therefore you can retrieve a single User from the database (by user-id) with a collection of items that are filtered by name. If the user-id does not exist or the user has no items that match the filter, then $user will be NULL. instead of the exception in the controller in my example you could use a if-else statement in twig to verify if user is null or not.

namespace AppBundle\Controller;

use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;

class DefaultController extends Controller
{
    /**
     * @Route("/someroute", name="some_route")
     */
    public function someAction()
    {
        $em = $this->getDoctrine()->getManager();

        // for this example two hardcoded parameters. $user might be NULL !
        $user = $em->getRepository('AppBundle:User')->getItemsWithNameForUser(1, 'Pencil');

        if(null === $user)
        {
            throw $this->createNotFoundException('This user does not have the selected items or the user does not exist');
        }

        return $this->render('default/index.html.twig', [
            'user' => $user
        ]);
    }
}

and the UserRepository:

namespace AppBundle\Repository;

class UserRepository extends \Doctrine\ORM\EntityRepository
{
    public function getItemsWithNameForUser($userId, $itemName)
    {
        return $this->getEntityManager()->createQuery(
            'SELECT u,i FROM AppBundle:User u JOIN u.items i WHERE u.id=:id AND i.name=:name'
        )   ->setParameter('id', $userId)
            ->setParameter('name', $itemName)
            ->getOneOrNullResult();
    }

}