many to many relationship querybuilder doctrine and symfony2 extended query

Question

I am applying following query using querybuilder but somehow many2many relationship is not working as expected.

$shopData = $sm->createQueryBuilder()                    
                ->select('v')
                ->from('AdminBundle:Voucher','v')
                ->innerJoin('v.shop', 's')
                ->leftJoin('AdminBundle:VoucherProgram', 'vp', \Doctrine\ORM\Query\Expr\Join::ON, 'vp.id = v.program_id')
                ->leftJoin('AdminBundle:shopHistory', 'sh', \Doctrine\ORM\Query\Expr\Join::ON, 'sh.shop = s.id')
                ->where('s.shopStatus = :shopStatus')
                ->setParameter('shopStatus', Shop::SHOP_ACTIVATED)
                ->andWhere('s.highlightedHome = :highlightedHome')
                ->setParameter('highlightedHome', Shop::SHOP_HIGHLIGHTED_HOME)
                ->andWhere('s.offers = \'voucher\'')
                ->setFirstResult(0)
                ->setMaxResults(6)
                ->addOrderBy('v.discount_amount', 'DESC')
                ->groupBy('sh.shop')
                ->getQuery()
                ->getSql();

Generated query looks like below:

SELECT v FROM AdminBundle:Voucher v INNER JOIN v.shop s LEFT JOIN AdminBundle:VoucherPrograms vp ON vp.id = v.program_id LEFT JOIN AdminBundle:shopHistory sh ON sh.shop = s.id WHERE s.shopStatus = :shopStatus AND s.highlightedHome = :highlightedHome AND s.offers = 'voucher' GROUP BY sh.shop ORDER BY v.discount_amount DESC

In case if I remove everything and keep only inner join for Many2Many relation, it is working as expected.

$sm->createQueryBuilder()                    
                ->select('v')
                ->from('AdminBundle:Voucher','v')
                ->innerJoin('v.shop', 's');

Here is the generated query:

SELECT l0.* FROM voucher l0_ INNER JOIN shop_voucher l2_ ON l0_.id = l2_.voucher_id INNER JOIN shop l1_ ON l1_.id = l2_.shop_id;

So I am wondering why system is not picking up correct relation when I add more joins.

Here is my main entities having Many2Many relationship:

Shop.php

namespace AdminBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * Shop.
 *
 * @ORM\Table(name="shop")
 * @ORM\Entity(repositoryClass="AdminBundle\Entity\ShopRepository")
 */
class Shop
{
    const SHOP_DEACTIVATED = 0;
    const SHOP_ACTIVATED = 1;
    const SHOP_HIGHLIGHTED_HOME = 1;
    ................................
    /**
     * @ORM\ManyToMany(targetEntity="Voucher", inversedBy="shop")
     * @ORM\JoinTable(name="shop_voucher")
     */
    private $voucher;
    ................................

Voucher.php

namespace AdminBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
#use Doctrine\Common\Collections\ArrayCollection;

/**
 * Voucher.
 *
 * @ORM\Table(name="voucher")
 * @ORM\Entity(repositoryClass="AdminBundle\Entity\VoucherRepository")
 */
class Voucher
{
    ................................
    /**
     * @ORM\ManyToMany(targetEntity="Shop", mappedBy="voucher", cascade={"persist"})    
     */
    private $shop;
    ................................

I have already checked on stack having same questions but I want to know how we can extend query. I have the solution for my question as below but not getting exactly what is happening in above scenario.

$shopDataQuery = $connection->prepare('SELECT v.* FROM voucher AS v INNER JOIN shop_voucher AS sv ON sv.voucher_id = v.id INNER JOIN shop AS s ON s.id = sv.shop_id LEFT JOIN voucher_programs AS vp ON vp.id = v.program_id LEFT JOIN shop_history AS sh ON sh.shop = s.id WHERE s.shopStatus = :shopStatus AND s.highlightedHome = :highlightedHome AND s.offers = 'voucher' GROUP BY sh.shop ORDER BY v.discount_amount DESC LIMIT 6');

---

Update:

Here is the shopHistory.php

namespace AdminBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * shopHistory.
 *
 * @ORM\Table(name="shop_history")
 * @ORM\Entity(repositoryClass="AdminBundle\Entity\shopHistoryRepository")
 */
class shopHistory
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var int
     * @ORM\ManyToOne(targetEntity="Shop", inversedBy="shopHistory")
     * @ORM\JoinColumn(name="shop", referencedColumnName="id")
     */
    private $shop;

Here is VoucherProgram.php

namespace AdminBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * VoucherProgram.
 *
 * @ORM\Table(name="voucher_program")
 * @ORM\Entity(repositoryClass="AdminBundle\Entity\VoucherProgramRepository")
 */
class VoucherProgram
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\OneToMany(targetEntity="Voucher", mappedBy="program")
     */
    private $voucher;

    /**
     * @ORM\OneToMany(targetEntity="Shop", mappedBy="vprogram")
     */
    private $shop;

Answer 1

I think this is because you have named your relation as same as your field :

SELECT v 
FROM AdminBundle:Voucher v 
INNER JOIN v.shop s 
LEFT JOIN AdminBundle:VoucherPrograms vp ON vp.id = v.program_id 
LEFT JOIN AdminBundle:shopHistory sh ON sh.shop = s.id 
WHERE s.shopStatus = :shopStatus 
AND s.highlightedHome = :highlightedHome 
AND s.offers = 'voucher' 
GROUP BY sh.shop 
ORDER BY v.discount_amount DESC

Try to rename in your shopHistory Entity the field shop as shop_id.

But I don't have the full entity model to make tests !

If this isn't it, try to paste the code of your 5 entities, please...

Edit :

I make tests on my local machine with a symfony 2.8 edition.

I have generated entities form given model.

I have just added this in Voucher Entity :

/**
 * @ORM\ManyToOne(targetEntity="voucherProgram", inversedBy="voucher")
 */
private $program;

I've this queries :

My first Query

SELECT v 
FROM AppBundle:Voucher v 
INNER JOIN v.shop s 
LEFT JOIN AppBundle:voucherProgram vp WITH vp.id = v.program 
LEFT JOIN AppBundle:shopHistory sh 
WITH sh.shop = s.id 
GROUP BY sh.shop

My second SQL Query

SELECT v0_.id AS id0, v0_.program_id AS program_id1 
FROM voucher v0_ 
INNER JOIN shop_voucher s2_ ON v0_.id = s2_.voucher_id 
INNER JOIN shop s1_ ON s1_.id = s2_.shop_id 
LEFT JOIN voucher_program v3_ ON (v3_.id = v0_.program_id) 
LEFT JOIN shop_history s4_ ON (s4_.shop = s1_.id) 
GROUP BY s4_.shop 
LIMIT 6 
OFFSET 0

I think that what is generated is correct !

Edit 2 :

I try with a symfony standard edition with :

$shopData = $this->getDoctrine()
  ->getManager()
  ->createQueryBuilder()                    
  ->select('v')
  ->from('AppBundle:Voucher','v')
  ->innerJoin('v.shop', 's')
  ->leftJoin('AppBundle:voucherProgram', 'vp', 'WITH', 'vp.id = v.program')
  ->leftJoin('AppBundle:shopHistory', 'sh', 'WITH', 'sh.shop = s.id')
  //->where('s.shopStatus = :shopStatus')
  //->setParameter('shopStatus', Shop::SHOP_ACTIVATED)
  //->andWhere('s.highlightedHome = :highlightedHome')
  //->setParameter('highlightedHome', Shop::SHOP_HIGHLIGHTED_HOME)
  //->andWhere('s.offers = \'voucher\'')
  ->setFirstResult(0)
  ->setMaxResults(6)
  //->addOrderBy('v.discount_amount', 'DESC')
  ->groupBy('sh.shop')
  ->getQuery()
  ->getDql();

My composer.json is :

"php": ">=5.3.9",
"symfony/symfony": "2.8.*",
"doctrine/orm": "^2.4.8",
"doctrine/doctrine-bundle": "~1.4",
"symfony/swiftmailer-bundle": "~2.3",
"symfony/monolog-bundle": "~2.4",
"sensio/distribution-bundle": "~5.0",
"sensio/framework-extra-bundle": "^3.0.2",
"incenteev/composer-parameter-handler": "~2.0"

And my Entities :

Shop

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Shop
 *
 * @ORM\Table(name="shop")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\ShopRepository")
 */
class Shop
{
    const SHOP_DEACTIVATED = 0;
    const SHOP_ACTIVATED = 1;
    const SHOP_HIGHLIGHTED_HOME = 1;

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


    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * @ORM\ManyToMany(targetEntity="Voucher", inversedBy="shop")
     * @ORM\JoinTable(name="shop_voucher")
     */
    private $voucher;
}

shopHistory

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * shopHistory
 *
 * @ORM\Table(name="shop_history")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\shopHistoryRepository")
 */
class shopHistory
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;


    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * @var int
     * @ORM\ManyToOne(targetEntity="Shop", inversedBy="shopHistory")
     * @ORM\JoinColumn(name="shop", referencedColumnName="id")
     */
    private $shop;
}

Voucher

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Voucher
 *
 * @ORM\Table(name="voucher")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\VoucherRepository")
 */
class Voucher
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;


    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * @ORM\ManyToMany(targetEntity="Shop", mappedBy="voucher", cascade={"persist"})    
     */
    private $shop;

    /**
     * @ORM\ManyToOne(targetEntity="voucherProgram", inversedBy="voucher")
     */
    private $program;
}

voucherProgram

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * voucherProgram
 *
 * @ORM\Table(name="voucher_program")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\voucherProgramRepository")
 */
class voucherProgram
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\OneToMany(targetEntity="Voucher", mappedBy="program")
     */
    private $voucher;

    /**
     * @ORM\OneToMany(targetEntity="Shop", mappedBy="vprogram")
     */
    private $shop;

    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }
}