CODEWITHSUNDEEP
mysqlsqldoctrine-orminner-joinnativequery
Mr B
Mr B

Reputation: 4130

How to write SQL query using Doctrine 2's NativeQuery with joins?

I want to write a query that involves the use of 'UNION' (which is not supported by DQL) therefore I am trying to write a MySQL query using Doctrine 2's NativeQuery. I have been using Doctrine's documentation.

So far my code consists of:

$rsm = new ResultSetMapping();
$rsm->addEntityResult('\Entities\Query', 'q');
$rsm->addFieldResult('q', 'id', 'id');
$rsm->addFieldResult('q', 'title', 'title');
$rsm->addFieldResult('q', 'deadline', 'deadline');

$query = $this->_em->createNativeQuery('SELECT query.id, query.title, query.deadline FROM query', $rsm);
$aQuery = $query->getResult();

$aQuery = $query->getResult();

foreach($aQuery as $o){
    echo '<p>';
    echo 'id: '.$o->getId().'<br/>';
    echo 'title: '.$o->getTitle().'<br/>';
    echo 'deadline: '.$o->getDeadline()->getTimestamp().'<br/>';  
    echo '</p>';die;
}

This returns the expected results. However, I run into problems when I try to introduce a second table/entity in the query:

$rsm->addJoinedEntityResult('\Entities\Status', 's', 'q', 'status');
$rsm->addFieldResult('s', 'id', 'id', '\Entities\Status');

Those lines throw a: Notice: Undefined index: id message.I can't figure out why this is.

The query table has a 'status_id' column, which is a foreign key referring to the 'id' column of the 'status' table. In the query entity class I have mapped the status property as follows:

/**
 * @var Status
 *
 * @ManyToOne(targetEntity="Status")
 * @JoinColumns({
 *   @JoinColumn(name="status_id", referencedColumnName="id")
 * })
 */
private $status;

Along with relevant get and set methods.

I have spent quite some time on trying to figure this out. Appreciate it if someone could shed some light on this.

Upvotes: 1

Views: 563

Answers (1)

Md Mehedi Hasan
Md Mehedi Hasan

Reputation: 1792

You may write your query in any repository like below then you will get expected ans. In This way you use any SQL function of MySQL 

public function yourFunction()
    {

        $em    = $this->getEntityManager()->getConnection();
        $sql = "YOUR DESIRE SQL"; // like SELECT * FROM users

        try {
            return $em->query($sql)->fetchAll();
        } catch (\Doctrine\ORM\NoResultException $e) {
            return null;
        }
    }

Upvotes: 1

Related Questions