Viewed   114 times

Like we can eager load a relationship of an Eloquent model, is there any way to eager load a method which is not a relationship method of the Eloquent model?

For example, I have an Eloquent model GradeReport and it has the following method:

public function totalScore()
    return $scores = DB::table('grade_report_scores')->where('grade_report_id', $this->id)->sum('score');

Now I am getting a collection of GradeReport Eloquent models.

$gradeReports = GradeReport::where('student_id', $studentId)->get();

How can I eager load the returning values of totalScore method for all GradeReport Eloquent models in the collection?



You can add arbitrary properties to your models by adding them to $appends array and providing a getter. In your case the following should do the trick:

class GradeReport extends Model {
  protected $appends = ['totalScore'];

  public function getTotalScoreAttribute() {
    return $scores = DB::table('grade_report_scores')->where('grade_report_id', $this->id)->sum('score');

Now all GradeReport objects returned from your controllers will have totalScore attribute set.

Tuesday, August 2, 2022

It's mostly raw queries:

DB::table('item_details')->selectRaw('GROUP_CONCAT(...) INTO @sql')->get();
DB::statement('SET @sql = CONCAT(...)');
DB::statement('PREPARE stmt FROM @sql');
DB::statement('EXECUTE stmt');
DB::statement('DEALLOCATE PREPARE stmt');

Try this:

DB::table('item_details')->selectRaw('GROUP_CONCAT(...) INTO @sql')->get();
$sql = DB::selectOne('select @sql')->{'@sql'};
ItemDetails::select('item_number', DB::raw('SUM(quantity) as total_quantity'))
Sunday, October 23, 2022

Okay so you're trying to get a single user but with their players (where users bTM players) already populated and ordered by position (where pivot bT position).

In this case you will not be able to use Laravel's inbuilt relationship methods without modification, because Laravel just wasn't built to hand relationships that are on the pivot table of other relationships. Luckily, the ORM is flexible enough to allow you to do what you want with a 'manual' join.

So to answer your question directly, here's the kind of code you require (you were really close!):

$user = User::with(['players' => function ($q) {
    $q->join('position', 'league_player_user.position_id', '=', '')
      ->orderBy('position.sort_id', 'asc');
}])->where('id', Auth::user()->id)->first();

However, it appears to me that that's not great code for a few reasons:

  1. You're manually getting the authorised user (when Auth::user() is so convenient)
  2. You're actually having to take implementation-specific logic from the model (the fact that the pivot table is called league_player_user and put it... well wherever this is (your controller?)
  3. This will only affect this one single query - if you happened to get a User some other way (e.g. Auth::user() or User::find(1) or whatever) you won't have the players ordered correctly

As such, might I suggest that for the purposes of making your query more simple you don't eager load the players. As such, all you'll need to do upfront is:

$user = Auth::user();

Now, on the relationship (the players() method in your User class) you do the special ordering work:

public function players()
    return $this->belongsToMany('AppModelsPlayer', 'league_player_user')
                ->join('position', 'league_player_user.position_id', '=', '')

This way, any time you call $user->players, you will get them ordered correctly.

I must just add that doing it this way may not allow you to eager load the players, as Laravel's eager loading (i.e. using ->with() in the ORM chain) due to the way that Laravel does the eager loading queries - one for main query (i.e. users) and one for the relationship (i.e. players) but it does this query to get all results, so won't work with out special ordering system possibly. You'll have to see if you really care about eager loading the players. In your case above (where you're getting the single authorised user), eager loading is not as important in my opinion.

Edit to add clarification regarding eager loading:

My reasoning behind suggesting that eager loading may not work is that eager loading in Laravel is done kinda like this: say you have categories and products: Category hM Product, Product bT Category. To get a category you use $category = Category::find(1) and Laravel turns that into a query a bit like this: SELECT * FROM `categories` WHERE `id` = '1'. If you then call $category->products Laravel will issue SELECT * FROM `products` WHERE `category_id` = '1'. That's sensible. But if you had the following code it'd be worse:

<?php $categories = Category::all(); ?>

    @foreach ($categories as $category)
        <li>Category {{{ $category->name }}} contains {{{ $category->products->count() }}} products.</li>

In that case you'd have the following queries:

SELECT * FROM `categories`;
SELECT * FROM `products` WHERE `category_id` = '1';
SELECT * FROM `products` WHERE `category_id` = '2';
SELECT * FROM `products` WHERE `category_id` = '3';
... as many categories as you had

However, if you were to change that first line to this:

<?php $categories = Category::with('products')->get(); ?>

Now you'd only have two queries:

SELECT * FROM `categories`;
SELECT * FROM `products` WHERE `category_id` IN ('1', '2', '3', ...);

Laravel then, after calling the second query, will create the various collections for you based on the category IDs it knows you have.

However, that's the simplistic relationship case. In your case, the products() method is not just return $this->hasMany('Product'); but it includes a pivot and a manual join, etc., and it's possible that that second query, which is the eager loading one, just won't be able to cope and do that ordering properly.

As I said, I don't know for certain how this works, it's just a bit of a red flag for me. By all means give it a go and see what you get - you may find that it works for you.

Sunday, November 13, 2022

This can be done in (at least) 2 ways.

Using pure Eloquent model logic:

class Buy extends Model
  public function getTotalPrice() {
    return $this->buyDetails->sum(function($buyDetail) {
      return $buyDetail->quantity * $buyDetail->price;

The only issue here is that it needs to fetch all buy details from the database but this is something you need to fetch anyway to display details in the view.

If you wanted to avoid fetching the relation from the database you could build the query manually:

class Buy extends Model
  public function getTotalPrice() {
    return $this->buyDetails()->sum(DB::raw('quantity * price'));
Thursday, September 1, 2022

This is because you should use with when you don't have object yet (you are making query), and when you already have an object you should use load.


Collection of users:

$users = User::with('profile')->get();


$users = User::all();

Single user:

$user = User::with('profile')->where('email','')->first();


$user = User::where('email','')->first();

Methods implementation in Laravel

Also you can look at with method implementation:

public static function with($relations)
    return (new static)->newQuery()->with(
        is_string($relations) ? func_get_args() : $relations

so it's starting new query so in fact it won't execute the query until you use get, first and so on where is load implementation is like this:

public function load($relations)
    $query = $this->newQuery()->with(
        is_string($relations) ? func_get_args() : $relations


    return $this;

so it's returning the same object, but it load relationship for this object.

Saturday, September 24, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :