doctrine-orm - 用于一对多、单向连接表的 Doctrine Dql

Question

所以我阅读了以下内容 article .

我对我自己的实体做了类似的事情，并且正在尝试创建一个 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();

虽然我收到以下错误:

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

但是，如果我删除第二个连接并检查 symfony 分析器，它会创建以下查询，在我看来，我已经正确创建了我的实体:

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;
}

Answer 1

您有 2 个实体，您正在尝试检索 3 个实体。第二次加入是不必要的。仅当 TreatmentType 具有另一种关系(与 Session 的关系不同)时才需要它。澄清一下:

$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();

奖励:使用绑定(bind)参数——有助于防止 SQL 注入(inject)并在将来加快查询速度