CODEWITHSUNDEEP
doctrine-orm
BrainPicker
BrainPicker

Reputation: 437

Doctrine Dql for One-To-Many, Unidirectional with Join Table

So I've read the following article.

I've done something similar with my own entities and am trying to create a DQL.

DQL: 

$em = $this->getDoctrine()->getManager();
$query = $em->createQuery(
    'SELECT s,st,tt
     FROM CrmClientBundle:Session s
     INNER JOIN s.session_treatments st
     INNER JOIN st.treatment_type_id tt
     WHERE s.client_id = ' . $id
);
$sessions = $query->getResult();

I receive the following error though: 

[Semantical Error] line 0, col 152 near 'tt
': Error: Class Crm\ClientBundle\Entity\TreatmentType has no association named
treatment_type_id

However if I remove the second join and check the symfony profiler it creates the following query which seems to me that I have created my entities properly:

SELECT 
  s0_.id AS id0, 
  s0_.client_id AS client_id1, 
  s0_.date AS date2, 
  s0_.session_id AS session_id3, 
  s0_.session_type AS session_type4, 
  s0_.session_cost AS session_cost5, 
  s0_.products_bought AS products_bought6, 
  s0_.products_cost AS products_cost7, 
  s0_.total_cost AS total_cost8, 
  s0_.total_paid AS total_paid9, 
  s0_.notes AS notes10, 
  t1_.id AS id11, 
  t1_.type AS type12, 
  t1_.cost AS cost13, 
  t1_.category_id AS category_id14 
FROM 
  sessions s0_ 
  INNER JOIN session_treatments s2_ ON s0_.id = s2_.session_id 
  INNER JOIN treatment_types t1_ ON t1_.id = s2_.treatment_type_id 
WHERE 
  s0_.client_id = 1

Crm\ClientBundle\Session.php:

/**
 * @ORM\Entity
 * @ORM\Table(name="sessions")
 */
class Session {
/**
 * @ORM\Id
 * @ORM\Column(type="integer")
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/** @ORM\Column(type="integer") */
private $client_id;

/** @ORM\Column(type="date") */
private $date;

/**
 * **
 * @ORM\ManyToMany(targetEntity="TreatmentType")
 * @ORM\JoinTable(name="session_treatments",
 *      joinColumns={@ORM\JoinColumn(name="session_id", referencedColumnName="id")},
 *      inverseJoinColumns={@ORM\JoinColumn(name="treatment_type_id", referencedColumnName="id ", unique=true)}
 *      )
 */
private $session_treatments;

/**
 * Constructor
 */
public function __construct()
{
    $this->session_treatments = new ArrayCollection();
}
}

Crm\ClientBundle\TreatmentType.php:

/**
 * @ORM\Entity
 * @ORM\Table(name="treatment_types")
 */
class TreatmentType {

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

/** @ORM\Column(type="string", length=255) */
private $type;

/** @ORM\Column(type="decimal") */
private $cost;
}

Upvotes: 3

Views: 6225

Answers (1)

Flip
Flip

Reputation: 4908

You have 2 entities and you are trying to retrieve 3 entities. The second join is unnecessary. It's only needed if TreatmentType has yet another relationship (other then the one with Session). For clarification:

$em = $this->getDoctrine()->getManager();
$query = $em->createQuery(
        'SELECT s,st,tt
         FROM CrmClientBundle:Session s // <-- s is your Session
         INNER JOIN s.session_treatments st // <-- st is your TreatmentType
         INNER JOIN st.treatment_type_id tt <-- TreatmentType does not have a property $treatement_type_id that points to non-defined relationship. tt would be your 3rd entity.
         WHERE s.client_id = ?1'
);
$query->setParameter(1, $id);
$sessions = $query->getResult();

Bonus: used bound a parameter -- helps against SQL injection and speeding up your query in the future

Upvotes: 2

Related Questions