CODEWITHSUNDEEP
symfonydoctrine-ormquery-builder
Imen AchOurii
Imen AchOurii

Reputation: 11

How convert my SQL statement to Doctrine QueryBuilder?

SELECT * FROM PropertyRequest p JOIN leads l WHERE p.lead_id= l.id

How can I write this sql statement with doctrine2 querybuilder? I'm trying with this 

$qb = $this->createQueryBuilder('p')
        ->Join('p.lead', 'm')


        ->Where('m.id = :lead')

        ->setParameter(':lead', $lead);


    return $qb->getQuery()->getResult();

but doesn't work

Upvotes: 1

Views: 454

Answers (2)

ib.programmer
ib.programmer

Reputation: 103

this is the way i make my queries.

//---Controller

  $em = $this->getDoctrine()->getRepository('SomeBundle:SomeEntity');
  $query = $em->createQueryBuilder('p')
      ->select("p")
      ->leftJoin("p.lead", "c")
      ->where("m.id = :lead")
      ->setParameter(":lead", $lead);
  return $query->getResult();

//---Entity

<?php
namespace Some\SomeBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Some\SomeBundle\Entity\SomeEntity
 *
 * @ORM\Table(name="sometable")
 * @ORM\Entity
 */
class SomeEntity
{
    /**
     * @var integer $id
     *
     * @ORM\Column(name="id", type="bigint", nullable=false)
     * @ORM\Id
     */
    private $id;

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

    /**
     * @var SomeOtherEntity
     *
     * @ORM\ManyToOne(targetEntity="SomeOtherEntity")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="lead", referencedColumnName="onelead")
     * })
     */
    private $lead;

    /**
     * Set lead
     *
     * @param Some\SomeBundle\Entity\SomeOtherEntity $lead
     * @return SomeOtherEntity
     */
    public function setLead(\Some\SomeBundle\Entity\SomeOtherEntity $lead = null)
    {
        $this->lead = $lead;
        return $this;
    }

    /**
     * Get lead
     *
     * @return Some\SomeBundle\Entity\SomeOtherEntity 
     */
    public function getLead()
    {
        return $this->lead;
    }
}

Does it throw an error?

Upvotes: 0

OberstK
OberstK

Reputation: 150

I suspect from here on, that you defined the lead_id as "lead" in the entity p. My answer is based on these assumptions.

You can then use this statement to perform the join

    $qb = $this->createQueryBuilder('p')
    ->innerJoin('p.lead', 'm', 'WITH', 'p.lead = m.id')
    ->Where('m.id = :lead')
    ->setParameter(':lead', $lead);

    return $qb->getQuery()->getResult();

If this does not work you need to provide a error message an probably the definition of both entities as well.

Upvotes: 0

Related Questions