Perform a join query using Doctrine Query Builder

Question

I want to not show a product in my index page if the quantity of that product equal to 1. The quantity of the product is store in the table Quantity. The table quantity is link to product (ManyToOne).

This is what i have done.

 public function product($id)
    {
        $query = $this->createQueryBuilder('e')
            ->leftJoin('e.quantity', 'r')
            ->where('r.quantity = :id')
            ->setParameter('id', $id)
            ->getQuery();

        return $query->getResult();
    }

Quantity

 /**
 * @var integer
 *
 * @ORM\Column(name="id", type="integer", nullable=false)
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
    protected $id;

    /**
     * @ORM\ManyToOne(targetEntity="Post", cascade={"remove"})
     * @ORM\JoinColumn(name="product_id", referencedColumnName="id")
     */
    protected $desk2;

    /**
     * @var integer
     * @ORM\Column(name="quantity", type="integer", nullable=true)
     */
    protected $quantity;

Product

/**
 * @var integer
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */

private $id;
/**
 * @var string
 *
 * @ORM\Column(name="telephone", type="string", length=30)
 */
private $telephone;

/**
 * @var string
 *
 * @ORM\Column(name="name", type="string", length=125)
 */
private $name;

/**
 * @var string
 *
 * @ORM\Column(name="description", type="text" , length=125)
 */
private $description;

index

 public function indexvAction($id)
    {

        $session = $this->getRequest()->getSession();
        $em = $this->getDoctrine()->getManager();
        $findEntities = $em->getRepository('FLYBookingsBundle:Post')->product($id);
        $entity = $this->get('knp_paginator')->paginate($findEntities, $this->get('request')->query->get('page', 1), 9
        );
        if ($session->has('cart'))
            $cart = $session->get('cart');
        else
            $cart = false;
        return $this->render('FLYBookingsBundle:Post:indexv.html.twig', array('entity' => $entity,
            'cart' => $cart));
    }

EDIT:

default: index.html.twig

{% extends "::base.html.twig" %}
        {% block newadvert %}
                <div class="span4 offset1">
                </div>
                {{ render(controller('FLYBookingsBundle:Post:indexv')) }}
        {% endblock newadvert %}

controller:

  public function indexAction()
  {

    return $this->render('FLYPlatformBundle:Default:index.html.twig');


  }

Answer 1

This will only return products with quantities > 1 (or whatever quantity parameter you pass).

 public function product($quantityLimit)
    {
        $query = $this->createQueryBuilder('e')
            ->join('e.quantity', 'r')
            ->addSelect('r')
            ->where('r.quantity > :quantityLimit')
            ->setParameter('quantityLimit', $quantityLimit)
            ->getQuery();

        return $query->getResult();
    }

If you wanted to return all products, but only quantities where they are > 1 you would use a leftJoin like so.

 public function product($quantityLimit)
    {
        $query = $this->createQueryBuilder('e')
            ->leftJoin('e.quantity', 'r', 'WITH', 'r.quantity > :quantityLimit')
            ->addSelect('r')
            ->setParameter('quantityLimit', $quantityLimit)
            ->getQuery();

        return $query->getResult();
    }