Teaching explanation

Question

I am asking something about the result of the request. I don't have the same result when I use MySql and Doctrine ... I have a field that changes when it is executed (package -> id).

My query in mysql:

"SELECT * 
FROM packages_dates_prices 
INNER JOIN packages 
ON packages_dates_prices.id = packages.id 
AND packages_dates_prices.package ='.$id.'"

My query in the repository and in DQL:

"SELECT a
FROM BackBundle:PackagesDatesPrices a
INNER JOIN BackBundle:Packages b
WHERE a.id = b.id
AND a.package = :id
"

Query on the debug toolbar:

SELECT 
  e0_.id AS id0, 
  e0_.date_start AS date_start1, 
  e0_.date_end AS date_end2, 
  e0_.price AS price3, 
  e0_.id AS id4 
FROM 
  packages_dates_prices e0_ 
  INNER JOIN packages e1_ ON (
    e0_.id = e1_.id 
    AND e0_.id = ?
  )
Parameters: [1] 

Entity Packages:

<?php

namespace Back\Bundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Gedmo\Mapping\Annotation as Gedmo;

/**
 * Packages
 *
 * @ORM\Table(name="packages")
 * @ORM\Entity(repositoryClass="Back\Bundle\Entity\PackagesRepository")
 */
class Packages
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer", nullable=true)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     *
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="title", type="string", length=255, nullable=true)
     */
    private $title;

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

    /**
     * @var string
     *
     * @ORM\Column(name="details", type="string", length=255, nullable=true)
     */
    private $details;

    /**
     * @var string
     *
     * @ORM\Column(name="essential", type="string", length=255, nullable=true)
     */
    private $essential;

    /**
     * @var string
     *
     * @ORM\Column(name="indices", type="string", length=255, nullable=true)
     */
    private $indices;

    /**
     * @var string
     *
     * @ORM\Column(name="mysterious", type="string", length=255, nullable=true)
     */
    private $mysterious;

    /**
     * @var string
     *
     * @ORM\Column(name="products", type="string", length=255, nullable=true)
     */
    private $products;

    /**
     * @var string
     *
     * @ORM\Column(name="attachment", type="string", nullable=true)
     */
    private $attachment;

    /**
     * @var string
     *
     * @ORM\Column(name="meta_keywords", type="string", nullable=true)
     */
    private $metaKeywords;

    /**
     * @var string
     *
     * @ORM\Column(name="meta_description", type="string", nullable=true)
     */
    private $metaDescription;

    /**
     * @var \DateTime
     *
     * @Gedmo\Timestampable(on="create")
     * @ORM\Column(name="created_at", type="datetime", nullable=true)
     */
    private $createdAt;

    /**
     * @var string
     *
     * @ORM\Column(name="agent", type="string", length=255, nullable=true)
     */
    private $agent;

    /**
     * @var string
     *
     * @ORM\Column(name="place", type="string", length=255, nullable=true)
     */
    private $place;

    /**
     * @var string
     *
     * @ORM\Column(name="theme", type="string", length=255, nullable=true)
     */
    private $theme;

    /**
     * @var \DateTime
     *
     * @Gedmo\Timestampable(on="update")
     * @ORM\Column(name="updated_at", type="datetime", nullable=true)
     */
    private $updatedAt;

    /**
     * @var \Boolean
     *
     * @ORM\Column(name="is_active", type="boolean", nullable=true)
     */
    private $isActive;

    /**
     * @var \Boolean
     *
     * @ORM\Column(name="is_home", type="boolean", nullable=true)
     */
    private $isHome;

    /**
     * @var string
     *
     * @ORM\Column(name="link", type="string", length=255, nullable=true)
     */
    private $link;

    /**
     * @var string
     *
     * @ORM\Column(name="order_home", type="integer", nullable=true)
     */
    private $orderHome;

    /**
     * @Gedmo\Slug(fields={"title"})
     * @ORM\Column(length=128, unique=true)
     */
    private $slug;

    /**
     * @var \Boolean
     *
     * @ORM\Column(name="is_outing", type="boolean", nullable=true)
     */
    private $isOuting;

    /**
     * @var \Boolean
     *
     * @ORM\Column(name="is_weekend", type="boolean", nullable=true)
     */
    private $isWeekend;

    /**
     * @var \Boolean
     *
     * @ORM\Column(name="is_wedding", type="boolean", nullable=true)
     */
    private $isWedding;

    /**
     * @var \Boolean
     *
     * @ORM\Column(name="is_present", type="boolean", nullable=true)
     */
    private $isPresent;

    /**
     * @var \Boolean
     *
     * @ORM\Column(name="is_company", type="boolean", nullable=true)
     */
    private $isCompany;

    /**
     * @var \Boolean
     *
     * @ORM\Column(name="is_solo", type="boolean", nullable=true)
     */
    private $isSolo;

    /**
     * @var \Boolean
     *
     * @ORM\Column(name="is_couple", type="boolean", nullable=true)
     */
    private $isCouple;

    /**
     * @var \Boolean
     *
     * @ORM\Column(name="is_friends", type="boolean", nullable=true)
     */
    private $isFriends;

    /**
     * @var \Boolean
     *
     * @ORM\Column(name="is_family", type="boolean", nullable=true)
     */
    private $isFamily;

    /**
     * @var \Doctrine\Common\Collections\Collection
     * @ORM\OneToMany(targetEntity="Back\Bundle\Entity\PackagesDatesPrices", mappedBy="Package")
     **/
    private $datesPrices;


    public function __construct()
    {
        $this->createdAt = new \Datetime();
        $this->datesPrices = new \Doctrine\Common\Collections\ArrayCollection();
    }

    /**
     * @return string
     */
    public function getPeople()
    {
        return sprintf("%s %s", $this->getIsSolo(), $this->getIsCouple(), $this->getIsFriends(), $this->getIsFamily());
    }

    public function __toString()
    {
        if ($this->getId())
            return "Packages n° ".$this->getId();
        return "Nouveau Package";
    }

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

    /**
     * Set title
     *
     * @param string $title
     * @return Packages
     */
    public function setTitle($title)
    {
        $this->title = $title;

        return $this;
    }

    /**
     * Get title
     *
     * @return string 
     */
    public function getTitle()
    {
        return $this->title;
    }

    /**
     * Set description
     *
     * @param string $description
     * @return Packages
     */
    public function setDescription($description)
    {
        $this->description = $description;

        return $this;
    }

    /**
     * Get description
     *
     * @return string 
     */
    public function getDescription()
    {
        return $this->description;
    }

    /**
     * Set details
     *
     * @param string $details
     * @return packages
     */
    public function setDetails($details)
    {
        $this->details = $details;

        return $this;
    }

    /**
     * Get details
     *
     * @return string 
     */
    public function getDetails()
    {
        return $this->details;
    }

    /**
     * Set essential
     *
     * @param string $essential
     * @return packages
     */
    public function setEssential($essential)
    {
        $this->essential = $essential;

        return $this;
    }

    /**
     * Get essential
     *
     * @return string 
     */
    public function getEssential()
    {
        return $this->essential;
    }

    /**
     * Set indices
     *
     * @param string $indices
     * @return Packages
     */
    public function setIndices($indices)
    {
        $this->indices = $indices;

        return $this;
    }

    /**
     * Get indices
     *
     * @return string 
     */
    public function getIndices()
    {
        return $this->indices;
    }

    /**
     * Set mysterious
     *
     * @param string $mysterious
     * @return Packages
     */
    public function setMysterious($mysterious)
    {
        $this->mysterious = $mysterious;

        return $this;
    }

    /**
     * Get mysterious
     *
     * @return string 
     */
    public function getMysterious()
    {
        return $this->mysterious;
    }

    /**
     * Set products
     *
     * @param string $products
     * @return Packages
     */
    public function setProducts($products)
    {
        $this->products = $products;

        return $this;
    }

    /**
     * Get products
     *
     * @return string 
     */
    public function getProducts()
    {
        return $this->products;
    }

    /**
     * Set attachment
     *
     * @param string $attachment
     * @return Packages
     */
    public function setAttachment($attachment)
    {
        $this->attachment = $attachment;

        return $this;
    }

    /**
     * Get attachment
     *
     * @return string 
     */
    public function getAttachment()
    {
        return $this->attachment;
    }

    /**
     * Set metaKeywords
     *
     * @param string $metaKeywords
     * @return Packages
     */
    public function setMetaKeywords($metaKeywords)
    {
        $this->metaKeywords = $metaKeywords;

        return $this;
    }

    /**
     * Get metaKeywords
     *
     * @return string 
     */
    public function getMetaKeywords()
    {
        return $this->metaKeywords;
    }

    /**
     * Set metaDescription
     *
     * @param string $metaDescription
     * @return Packages
     */
    public function setMetaDescription($metaDescription)
    {
        $this->metaDescription = $metaDescription;

        return $this;
    }

    /**
     * Get metaDescription
     *
     * @return string 
     */
    public function getMetaDescription()
    {
        return $this->metaDescription;
    }

    /**
     * Set createdAt
     *
     * @param \DateTime $createdAt
     * @return Packages
     */
    public function setCreatedAt($createdAt)
    {
        $this->createdAt = $createdAt;

        return $this;
    }

    /**
     * Get createdAt
     *
     * @return \DateTime 
     */
    public function getCreatedAt()
    {
        return $this->createdAt;
    }

    /**
     * Set agent
     *
     * @param string $agent
     * @return Packages
     */
    public function setAgent($agent)
    {
        $this->agent = $agent;

        return $this;
    }

    /**
     * Get agent
     *
     * @return string 
     */
    public function getAgent()
    {
        return $this->agent;
    }

    /**
     * Set place
     *
     * @param string $place
     * @return Packages
     */
    public function setPlace($place)
    {
        $this->place = $place;

        return $this;
    }

    /**
     * Get place
     *
     * @return string 
     */
    public function getPlace()
    {
        return $this->place;
    }

    /**
     * Set theme
     *
     * @param string $theme
     * @return Packages
     */
    public function setTheme($theme)
    {
        $this->theme = $theme;

        return $this;
    }

    /**
     * Get theme
     *
     * @return string 
     */
    public function getTheme()
    {
        return $this->theme;
    }

    /**
     * Set updatedAt
     *
     * @param \DateTime $updatedAt
     * @return Packages
     */
    public function setUpdatedAt($updatedAt)
    {
        $this->updatedAt = $updatedAt;

        return $this;
    }

    /**
     * Get updatedAt
     *
     * @return \DateTime 
     */
    public function getUpdatedAt()
    {
        return $this->updatedAt;
    }

    /**
     * Set isActive
     *
     * @param boolean $isActive
     * @return Packages
     */
    public function setIsActive($isActive)
    {
        $this->isActive = $isActive;

        return $this;
    }

    /**
     * Get isActive
     *
     * @return boolean 
     */
    public function getIsActive()
    {
        return $this->isActive;
    }

    /**
     * Set isHome
     *
     * @param boolean $isHome
     * @return Packages
     */
    public function setIsHome($isHome)
    {
        $this->isHome = $isHome;

        return $this;
    }

    /**
     * Get isHome
     *
     * @return boolean 
     */
    public function getIsHome()
    {
        return $this->isHome;
    }

    /**
     * Set link
     *
     * @param string $link
     * @return Packages
     */
    public function setLink($link)
    {
        $this->link = $link;

        return $this;
    }

    /**
     * Get link
     *
     * @return string 
     */
    public function getLink()
    {
        return $this->link;
    }

    /**
     * Set orderHome
     *
     * @param integer $orderHome
     * @return Packages
     */
    public function setOrderHome($orderHome)
    {
        $this->orderHome = $orderHome;

        return $this;
    }

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

    /**
     * Set slug
     *
     * @param string $slug
     * @return Packages
     */
    public function setSlug($slug)
    {
        $this->slug = $slug;

        return $this;
    }

    /**
     * Get slug
     *
     * @return string 
     */
    public function getSlug()
    {
        return $this->slug;
    }

    /**
     * Set isOuting
     *
     * @param boolean $isOuting
     * @return Packages
     */
    public function setIsOuting($isOuting)
    {
        $this->isOuting = $isOuting;

        return $this;
    }

    /**
     * Get isOuting
     *
     * @return boolean 
     */
    public function getIsOuting()
    {
        return $this->isOuting;
    }

    /**
     * Set isWeekend
     *
     * @param boolean $isWeekend
     * @return Packages
     */
    public function setIsWeekend($isWeekend)
    {
        $this->isWeekend = $isWeekend;

        return $this;
    }

    /**
     * Get isWeekend
     *
     * @return boolean 
     */
    public function getIsWeekend()
    {
        return $this->isWeekend;
    }

    /**
     * Set isWedding
     *
     * @param boolean $isWedding
     * @return Packages
     */
    public function setIsWedding($isWedding)
    {
        $this->isWedding = $isWedding;

        return $this;
    }

    /**
     * Get isWedding
     *
     * @return boolean 
     */
    public function getIsWedding()
    {
        return $this->isWedding;
    }

    /**
     * Set isPresent
     *
     * @param boolean $isPresent
     * @return Packages
     */
    public function setIsPresent($isPresent)
    {
        $this->isPresent = $isPresent;

        return $this;
    }

    /**
     * Get isPresent
     *
     * @return boolean 
     */
    public function getIsPresent()
    {
        return $this->isPresent;
    }

    /**
     * Set isCompany
     *
     * @param boolean $isCompany
     * @return Packages
     */
    public function setIsCompany($isCompany)
    {
        $this->isCompany = $isCompany;

        return $this;
    }

    /**
     * Get isCompany
     *
     * @return boolean 
     */
    public function getIsCompany()
    {
        return $this->isCompany;
    }

    /**
     * Set isSolo
     *
     * @param boolean $isSolo
     * @return Packages
     */
    public function setIsSolo($isSolo)
    {
        $this->isSolo = $isSolo;

        return $this;
    }

    /**
     * Get isSolo
     *
     * @return boolean 
     */
    public function getIsSolo()
    {
        return $this->isSolo;
    }

    /**
     * Set isCouple
     *
     * @param boolean $isCouple
     * @return Packages
     */
    public function setIsCouple($isCouple)
    {
        $this->isCouple = $isCouple;

        return $this;
    }

    /**
     * Get isCouple
     *
     * @return boolean 
     */
    public function getIsCouple()
    {
        return $this->isCouple;
    }

    /**
     * Set isFriends
     *
     * @param boolean $isFriends
     * @return Packages
     */
    public function setIsFriends($isFriends)
    {
        $this->isFriends = $isFriends;

        return $this;
    }

    /**
     * Get isFriends
     *
     * @return boolean 
     */
    public function getIsFriends()
    {
        return $this->isFriends;
    }

    /**
     * Set isFamily
     *
     * @param boolean $isFamily
     * @return Packages
     */
    public function setIsFamily($isFamily)
    {
        $this->isFamily = $isFamily;

        return $this;
    }

    /**
     * Get isFamily
     *
     * @return boolean 
     */
    public function getIsFamily()
    {
        return $this->isFamily;
    }

    /**
     * Add datesPrices
     *
     * @param Back\Bundle\Entity\PackagesDatesPrices $datesPrices
     * @return Packages
     */
    public function addDatesPrice(\Back\Bundle\Entity\PackagesDatesPrices $datesPrices)
    {
        $this->datesPrices[] = $datesPrices;

        $datesPrices->setPackage($this);

        return $this;
    }

    /**
     * Remove datesPrices
     *
     * @param \Back\Bundle\Entity\PackagesDatesPrices $datesPrices
     */
    public function removeDatesPrice(\Back\Bundle\Entity\PackagesDatesPrices $datesPrices)
    {
        $this->datesPrices->removeElement($datesPrices);
    }

    /**
     * Get datesPrices
     *
     * @return \Doctrine\Common\Collections\Collection 
     */
    public function getDatesPrices()
    {
        return $this->datesPrices;
    }
}

PackagesDatesPrices:

<?php

namespace Back\Bundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Gedmo\Mapping\Annotation as Gedmo;

/**
 *
 * @ORM\Table(name="packages_dates_prices")
 * @ORM\Entity(repositoryClass="Back\Bundle\Entity\PackagesDatesPricesRepository")
 */
class PackagesDatesPrices
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer", nullable=true)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     **/
    private $id;

    /**
     * @var \Back\Bundle\Entity\Packages
     * @ORM\ManyToOne(targetEntity="Back\Bundle\Entity\Packages", inversedBy="datesPrices")
     * @ORM\JoinColumn(name="id", referencedColumnName="id")
     **/
    private $package;

    /**
     * @var \Back\Bundle\Entity\Packages
     * @var \DateTime
     *
     * @ORM\Column(name="date_start", type="datetime", nullable=true)
     */
    private $dateStart;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="date_end", type="datetime", nullable=true)
     */
    private $dateEnd;

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


    public function __construct() {
        $this->children = new \Doctrine\Common\Collections\ArrayCollection();
    }


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

    /**
     * Set dateStart
     *
     * @param \DateTime $dateStart
     * @return PackagesDatesPrices
     */
    public function setDateStart($dateStart)
    {
        $this->dateStart = $dateStart;

        return $this;
    }

    /**
     * Get dateStart
     *
     * @return \DateTime 
     */
    public function getDateStart()
    {
        return $this->dateStart;
    }

    /**
     * Set dateEnd
     *
     * @param \DateTime $dateEnd
     * @return PackagesDatesPrices
     */
    public function setDateEnd($dateEnd)
    {
        $this->dateEnd = $dateEnd;

        return $this;
    }

    /**
     * Get dateEnd
     *
     * @return \DateTime 
     */
    public function getDateEnd()
    {
        return $this->dateEnd;
    }

    /**
     * Set price
     *
     * @param integer $price
     * @return PackagesDatesPrices
     */
    public function setPrice($price)
    {
        $this->price = $price;

        return $this;
    }

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

    /**
     * Set package
     *
     * @param \Back\Bundle\Entity\Packages $package
     * @return PackagesDatesPrices
     */
    public function setPackage(\Back\Bundle\Entity\Packages $package = null)
    {
        $this->package = $package;

        return $this;
    }

    /**
     * Get package
     *
     * @return \Back\Bundle\Entity\Packages
     */
    public function getPackage()
    {
        return $this->package;
    }
}

PackagesDatesPricesRepository.php:

<?php

namespace Back\Bundle\Entity;

use Doctrine\ORM\EntityRepository;

/**
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class PackagesDatesPricesRepository extends EntityRepository
{
    public function getTest($id)
    {
        $req=$this->getEntityManager()->createQuery
        (
            "SELECT a
            FROM BackBundle:PackagesDatesPrices a
            INNER JOIN BackBundle:Packages b
            WHERE a.package = b.id
            AND  b.id = :id
            "
        );

        $req ->setParameter('id', $id);

        return $req->getResult();
    }

}

My controller:

public function packagePageAction($id)
    {
        $test = $this->getDoctrine()
            ->getManager()
            ->getRepository('BackBundle:PackagesDatesPrices')
            ->getTest($id)
        ;

        return $this->render('BackBundle:Global:packagePage.html.twig',array(
            'test' => $test
        ));
    }

I have a OneToMany relationship between package id (field name is id) and package_dates_prices (field name is package).

I have not found a mistake yet. I tried a lot of time, but I am not very familiar with the doctrine.

Thank you for reading me and helping me.

Answer 1

Symfony2 .

- SQL.

$package = $this->getDoctrine()->getRepository("BackBundle:Packages")->find($id);
$datesPrices = $package->getDatesPrices();

$datePrices , OneToMany.