How to write sub-query in eloquent

How do I perform this SQL query using Eloquent

SELECT * 
FROM  `bills` 
WHERE bills.amount = ( 
SELECT SUM( deposits.amount ) 
FROM deposits
WHERE deposits.bill_id = bills.id )

Bill Model:

class Bill extends Model
{
    //relations
    public function deposits()
    {
            return $this->hasMany(Deposit::class);
    }

    //scopes
    function scopeUpcoming($query) 
    {
            return $query->where('due_date', '>', Carbon::today());
    }

    function scopeDue($query) 
    {
        return $query->where('due_date', '<=', Carbon::today());
    }

    function scopeFulfilled($query) 
    {
        //code goes here...
    }
}

Deposit Model:

class Deposit extends Model
{
    //relations
    public function bill()
    {
        return $this->belongsTo(Bill::class);
    }
}

I want to do it in eloquent so I can do in the controller:

$upcoming_bills = Bill::upcoming()->fulfilled()->get();

bobbybouwmann

8 years ago

Level 88

This is the example from the documentation:

DB::table('users')
    ->whereExists(function ($query) {
        $query->select(DB::raw(1))
            ->from('orders')
            ->whereRaw('orders.user_id = users.id');
    })
    ->get();

Documentation: https://laravel.com/docs/5.6/queries#where-clauses

However I think you can do something like this

$bills = Bill::upcoming()->where('amount', function ($query) {
    $query->selectRaw('SUM(deposits.amount)')
        ->from(with(new Deposit)->getTable())
        ->where('bill_id', '=', 'bills.id')
})->get();

Note: I didn't test this, but I think it should work! At least I hope I put you in the right direction here!

ORLRO

8 years ago

Level 1

@bobbybouwmann I have tried your suggestion. the resultant SQL is:

select * 
from `bills` 
where `amount` = (
select SUM(deposits.amount) 
from `deposits` 
where `bill_id` = ?) 
and `bills`.`deleted_at` is null

didn't work :(

ORLRO

8 years ago

Best Answer

Level 1

Thanks @bobbybouwmann worked after a minor modification

function scopeFulfilled($query) 
    {
        return $query->where('amount', function ($q) {
            $q->selectRaw('SUM(deposits.amount)')
                ->from(with(new Deposit)->getTable())
                ->whereRaw('deposits.bill_id = bills.id');
        });
    }

Please or to participate in this conversation.