Dlaczego mi zwraca null przy natywny sql w zapytaniu. Dump w szablonie są same null.
public function getExpensesByProperty() { $rsm = new ResultSetMapping; $rsm->addEntityResult('User\UserBundle\Entity\User', 'u'); $rsm->addFieldResult('u', 'username', 'username'); $sql = " SELECT username FROM users "; $query= $this->_em->createNativeQuery( $sql, $rsm ); return $query->getResult(); } }
Hmm patrz na tą linijkę:
$rsm->addEntityResult('User\UserBundle\Entity\User', 'u');
Nie pobieraj pojedynczych kolumn mapując dane na encje, zapomnisz czegoś zmapować i znowu będziesz się zastanawiał, czemu jest null.
SELECT u.* FROM users u
Teraz mam problem ze SUMĄ . W ogóle ją nie wyświetla
public function getExpensesByProperty() { $rsm = new ResultSetMapping; $rsm->addEntityResult('User\UserBundle\Entity\User', 'u'); $rsm->addFieldResult('u', 'username', 'username'); $rsm->addFieldResult('u', 'id', 'id'); $rsm->addJoinedEntityResult('Property\ManagementBundle\Entity\Owner' , 'o', 'u', 'owners'); $rsm->addFieldResult('o', 'o_id', 'id'); $rsm->addFieldResult('o', 'o_name', 'name'); $rsm->addJoinedEntityResult('Property\ManagementBundle\Entity\Property' , 'p', 'o', 'property'); $rsm->addFieldResult('p', 'p_id', 'id'); $rsm->addFieldResult('p', 'p_name', 'name'); $rsm->addFieldResult('p', 'p_adress', 'adress'); $rsm->addJoinedEntityResult('Property\ManagementBundle\Entity\Expenses' , 'e', 'p', 'expenses'); $rsm->addFieldResult('e', 'e_id', 'id'); $rsm->addFieldResult('e', 'e_name', 'name'); $rsm->addFieldResult('e', 'e.price', 'price'); $sql = " SELECT u.id,u.username, o.id as o_id,o.name as o_name, p.id as p_id,p.name as p_name,p.adress as p_adress, e.id as e_id,e.name as e_name,SUM(e.price) as SUMA FROM users u INNER JOIN owner o ON u.id = o.user_id INNER JOIN property p ON o.id=p.owner_id LEFT JOIN expenses e ON p.id=e.property_id GROUP BY p_id "; $query= $this->_em->createNativeQuery( $sql, $rsm ); // $query->setParameter(1,$User); return $query->getArrayResult(); } }
Pozwoliłem sobie poprawić:
public function findExpensesByProperty() { $rsm = new ResultSetMapping; $rsm->addEntityResult('User\UserBundle\Entity\User', 'u'); $rsm->addJoinedEntityResult('Property\ManagementBundle\Entity\Owner', 'o', 'u', 'owners'); $rsm->addJoinedEntityResult('Property\ManagementBundle\Entity\Property' , 'p', 'o', 'property'); $rsm->addJoinedEntityResult('Property\ManagementBundle\Entity\Expenses' , 'e', 'p', 'expenses');\ $rsm->addScalarResult('SUMA', 'sum'); // W tablicy będzie pod kluczem z 2 argumentu, czyli sum $sql = " SELECT u.id,u.username, o.id as o_id,o.name as o_name, p.id as p_id,p.name as p_name,p.adress as p_adress, e.id as e_id,e.name as e_name,SUM(e.price) as SUMA FROM users u INNER JOIN owner o ON u.id = o.user_id INNER JOIN property p ON o.id=p.owner_id LEFT JOIN expenses e ON p.id=e.property_id GROUP BY p_id "; $query= $this->_em->createNativeQuery( $sql, $rsm ); // $query->setParameter(1,$User); return $query->getArrayResult(); } }
Potrzebuję takie zapytania zrobić ale widzę że nie jest to takie łatwe
SELECT `users`.`username`,`owner`.`name`,`property`.`name` AS `p`,`property`.`adress`,`expenses`.`name` , SUM(`expenses`.`price`) AS `s`,`property`.`id` AS `i` FROM `users` INNER JOIN `owner` ON `users`.`id`=`owner`.`user_id` INNER JOIN `property`ON `owner`.`id`=`property`.`owner_id` LEFT JOIN `expenses`ON `property`.`id`=`expenses`.`property_id` GROUP BY `property`.`id`,`expenses`.`id` UNION SELECT `users`.`username`,`owner`.`name`,`property`.`name`,`property`.`adress`,`expenses`.`name`=NULL,SUM(`expenses`.`price`),`property`.`id` FROM `users` INNER JOIN `owner`ON `users`.`id`=`owner`.`user_id` INNER JOIN `property`ON `owner`.`id`=`property`.`owner_id` LEFT JOIN `expenses`ON `property`.`id`=`expenses`.`property_id` GROUP BY `property`.`id` ORDER BY `i`,`s`
Doctrine ORM (object-relational mapping) nie służy do "robienia zapytań", jest to obiektowa warstwa abstracji dla bazy danych i nie można tutaj myśleć kategoriami zapytań SQL, tylko obiektami i ich wzajemnymi relacjami. Mechanizmy Doctrine ORM nigdy nie wygenerują Ci takiego zapytania, bo nie o to tutaj chodzi.
Jeżeli chcesz pisać zapytania SQL zamiast korzystać z mapowania na obiektu, to użyj Doctrine DBAL (database abstraction layer), link do dokumentacji: http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/
Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)