CODEWITHSUNDEEP
phpsymfonysymfony-3.3
Hemraj Pal
Hemraj Pal

Reputation: 164

Symfony3 - create join query of two tables in controller

How to show two tables data from my controller.

Here is my controller's code.

class TestController extends Controller
{
   public function showAction(Request $request)
   {
        $em = $this->getDoctrine()->getManager();
        $teacher = $this->getDoctrine()->getRepository(Teacher::class);

        $query = $em
        ->createQueryBuilder('t')
        ->from('AppBundle:Teacher','t')
        ->Join('AppBundle:Student','s')
        ->where('t.id=id and s.tid=tid')
        ->getQuery()
        ->getResult();
    }
}

When print_r it's showing only one table data. Please help

Upvotes: 1

Views: 1750

Answers (5)

pedram shabani
pedram shabani

Reputation: 1680

Suppose you have two tables.comment table and article table and You want to fetch comments on each article

 $commentContent = $em
             // automatically knows to select Comment
            // the "c" is an alias you'll use in the rest of the query
            ->createQueryBuilder('c')
            ->select('c.message, c.name')////Fields required to display
            ->from('AppBundle:Comment','c')
            ->join('AppBundle:Article','a')
            ->where('c.idArticle=a.id and c.publish_mainPage = 1')
            ->orderBy('c.idArticle', 'DESC')
            ->getQuery()
            ->getResult();

 var_dump($commentContent);

Upvotes: 0

Hemraj Pal
Hemraj Pal

Reputation: 164

$query = $em
    ->createQueryBuilder('t')
    ->add('select', 't,s')
    ->from('AppBundle:Teacher', 't')
    ->Join('AppBundle:Student', 's')
    ->where('t.id = s.tid')
    ->getQuery()
    ->getResult();

it working perfect.

Upvotes: 1

Marek Zavoronok
Marek Zavoronok

Reputation: 140

First we select all from Teachers table, then join students. Assume that your relationship name in Teachers model is student. In repository file:

public function getWithStudents() {
    return $this->createQueryBuilder('t') 
        ->Join('t.student', 's') 
        ->addSelect('s')
        ->getQuery()->getArrayResult();
}

Then in controller call it:

$teachersWithStudents = $teacher->getWithStudents();

Or in this case you can just

$teachersWithStudents = $teacher->getStudents();

Upvotes: 0

W0rma
W0rma

Reputation: 309

I assume that you have defined a relationship between Teacher and Student in your entities. In this case you can get the Student objects by calling $teacher->getStudents() (assuming that you have defined such a method in your Teacher entity class). See Doctrine documentation about association mapping

Example for a One-To-Many relationship:

<?php
use Doctrine\Common\Collections\ArrayCollection;

/** @Entity */
class Teacher
{
    // ...
    /**
     * One Teacher has Many Students.
     * @OneToMany(targetEntity="Student", mappedBy="teacher")
     */
    private $students;
    // ...

    public function __construct() {
        $this->students = new ArrayCollection();
    }
}

/** @Entity */
class Student
{
    // ...
    /**
     * Many Students have One Teacher.
     * @ManyToOne(targetEntity="Teacher", inversedBy="students")
     * @JoinColumn(name="teacher_id", referencedColumnName="id")
     */
    private $teacher;
    // ...
}

In the QueryBuilder object you can avoid the need of additional queries on $teacher->getStudents() calls by adding something like that:

$query = $em
   ->createQueryBuilder('t')
   ->from('AppBundle:Teacher','t')
   ->join('AppBundle:Student','s')
   ->select(array('t', 's'))
   ->where('t.id=id and s.tid=tid')
   ->getQuery()
   ->getResult();
}

If there is a relationship defined between Teacher and Student in your entities as mentioned above you can even simplify the join:

$query = $em
   ->createQueryBuilder('t')
   ->from('AppBundle:Teacher','t')
   ->join('t.students', 's')
   ->select(array('t', 's'))
   ->getQuery()
   ->getResult();
}

Furthmore you do not need to call the from() method if you create the QueryBuilder object via the TeacherRepository object:

$query = $teacher
   ->createQueryBuilder('t')
   ->join('t.students', 's')
   ->select(array('t', 's'))
   ->getQuery()
   ->getResult();
}

Upvotes: 1

Alex Mac
Alex Mac

Reputation: 2993

Please check below mentioned solution.

$query = $em
       ->createQueryBuilder('t.*,s.*')
       ->from('AppBundle:Teacher','t')
       ->Join('AppBundle:Student','s')
       ->where('t.id=id and s.tid=tid')
       ->getQuery()
       ->getResult();
    }

Let me know if it not works.

Upvotes: 2

Related Questions