Is it possible to eager load arbitrary queries in Eloquent?

Question

I'm working in Laravel 4, and I have a Child model with multiple EducationProfiles:

class Child extends EloquentVersioned 
{
public function educationProfiles()
    {
        return $this->hasMany('EducationProfile');
    }
}

If I wanted to get all the EducationProfiles for each kid under age 10 it would be easy:

Child::where('date_of_birth','>','2004-03-27')->with('educationProfiles')->all();

But say (as I do) that I would like to use with() to grab a calculated value for the Education Profiles of each of those kids, something like:

SELECT `education_profiles`.`child_id`, GROUP_CONCAT(`education_profiles`.`district`) as `district_list`

In theory with() only works with relationships, so do I have any options for associating the district_list fields to my Child models?

EDIT: Actually, I was wondering whether with('educationProfiles') generates SQL equivalent to:

EducationProfile::whereIn('id',array(1,2,3,4))

or whether it's actually equivalent to

DB::table('education_profiles')->whereIn('id',array(1,2,3,4))

The reason I ask is that in the former I'm getting models, if it's the latter I'm getting unmodeled data, and thus I can probably mess it up as much as I want. I assume with() generates an additional set models, though. Anybody care to correct or confirm?

Yep, just tested, with() generates SQL equivalent to EducationProfile::, not DB::table . Bummer. — dspitzle, Mar 27 '14 at 19:25

dspitzle · Accepted Answer · 2014-03-27T20:29:30.603

Ok, I think I've cracked this nut. No, it is NOT possible to eager load arbitrary queries. However, the tools have been provided by the Fluent query builder to make it relatively easy to replicate eager loading manually.

First, we leverage the original query:

$query = Child::where('date_of_birth','>','2004-03-27')->with('educationProfiles');
$children = $query->get();
$eagerIds = $query->lists('id');

Next, use the $eagerIds to filterDB::table('education_profile') in the same way that with('educationProfiles') would filter EducationProfile::...

$query2 = DB::table('education_profile')->whereIn('child_id',$eagerIds)->select('child_id', 'GROUP_CONCAT(`education_profiles`.`district`) as `district_list`')->groupBy('child_id');
$educationProfiles = $query2->lists('district_list','child_id');

Now we can iterate through $children and just look up the $educationProfiles[$children->id] values for each entry.

Ok, yes, it's an obvious construction, but I haven't seen it laid out explicitly anywhere before as a means of eager loading arbitrary calculations.

score -1 · Answer 2 · answered Oct 10 '17 at 21:53

You can add a where clause to your hasMany() call like this:

public function educationProfilesUnderTen() {
    $ten_years_ago = (new DateTime('10 years ago'))->format('Y-m-d');
    return $this->hasMany('EducationProfile')->where('date_of_birth', '>', $ten_years_ago)
}

Is it possible to eager load arbitrary queries in Eloquent?

2 Answers2