How to convert SQL query to Symfony2 DQL

Question

SQL Query: I am trying to get affiliation, feerange, insttype(checkbox data) through DQL query.

Here is SQL query:

$affiliation = implode(",", array('1','2','3','4'));
$feerange    = implode(",", array('1','2','3','4','5','6'));
$insttype    = implode(",", array('1','2','3'));  
$dql = "SELECT i FROM Edufaction\Bundle\EdufactionBundle\Entity\Institute i"
    . " JOIN i.instsummary s"
    . " WHERE s.instaffiliation IN ($affiliation)"
    . " AND s.insttype IN ($insttype)"
    . " AND i.institutefeerange IN ($feerange)"
    . " ORDER BY i.instituterating ASC"; 

I have two entities: Institute Entity:

/**
 * @ORM\Entity
 * @ORM\Table(name="institute")
 */
class Institute {
public static $instfeerange = array(1 => "₹",
                            2 => "₹₹", 
                            3 => "₹₹₹", 
                            4 => "₹₹₹₹", 
                            5 => "₹₹₹₹₹", 
                            6 => "₹₹₹₹₹₹",
                            0 => " "  );
/**
 * @ORM\Id
 * @ORM\Column(type = "integer", name= "id")
 * @ORM\GeneratedValue(strategy="AUTO")
 */
protected $id;
/**
 * @ORM\OneToOne(targetEntity="InstSummary", mappedBy="institute")
 */
protected $instsummary;

/**
 * @ORM\Column(type="string",name="institutename", length=200,nullable=true)
 */
protected $institutename;

/**
 * @ORM\Column(type="string",name="institutefeerange", length=100,nullable=true)
 */
protected $institutefeerange;
/**
 * @ORM\Column(type="decimal", precision=19, scale=2, name="instituterating",nullable=true)
 */
protected $instituterating;

public function getId() {
    return $this->id;
}

public function getInstitutename() {
    return $this->institutename;
}

public function getInstitutefeerange() {
    return $this->institutefeerange;
}
function getInststringfeerange() {
     $aff = $this->institutefeerange;
     $test = self::$instfeerange[$aff];
     return $test;
}
public function getInstituterating() {
    return $this->instituterating;
}

public function setId($id) {
    $this->id = $id;
}

public function setInstitutename($institutename) {
    $this->institutename = $institutename;
}

public function setInstitutefeerange($institutefeerange) {
    $this->institutefeerange = $institutefeerange;
}

public function setInstituterating($instituterating) {
    $this->instituterating = $instituterating;
}

/**
 * Set instsummary
 *
 * @param \Edufaction\Bundle\EdufactionBundle\Entity\InstSummary $instsummary
 *
 * @return Institute
 */
public function setInstsummary(\Edufaction\Bundle\EdufactionBundle\Entity\InstSummary $instsummary = null) {
    $this->instsummary = $instsummary;

    return $this;
}

/**
 * Get instsummary
 *
 * @return \Edufaction\Bundle\EdufactionBundle\Entity\InstSummary
 */
public function getInstsummary() {
    return $this->instsummary;
} }

InstSummary Entity: Here I applied OneToOne relation between Institute and InstSummary table through reference institute_id

/**
 * @ORM\Entity
 * @ORM\Table(name="instsummary")
 */
class InstSummary {

public static $instituteaff = array(1 => "ICSE",
                            2 => "CBSE", 
                            3 => "State Board", 
                            4 => "ISC", 
                            5 => "IGCSE", 
                            6 => "IB", 
                            7 => "Play School",
                            8 => "Not Applicable",
                            ''=>""
                            );
public static $instype = array(1 => "Boys",
                            2 => "Girls", 
                            3 => "Co-educational"
                            );
/**
 * @ORM\Id
 * @ORM\Column(type = "integer", name= "id")
 * @ORM\GeneratedValue(strategy="AUTO")
 */
protected $id;

// ...

/**
 * @ORM\OneToOne(targetEntity="Institute", inversedBy="instsummary")
 * @ORM\JoinColumn(name="institute_id", referencedColumnName="id")
 */
protected $institute;

/**
 * @ORM\Column(type="bigint", name="instaffiliation", length=50,nullable=true)
 */
protected $instaffiliation;
/**
 * @ORM\Column(type="integer", name="insttype", length=10,nullable=true)
 */
protected $insttype;

public function getId() {
    return $this->id;
}

public function getInstaffiliation() {
    return $this->instaffiliation;
}

function getInststringaffiliation() {
    $aff = $this->instaffiliation;
    $test = self::$instituteaff[$aff];
    return $test;
}

public function getInstituteid() {
    return $this->instituteid;
}

public function getInsttype() {
    return $this->insttype;
}
function getInststringtype() {
     $type = $this->insttype;
     $test = self::$instype[$type];
     return $test;
}

public function setId($id) {
    $this->id = $id;
}
public function setInstaffiliation($instaffiliation) {
    $this->instaffiliation = $instaffiliation;
}
public function setInstituteid($instituteid) {
    $this->instituteid = $instituteid;
}

public function setInsttype($insttype) {
    $this->insttype = $insttype;
}

/**
 * Set institute
 *
 * @param \Edufaction\Bundle\EdufactionBundle\Entity\Institute $institute
 *
 * @return InstSummary
 */
public function setInstitute(\Edufaction\Bundle\EdufactionBundle\Entity\Institute $institute = null) {
    $this->institute = $institute;

    return $this;
}

/**
 * Get institute
 *
 * @return \Edufaction\Bundle\EdufactionBundle\Entity\Institute
 */
public function getInstitute() {
    return $this->institute;
}
}

DQL query:

$data = $request->request->get('request'); 
$repository = $em->getRepository('EdufactionBundle:Institute');

$query1 = $repository->createQueryBuilder('i')
        ->innerJoin('i.instsummary', 's')              
        ->select('s.id as instaffiliation, i.id as insttype')  
        ->where('s.id=:instaffiliation')  
        ->setParameter('instaffiliation', $data)   
        ->getQuery()->getResult();

How to convert SQL query to Symfony2 DQL query? I tried this DQL query but I am not getting I think this is wrong...

Pls any one help - Thanks for advance...

Answer 1

You should use IN to check for all the comma separated value, Please check below query, May be it will help you.

$data = $request->request->get('request'); 
$repository = $em->getRepository('EdufactionBundle:Institute');
$queryBuilder = $repository->createQueryBuilder('i');
$query1 = $queryBuilder
                ->innerJoin('i.instsummary', 's')            
                ->select('s.id as instaffiliation, i.id as insttype')  
                ->where('s.id IN (:instaffiliation)')  
                ->setParameter('instaffiliation', $data(should be array))   
                ->getQuery()->getResult();