CODEWITHSUNDEEP
phpsymfonydoctrinemany-to-many
lepix
lepix

Reputation: 4990

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

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.

Upvotes: 0

Views: 1692

Answers (2)

Frank B
Frank B

Reputation: 3697

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();
    }

}

Upvotes: 1

Nickolaus
Nickolaus

Reputation: 4835

I think it should be something like:

$items= $em->getDoctrine->createQueryBuilder()
         ->select('[items]')
         ->from(User::class, 'users')
         ->join('users.items', 'items')
         ->where('users.id = :userId')
         ->setParameter('userId', $theId)
         ->getQuery()
         ->getResult();

I am not sure this works (syntax is maybe not perfect), but theoretically it should, the problem in your case is that the owning side only knows about the relation and you want to query from the other side.

Upvotes: 0

Related Questions