Codeigniter and doctrine join three tables

Question

Hello there I am trying to join 3 tables in doctrine codeigniter i have successfully join two tables but have problem in joining the third one.

Here is my codeigniter active record Query. I want to handle this query in doctrine

$query =  $this->db->select('table1.name, table3.date')
        ->from('table1')
        ->join('table2', 'table2.id = table1.Uid')
        ->join('table3', 'table3.id = table1.Rid')
        ->where('table1.Uid', $Uid)
        ->get();
    return $query->result();

This is my Doctrine query builder query where i join two tables successfully.

$qb = $this->em->createQueryBuilder();
    $query = $qb->select('t1, t2')
        ->from('table1', 't1')
        ->join('table2', 't2')
        //   ->join('table3', 't3')
        ->where('t1.Uid = :Uid')
        ->andwhere('t2.Yid = :Yid')
        ->setParameters(array('Uid'=> $Uid, 'Yid' => $user_id))
        ->getQuery()
        ->getResult();

In Doctrine the JOIN syntax goes as >select('t1.name', 't2.name') ->from('table1', 't1') ->innerJoin('t1', 'table2', 't2', 't2.id = t1.Uid') ->innerJoin('t1', 'table3', 't3', 't3.id = t1.Rid') — Abhik Chakraborty

Ilanus Ilanus · Accepted Answer · 2015-08-27T08:10:24

Try using the following code, seems like your syntax is not correct.

$qb = $this->em->createQueryBuilder();
$query = $qb->select('t1.name', 't2.name')
->from('table1', 't1')
->leftJoin('t1', 'table2', 't2', 't2.id = t1.Uid')
->leftJoin('t1', 'table3', 't3', 't3.id = t1.Rid')
->where('t1.Uid = :Uid')
->andwhere('t2.Yid = :Yid')
->setParameters(array('Uid'=> $Uid, 'Yid' => $user_id))
->getQuery()
->getResult()

More information about Constructing a new QueryBuilder object

Codeigniter and doctrine join three tables

1 Answers