Я хочу получить данные из двух разных таблиц в зависимости от значения одного поля (типа). В принципе, если тип равен 2 или 3, относите 'subcategory_id' к категории2 или категории 3 соответственно
Простой SQL:
SELECT s.id, s.subcategory_id, s.type, IF(s.type = 2, c2.name, c3.name) as name FROM show_subcategory s LEFT JOIN category2 c2 ON (s.type = 2 AND s.subcategory_id = c2.id) LEFT JOIN category3 c3 ON (s.type = 3 AND s.subcategory_id = c3.id) WHERE s.category1_id = 1 ORDER BY s.order_list Перевод в DQL в класс репозитория:
$em = $this->getEntityManager(); $rsm = new ResultSetMapping; $rsm->addEntityResult('Acme\CoreBundle\Entity\ShowSubcategory', 's'); $rsm->addFieldResult('s', 'id', 'id'); $rsm->addFieldResult('s', 'subcategory_id', 'subcategoryId'); $rsm->addFieldResult('s', 'type', 'type'); $rsm->addFieldResult('s', 'order_list', 'orderList'); $rsm->addJoinedEntityResult('Acme\CoreBundle\Entity\Category2' , 'c2', 's', 'category2'); $rsm->addFieldResult('c2', 'id', 'id'); $rsm->addFieldResult('c2', 'name', 'name'); $rsm->addJoinedEntityResult('Acme\CoreBundle\Entity\Category3' , 'c3', 's', 'category3'); $rsm->addFieldResult('c3', 'id', 'id'); $rsm->addFieldResult('c3', 'name', 'name'); $q = $em->createNativeQuery("SELECT s.id, s.subcategory_id, s.type, IF(s.type = 2, c2.name, c3.name) as name FROM show_subcategory s LEFT JOIN category2 c2 ON (s.type = 2 AND s.subcategory_id = c2.id) LEFT JOIN category3 c3 ON (s.type = 3 AND s.subcategory_id = c3.id) WHERE s.category1_id = ? ORDER BY s.order_list", $rsm); $q->setParameter(1, $category1); return $q->getResult();
В сущности для ShowSubcategory я добавил два новых свойства для обработки этого запроса (category2, category3):
/** * @var integer $id * * @ORM\Column(name="id", type="integer", nullable=false) * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") */ protected $id; /** * @var integer $subcategoryId * * @ORM\Column(name="subcategory_id", type="integer", nullable=false) */ protected $subcategoryId; /** * @var smallint $type * * @ORM\Column(name="type", type="smallint", nullable=false) */ protected $type; /** * @var smallint $orderList * * @ORM\Column(name="order_list", type="smallint", nullable=true) */ protected $orderList; /** * @var \Category1 * * @ORM\ManyToOne(targetEntity="Acme\CoreBundle\Entity\Category1") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="category1_id", referencedColumnName="id") * }) */ protected $category1; /** * @var category2 * * @ORM\ManyToOne(targetEntity="Acme\CoreBundle\Entity\Category2") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="subcategory_id", referencedColumnName="id") * }) * @Assert\Blank() */ protected $category2; /** * @var category3 * * @ORM\ManyToOne(targetEntity="Acme\CoreBundle\Entity\Category3") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="subcategory_id", referencedColumnName="id") * }) * @Assert\Blank() */ protected $category3; /** * Get id * * @return integer */ public function getId() { return $this->id; } /** * Set category1 * * @param \Acme\CoreBundle\Entity\Category1 $category1 * @return ShowSubcategory */ public function setCategory1(\Acme\CoreBundle\Entity\Category1 $category1 = null) { $this->category1 = $category1; return $this; } /** * Get category1 * * @return Acme\CoreBundle\Entity\Category1 */ public function getCategory1() { return $this->category1; } /** * Set subcategoryId * * @param integer $subcategoryId * @return ShowSubcategory */ public function setSubcategoryId($subcategoryId) { $this->subcategoryId = $subcategoryId; return $this; } /** * Get subcategoryId * * @return integer */ public function getSubcategoryId() { return $this->subcategoryId; } /** * Set type * * @param smallint $type * @return ShowSubcategory */ public function setType($type) { $this->type = $type; return $this; } /** * Get type * * @return smallint */ public function getType() { return $this->type; } /** * Set order * * @param smallint $OrderList * @return ShowSubcategory */ public function setOrderList($orderList) { $this->orderList = $orderList; return $this; } /** * Get OrderList * * @return smallint */ public function getOrderList() { return $this->orderList; } public function getCategory2() { return $this->category2; } public function setCategory2(Category2 $category2 = null) { $this->category2 = $category2; return $this; } public function getCategory3() { return $this->category3; } public function setCategory3(Category3 $category3 = null) { $this->category3 = $category3; return $this; }
}
Я получаю эту ошибку:
Notice: Undefined index: id in /var/www/project/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php line 2402
Я не знаю, что не так. Я попробовал несколько идей, но ничего не работает. Любое предложение?. Заранее спасибо.