Make eloquent model for subtype.

Read about Global Scopes in the docs:

https://laravel.com/docs/5.5/eloquent#global-scopes

You could use an anonymous global scope:

<?php

namespace App;

class Student extends User
{
    protected $table= 'users';

    public function enrollments(){
        return $this->belongsToMany(Enrollment::class);
    }

    protected static function boot()
    {
        parent::boot();

        static::addGlobalScope('student', function (Builder $builder) {
            $builder->whereExists(function ($query) {
                $query->select(DB::raw(1))
                      ->from('enrollments')
                      ->whereRaw('enrollments.user_id = users.id');
            });
        });
    }
}

It will be applied automatically to any select statement from this model.

@rodrigo.pedra Thank you! This did the trick, only had to add use Illuminate\Support\Facades\DB; .

Could you explain your query starting from the builder, because I find it a little complex to understand at the moment.

I missed the DB Façade, sorry.

The whereExists add a subquery using the SQL's EXISTS operator.

Inside its closure you basically builds a new select query to be executed by the EXISTS operator, it returns true with its related subquery returns at least one record from the database.

Before if you do something like this:

$students = Student::all();

Eloquent underlying query builder would build the following SQL query:

SELECT * 
FROM students

Now with the added global scope, the same call (Student::all()) will generate the following SQL query:

SELECT * 
FROM students
WHERE EXISTS (
    SELECT 1
    FROM enrollments
    WHERE enrollments.user_id = users.id
)

The \DB::raw(...) and the ->whereRaw(...) methods tell Eloquent to not try to parse their arguments values and use them as provided (raw).

As the SQL's EXISTS operator checks if a record exists, we don't need to retrieve any columns from the subquery, that is why I used ->select( DB::raw(1) )

You can read more about the ->whereExists(...)on the Laravel docs:

https://laravel.com/docs/5.5/queries#where-exists-clauses

And more about the EXISTS operator in any DBMS docs, I am linking MySQL's for reference:

https://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html

@rodrigo.pedra Thank you for comprehensive explanation, I learned a lot today :)

@rodrigo.pedra Sorry for the follow-up question, but I now have the problem that I cannot execute the query to get the students with their enrollment $students = Student::with('enrollments')->get();.

I found out that i could tweak this relationship until the query is correct, like this public function enrollments(){return $this->belongsToMany(Enrollment::class,'users','id','id','doesntmatter','user_id'); }

This let me execute the query, but returns students with empty collections of enrollments (if I execute the query in the DB I get the enrollments correct).

My Student model currently looks like this:

<?php

namespace App;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

class Student extends User
{

    protected $table= 'users';
    protected $primaryKey = 'user_id';

    protected static function boot()
    {
        parent::boot();

        static::addGlobalScope('student', function (Builder $builder) {
            $builder->whereExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('enrollments')
                    ->whereRaw('enrollments.user_id = users.id');
            });
        });
    }

    public function enrollments(){
        return $this->belongsToMany(Enrollment::class,'users','id','id','doesntmatter','user_id');
    }

    public function currentStudentsWithClass(){
        Student::with($this->enrollments());
    }

}

I took a look in your EER and it seems Enrolment should be a pivot between User (Student) and SchoolClass.

Anyway, if we consider that one Student has many Enrolment, you need to use the correct relation ship between them.

I built the users and enrollments based on your EER and wrote the Student and Enrollment models to test it out.

I made some comments in the code:

<?php

namespace App;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

class Student extends User
{
    protected $table = 'users';

    // **********************************************************************
    // don't do this, your primary key by your EER is id
    // I understand you tried to change it to make the relationship work
    // protected $primaryKey = 'user_id';

    protected static function boot()
    {
        parent::boot();

        static::addGlobalScope( 'student', function ( Builder $builder ) {
            $builder->whereExists( function ( $query ) {
                $query->select( DB::raw( 1 ) )
                    ->from( 'enrollments' )
                    ->whereRaw( 'enrollments.user_id = users.id' );
            } );
        } );
    }

    public function enrollments()
    {
        // **********************************************************************
        // by your EER I read that users has many enrollments,
        // actually 'enrollments' should be a pivot table between Students and
        // Classes, read about using pivot tables in a many-to-many
        // relationship in:
        // https://laravel.com/docs/5.5/eloquent-relationships#many-to-many
        return $this->hasMany( Enrollment::class );
    }

    public function getForeignKey()
    {
        // **********************************************************************
        // I think this is what you are trying to do when you changed
        // the $primaryKey

        // by default, Eloquent will try to guess the foreign key
        // for a model from its class name

        // so we need to manually override this
        return 'user_id';
    }

    public static function currentStudentsWithClass()
    {
        // I guess this is what you meant
        return Student::with( 'enrollments' )->get();
    }
}

And I tested with the following route:

Route::get( '/test', function () {
    return \App\Student::with( 'enrollments' )->get();
} );

That return only the users who are students and their related enrolments.

I don't know your project requirement, but I would change the EER a bit, for example I would create a pivot between school_classes and schollyears called offerings and make the enrollments table a pivot between offerings and users. In this case you will need a dedicated id in the offerings table.

Anyway, read about using pivot tables, it will help you a lot.

@rodrigo.pedra I changed my EER like suggested. EER

Now I got everything back working with your suggestions in mind, but when fetching the enrollments ($students = Student::with('enrollments')->get()) of each student it still returns an empty collection.

Student.php:

<?php

namespace App;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

class Student extends User
{

    protected $table= 'users';
    protected $primaryKey = 'user_id';
    protected $guard_name = 'web';

    public function getForeignKey()
    {
        return 'user_id';
    }

    protected static function boot()
    {
        parent::boot();

        static::addGlobalScope('student', function (Builder $builder) {
            $builder->whereExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('enrollments')
                    ->whereRaw('enrollments.user_id = users.id');
            });
        });
    }

    public function enrollments(){
        return $this->hasMany( Enrollment::class );
    }

    public function currentStudentsWithClass(){
        Student::with('enrollments')->get();
    }

}

Enrollment.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Enrollment extends Model
{
    public function student(){
        return $this->belongsTo(Student::class,'user_id','user_id');
    }

}

Do not change the $primaryKey on the Student model, it’s primary key should be the `'i field. As it extends the User model it should keep the same primary key.

Also on the Enrollment model drop the third parameter on the student relation or change it to 'id'.

@rodrigo.pedra Allright I got that working :). I read more about the eloquent relationships and I built my models like I made my relations.

So with my EER in mind I made these models:

Student.php

<?php

namespace App;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

class Student extends User
{

    protected $table= 'users';
    protected $guard_name = 'web';

    public function getForeignKey()
    {
        return 'user_id';
    }

    protected static function boot()
    {
        parent::boot();

        static::addGlobalScope('student', function (Builder $builder) {
            $builder->whereExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('enrollments')
                    ->whereRaw('enrollments.user_id = users.id');
            });
        });
    }

    public function enrollments(){
        return $this->hasMany( Enrollment::class );
    }

    public function offerings(){
        return $this->belongsToMany(Offering::class,'enrollments');
    }

}

Enrollment.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Enrollment extends Model
{
    public function student(){
        return $this->belongsTo(Student::class,'user_id');
    }

    public function offering(){
        return $this->belongsTo(Offering::class);
    }

}

Offering.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Offering extends Model
{
    public function schoolClasses(){
        return $this->belongsTo(SchoolClass::class);
    }

    public function schoolyears(){
        return $this->belongsTo(Schoolyear::class);
    }

    public function enrollments(){
        return $this->hasMany(Enrollment::class);
    }

    public function students(){
        return $this->belongsToMany(Student::class,'enrollments');
    }
}

Schoolyear.php

<?php

namespace App;


class Schoolyear extends Model
{
    public $timestamps =false;

    public function offerings(){
        return $this->hasMany(Offering::class);
    }
}

SchoolClass.php

<?php

namespace App;

class SchoolClass extends Model
{
    public $timestamps=false;

    public function offering(){
        return $this->hasMany(Offering::class);
    }

    public function grade(){
        return $this->belongsTo(Grade::class);
    }
}

Grades.php

<?php

namespace App;


class Grade extends Model
{
    public $timestamps=false;

    public function grades(){
        return $this->hasMany(SchoolClass::class);
    }
}

I think these relationships do follow my EER, or am I wrong?

Ideally I would like to do some queries now like: -fetching all students for this year (school_id) with their class name + grade. -Additionally I would also like to fetch all students from a certain class within a school year.

So far I tried these queries, but none with succes:

Fetching all students with grade name and class name for all years.

$students = Student::with('offerings.schoolClasses.grades')->get();

this does return the offering with the class_id and schoolyear_id but I cant get any further, like getting the grade and name of the class and fetching the schoolyear.

This I tried to filter on schoolyear id, but didn't work.

$students = Student::with(['offerings.schoolClasses.grades'=>function($query){
                $query->where('schoolyear.id',2);
            }])->get();

I already want to thank you for all your help and things that I learned from you :)