Use an append attribute in a eloquent condition

Question

this is my situation of my laravel project:

• I have 3 models: Travel, Activity and Expense.
• One Travel has many Activities and many Expenses.
• Model attributes:
  • Travel: id, title, desc, date, location
  • Activity: id, travel_id, title, desc, date, prize
  • Expense: id, travel_id, title, desc, prize
• The Travel model has an append attribute called 'total_prize', that is the sum of all Expenses and Activities that it has.

So far so good. My problem is when I try to apply the filter 'available_amount <= total_prize' to Travel's list.

How can I use the append attribute 'total_prize' for get all travels less than the amount_available?

• amount_available is a post request variable.

This is the accessor for total_prize attribute:

 public function getTotalPrizeAttribute()
    {
        return (float) $this->activities->sum('prize') + $this->expenses->sum('prize');
    }

I want to do something like this:

Travel::where('total_prize', '<=', 'amount_available')->get();

Answer 1

You have two choices:

• Get all travels and then filter them (not very efficient):

$travels = Travel::with('activities', 'expenses')->get()
    ->where('total_prize', '<=', $amount_available);

• Recreate getTotalPrizeAttribute() in the query:

$travels = Travel::withCount([
        'activities' => function($query) {
            $query->select(DB::raw('sum(prize)'));
        },
        'expenses' => function($query) {
            $query->select(DB::raw('sum(prize)'));
        }
    ])
    ->having(DB::raw('activities_count + expenses_count'), '<=', $amount_available)
    ->get();