What's the best Yii way of getting data from 2 tables?

Question

I want to get data based on 2 db tables

There is:

 course table
 student_in_course table (with foreign key course_id)

I would like to get all course.name

based on student_in_course.course_id for a specific student_in_course.student_id

What's the best practice of doing it with ActiveRecord (or other recommended way)?

Thanks in advance

Answer 1

Yii2 documentation suggests to use 'joinWith' in case you need to perform a left join query with Active record. So in your case you'd want something like this:

$courses = Course::find()
  ->select('course.name')
  ->joinWith('student_in_course')
  ->where(['student_in_course.student_id' => $student_id])
  ->all();

Please refer to Yii2 official docs.

Answer 2

Yes, ActiveRecord is what I would prefer doing it with,but the thing is if you are going to display in GridView or ListView using activeDataProvider sometime you might need to update/ adjust the query in serachModel rather than writing a separate function with a query in the model or as some people do write inside controller's action, until unless if you are using a custom view and displaying it manually and want the result set as an array or activedataprovider object to iterate on it and display records then the answer suggested by @GiulioG is applicable. But the thing to be noticed in both the scenarios is that you should define appropriate relations and you do not need to use joins manually.

Answer 3

First of all, ActiveRecord is the best approach if you are going to work with YII, seems like you are going to use a cross-reference table use via() or viaTable().

class Student extends ActiveRecord{

     public function getStudentsInCourses() {
          return $this->hasMany(StudentInCourses::className(), ['student_id' => 'id']);
      }

     public function getCourses() {
          return $this->hasMany(Course::className(), ['id' => course_id'])
                   ->via('studentsInCourses');
     }
}