CODEWITHSUNDEEP
phplaravel
sc1013
sc1013

Reputation: 1126

Laravel 4 Eager Loading Constraint

Suppose I have two models:

class Faculty {
    $primaryKey = 'FacultyID';

    public function professors()
    {
        return $this->hasMany('Professor', 'FacultyID', 'FacultyID');
    }
}

class Professor {
    $primaryKey = 'ProfessorID';

    public function faculty()
    {
        return $this->belongsTo('Faculty', 'FacultyID', 'FacultyID');
    }
}

where a faculty has many professors and a professor only belongs to one faculty.

Data in Faculty:

FacultyID | FacultyName

    1         Engineering
    2         Business

Data in Professor:

ProfessorID | FacultyID | ProfessorName

       1         1         A
       2         1         B
       3         1         C
       4         2         D

I have a page that lists all professors in a table (showing Faculty name, Professor name, etc. in a row) I would also like to let user sort the table by Faculty name. Therefore I write the following code:

$professors = Professor::with(array('faculty' => function($query) {
    $query->orderBy('FacultyName', 'desc');
}));

However, the sorting is not correct. So I tried to debug by replacing the closure to the following to see if constraint above is correct:

$query->where('FacultyID', '=', '2');

I would expect to see there is only ONE professor in the result according to the data in the tables. But the result is it still lists ALL professors.

Why? What should I do in order to list all professors with its FacultyName showing in descending order?

Thanks!

Upvotes: 0

Views: 53

Answers (1)

patricus
patricus

Reputation: 62278

The with method is eager loading the records in your relationship. It is a second query that is run using the ids retrieved from the main query. It does not perform a join.

So, when you do Professor::with('faculty')->get(), it is running two sql statements. Your attempts produced the following approximate SQL queries:

Professor::with('faculty')->get();
// select * from professors;
// select * from faculties where FacultyID in (?);

Professor::with(array('faculty' => function($query) {
    $query->orderBy('FacultyName', 'desc');
}));
// select * from professors;
// select * from faculties where FacultyID in (?) order by FacultyName desc;

Professor::with(array('faculty' => function($query) {
    $query->where('FacultyID', '=', '2');
}));
// select * from professors;
// select * from faculties where FacultyID in (?) and FacultyID = 2;

As you can see, the conditions on the relationship don't affect the query for the parent table at all.

In order to be able to sort on the related field, you'll need to ->join the tables to create one SQL statement, which can be ordered:

$professors = Professor::join('faculties', 'professors.FacultyID', '=', 'faculties.FacultyID`)
    ->orderBy('faculties.FacultyName', 'desc')
    ->get();
// select * from professors
// inner join faculties
// on professors.FacultyID = faculties.FacultyID
// order by FacultyName desc;

It is a bit more of a manual process, but it can be somewhat generalized. I'd suggest creating a scope on your model, and using methods available on the models and relations to replace the hard coded values. But, make sure you can get it working and understand how it works before getting into all that.

Upvotes: 1

Related Questions