I'm working on my portfolio, and I got a page where I show my project. I created 2 cayegorie for now: Programmation and artistic. Each of my project can be a programming project, an artistique project or both. There for, I made a table project and a table categorie and they are join with a many to many relationship. So far, no probleme.
I started to create my query in the repository file of my project entitie. The problem is when I try to join my project entitie and my categorie entitie, it doesn't look like it works, because I specefy that I want just the project that have at least programmation for categorie at least. But it still returns me ALL the project when it should only give me 2 of them.
Did I made my JOIN right?
Here are the entities (watch out for the french!): Project:
<?php
namespace PublicBundle\Entity;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\ORM\Mapping as ORM;
/**
* Projet
*
* @ORM\Table(name="pt_projet");
* @ORM\Entity
* @ORM\Entity(repositoryClass="PublicBundle\Entity\ProjetDepot")
*/
class Projet
{
/**
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
//ID du projet
protected $id;
/**
* @ORM\Column(name="pro_tag", type="string",length=255, unique=true)
*/
//Tag du projet
protected $tag;
/**
* @ORM\OneToMany(targetEntity="ProjetInt", mappedBy="projetId", orphanRemoval=true)
*/
protected $descriptions;
/**
* @ORM\Column(name="pro_img", type="string", length=64, unique=true)
*/
//Nom du fichier de l'image du projet
protected $image;
/**
* @ORM\Column(name="pro_technologie_utilisee", type="text", length=200)
*/
//Text qui liste tout les technologies utilisées pour le projet
protected $technologie;
/**
* @ORM\Column(name="pro_annee", type="integer", length=4)
*/
//Année de réalisation du projet
protected $annee;
/**
* @ORM\ManyToOne(targetEntity="Type", inversedBy="projets")
* @ORM\JoinColumn(name="pro_type", referencedColumnName="id", nullable=false)
*/
//Clef étrangère du type de projet
//Le type de projet ne correspond pas à la catégore. Il peu être Unity, flash, image, vidéo, etc. Il permet de savoir quelle page charger pour pouvoir intégrer le projet dans le portfolio.
protected $type;
/**
* @ORM\Column(name="pro_fichier", type="string", length=64, unique=true)
*/
//Nom du fichier du projet
private $fichier;
/**
* @ORM\ManyToMany(targetEntity="Categorie", cascade={"persist"})
*/
//La ou les catégories du projet
private $categories;
/**
* Constructor
*/
public function __construct()
{
$this->descriptions=new ArrayCollection();
$this->categories=new ArrayCollection();
}
/**
* Get id
*
* @return integer
*/
public function getId()
{
return $this->id;
}
/**
* Set image
*
* @param string $image
* @return Projet
*/
public function setImage($image)
{
$this->image = $image;
return $this;
}
/**
* Get image
*
* @return string
*/
public function getImage()
{
return $this->image;
}
/**
* Set technologie
*
* @param string $technologie
* @return Projet
*/
public function setTechnologie($technologie)
{
$this->technologie = $technologie;
return $this;
}
/**
* Get technologie
*
* @return string
*/
public function getTechnologie()
{
return $this->technologie;
}
/**
* Set annee
*
* @param integer $annee
* @return Projet
*/
public function setAnnee($annee)
{
$this->annee = $annee;
return $this;
}
/**
* Get annee
*
* @return integer
*/
public function getAnnee()
{
return $this->annee;
}
/**
* Set fichier
*
* @param string $fichier
* @return Projet
*/
public function setFichier($fichier)
{
$this->fichier = $fichier;
return $this;
}
/**
* Get fichier
*
* @return string
*/
public function getFichier()
{
return $this->fichier;
}
/**
* Set type
*
* @param Type $type
* @return Projet
*/
public function setType(Type $type)
{
$this->type = $type;
return $this;
}
/**
* Get type
*
* @return Type
*/
public function getType()
{
return $this->type;
}
/**
* Add categories
*
* @param Categorie $categories
* @return Projet
*/
public function addCategory(Categorie $categories)
{
$this->categories[] = $categories;
return $this;
}
/**
* Remove categories
*
* @param Categorie $categories
*/
public function removeCategory(Categorie $categories)
{
$this->categories->removeElement($categories);
}
/**
* Get categories
*
* @return \Doctrine\Common\Collections\Collection
*/
public function getCategories()
{
return $this->categories;
}
/**
* Add description
*
* @param \PublicBundle\Entity\ProjetInt $description
* @return Projet
*/
public function addDescription(\PublicBundle\Entity\ProjetInt $description)
{
$this->description[] = $description;
return $this;
}
/**
* Remove description
*
* @param \PublicBundle\Entity\ProjetInt $description
*/
public function removeDescription(\PublicBundle\Entity\ProjetInt $description)
{
$this->description->removeElement($description);
}
/**
* Get description
*
* @return \Doctrine\Common\Collections\Collection
*/
public function getDescription()
{
return $this->description;
}
}
Category:
<?php
namespace PublicBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
/**
* Catégorie
*
* @ORM\Table(name="pt_categorie");
* @ORM\Entity
* @ORM\Entity(repositoryClass="PublicBundle\Entity\CategorieDepot")
*/
class Categorie
{
/**
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
//ID de la catégorie
protected $id;
/**
* @ORM\Column(name="cat_tag", type="string",length=255, unique=true)
*/
//Tag de la catégorie
protected $tag;
/**
* Get id
*
* @return integer
*/
public function getId()
{
return $this->id;
}
/**
* Set tag
*
* @param string $tag
* @return Categorie
*/
public function setTag($tag)
{
$this->tag = $tag;
return $this;
}
/**
* Get tag
*
* @return string
*/
public function getTag()
{
return $this->tag;
}
}
And the reposetory:
<?php
namespace PublicBundle\Entity;
use Doctrine\ORM\EntityRepository;
/**
* ProjetDepot
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class ProjetDepot extends EntityRepository
{
public function rechercherProjets($lang, $cat)
{
return $this->getEntityManager()->createQuery(
'SELECT p.tag,
p.image,
pi.nom,
pi.descriptionCours,
pi.descriptionComplete,
pi.roles,
p.technologie,
pi.aptitudesDeveloppees,
p.annee
FROM PublicBundle:Projet p
JOIN PublicBundle:ProjetInt pi
WITH p.id=pi.projetId
JOIN PublicBundle:Categorie c
WHERE pi.langue=:lang
AND c.tag=:cat'
)->setParameters(array('lang'=>$lang,'cat'=>$cat))->getResult();
}
}
Try this
public function rechercherProjets($lang, $cat) {
$qb = $this->createQueryBuilder('p')
->innerJoin ('p.description', 'pi')
->innerJoin('p.categories', 'pc')
->andWhere('pc.tag = :cat')
->andWhere('pi.langue = :lang')
->setParameters(array('lang'=>$lang,'cat'=>$cat));
return $qb->getQuery()->getResult()
}