DB query builder or eloquent: relationships

If i understood the setup correctly i'd start like this

Lecturers

• id
• name

Subjects

• id
• name
• course_id

Courses

• id
• name

Students

• id
• name

Lecturer_Subject (A lecturer can teach many subject a subject can have many lecturers)

• lecturer_id
• subject_id

Course_Sudent (A student can be enrolled on many courses and a course can have many students)

• course_id
• student_id

Follow the models

class Lecturer extends Model 
{
    public function subjects()
    {
        return $this->belongsToMany('App\Subject', 'lecturer_subject', 'lecturer_id', 'subject_id');
    }
}

class Subject extends Model 
{
    public function course()
    {
        return $this->belongsTo('App\Course', 'course_id');
    }

    public function lecturer()
    {
        return $this->belongsToMany('App\Subject', 'lecturer_subject', 'subject_id', 'lecturer_id');
    }
}

class Course extends Model 
{
    public function subjects()
    {
        return $this->hasMany('App\Subject', 'course_id');
    }

    public function students()
    {
        return $this->belongsToMany('App\Student', 'course_student', 'course_id', 'student_id');
    }
}

class Student extends Model 
{
    public function courses()
    {
        return $this->belongsToMany('App\Course', 'course_student', 'student_id', 'course_id');
    }
}

Unless i missed something this should be it. Also i gave more readable names to the fields. All those abbreviations are just confusing in the long run.

1 .

$courses = Course::with('subjects')->find($course_id);

Now for the given course you have available $subjects->name

2.Not necessarily. If the student is enrolled to that course and the course contains that subject you don't need to.

3. You could but it's exhausting.

Let me know if it suffices and if you need further assistance :)

@sid405 How should I handle

*lecturer_subject (lecturer_id, subject_id)

*subject_offer(lecturer_id, subject_id, semester_id)

*enrollments(lecturer_id, subject_id, semester_id, student_id)

@jameshaishitan lecturer_subject is already handled above

$lecturer = Lecturer::with('subjects')->find($lecturer_id);
return $lecturer->subjects(); //returns the lecturer's subjects

//the inverse is also true
$subject = Subject::with('lecturer')->find($subject_id);
return $subject->lecturer(); //returns the subject's lecturer

For the other two i would need a bit more info regarding of logic of your application. Essentially speaking you don't want a table with more than 2 ids.

For the offers i would create a Semester table, then an Offers table that references the semester_id and the id of the lecturer_subject table.

Then for enrollments, i would link the offer_id field (from the table right above), with the student_id table.

From what i'm understanding, this should do. This would solve, but would not necessarily be the best approach. Seems a little too contorted in the planning stages.

I hope it helps.

@sid405 thanks.

I always got into a dilemma when comes to intermediate table which using multiple fields as a primary key. Let me try your way, it looks good.

About the Lecturer_Subject table, do you create its own controller and modal?

Can you explain a bit more when you says, "Seems a little too contorted in the planning stages.".

• "About the Lecturer_Subject table, do you create its own controller and modal?" In what sense? To associate them in an admin interface? Or to query it?

Either way doing something like this solves it both ways:

$lecturer = Lecturer->find($lecturer_id);
$lecturer->subjects()->save($subject_id);

//The inverse is also true

$subject = subject->find($subject_id);
$subject->lecturer()->save($lecturer_id);

Well, what i meant when i said contorted, is that when you end up with tables that have 3 ids, then it can definitely be simplified. But then again, this is a domain-specific problem. Maybe there's no way to simplify it further.

:)

@sid405 On Lecturer_Subject, I don't know how to categorise them, but if I wanted to do something like below how should I go about to do it.

|-------------------|---------------------|

|Lecture ID | Lecturer ABC |

|-------------------|---------------------|

|Course ID----|Course Name-|Subject ID--|Subject Name|

|CRS1 |My Crouse |Math | Mathematic A|

From your above mentioned, I am able to get the Lecturer and subjects. How to get the courses link to each subject. Lecturer->Lecturer_Subject->Subject->Course.

Lecturer.php
 public function subjects()
    {
        return $this->belongsToMany('App\Subject', 'lecturer_subject', 'lecturer_id', 'subject_id')->withTimestamps();;
    }

Thanks.

@jameshaishitan No need to reference a pivot model 'Lecturer_Subject'

Why we build these relationships in eloquent is to specifically avoid that.

You can do it Lecturer->Subject->Course because we have defined relations between models.

You might wanna keep this page for reference http://laravel.com/docs/5.1/eloquent-relationships and of course http://laravel.com/docs/5.1/eloquent-relationships#eager-loading

But since we're here

1. Fetching a subject and all it's courses

$subject = Subject::with('course')->find($subject_id); //returns a Subject collection with Courses for each

2. Fetching courses for a subject

$courses = Course::whereHas('subject', function($query) use ($subject_id){
        $query->where('id', $subject_id);
    })->get(); // Returns a collection of courses

3. Fetching a lecturer, with subjects and courses

$lecturer = Lecturer::with('subject.course')->find($lecturer_id); 

4. Fetching courses for a lecturer

$courses = Course::whereHas('subject.lecturer', function($query) use ($lecturer_id){
        $query->where('id', $lecturer_id);
    })->get(); // Returns a collection of courses

You see where this is going.

But again, have the relationship documentation open for reference.

Once 'it clicks' in your mind, you'll be good to go.

Best.

sid

@sid405 Thanks a lot.

Yes, that document is always on. I have read it many times. May be it doesn't click me until you point out here. I read about the eagle load. Now it makes sense.

@jameshaishitan Glad to be of help.