Laravel 5.1 Querying Relationship

Question

Learning Laravel by building a stock portfolio app. Have models for Users, Accounts, Stocks, Options, and Transactions. I believe I have the relationships set up properly.

Question is how do I get a Users->Account->Transactions. I'm sure I could just do something in query builder but I was hoping for a more "eloquent" approach.

User

public function accounts()
{
    return $this->hasMany('App\Account');
}

public function stocks()
{
    return $this->belongsToMany('App\Stock');
}

public function options()
{
    return $this->belongsToMany('App\Option');
}

public function transactions()
{
    return $this->hasMany('App\Transaction');
}

Account

class Account extends Model
{
    protected $fillable = 
    [
        'name',
        'broker'

    ];

    public function user()
    {
        return $this->belongsTo('App\User');
    }

    public function stocks()
    {
        return $this->belongsToMany('App\Stock');
    }

    public function options()
    {
        return $this->belongsToMany('App\Option');
    }

    public function transactions()
    {
        return $this->hasMany('App\Transaction');
    }

Transaction

class Transaction extends Model
{
    protected $fillable = 
    [
        'type',
        'account_id',
        'transaction_date',
        'quantity',
        'stock_id',
        'option_id',
        'amount',
        'description'

    ];


    public function user()
    {
        return $this->belongsTo('App\User');
    }

    public function stock()
    {
        return $this->belongsTo('App\Stock');
    }

    public function option()
    {
        return $this->belongsTo('App\Option');
    }

    public function account()
    {
        return $this->belongsTo('App\Account');
    }

    public function accounts()
    {
        return $this->hasManyThrough('App\Account', 'App\User');
    }
}

Ultimately, I guess I would be looking for a total amount for each account a user may have. (User can have many accounts and each account would have many transactions, stocks and options.)

Thanks for any help or at least letting me know if I am going down the right road!!

Answer 1

If $user is a User object then $user->accounts is a collection of Eloquent models

You can load all of the accounts and their transactions with eager loading:

$user->load([
    'accounts',
    'accounts.transactions',
]);

But $user->accounts->transactions isn't something you can do because "transactions" is a relation on each individual Account object, not on the collection of account objects. So to get the transactions you'd loop through each account:

foreach ($user->accounts as $account) {
    // do something with $account->transactions;
}

I highly don't recommend doing this, as @alexw mentioned in his comment, Eloquent objects are pretty large and having x many x many is an exponentially large amount of data you're loading into memory. But generally, that's how you'd access relations of relations.

You're better off using the query builder

The good news is that relations can make querying really easy! For example, you could do something like this instead:

Note: in Laravel 5.2, the lists method has been replaced with pluck

$user->load(['accounts']);

foreach ($user->accounts as $account) {
    $amounts = $account->transactions()->lists('amount');
    $total = $amounts->sum();

    // - or -

    $query = $account->transactions()
        ->select(\DB::raw('SUM(amount) AS total'))
        ->first();
    $total = $query->total;
}