Laravel - Select specific columns from joined relationship models using Eloquent

0
votes

I'm trying to select specific columns from tables that I have joined using Eloquent.

I have 3 models - Transaction - Channel - Merchant

Transactions links to Channel. It has a hasOne relationship. Channel links to Merchant. It also has a hasOne relationship.

public function channel() {
    return $this->hasOne(Channel::class, 'uuid', 'entityId');
}

public function merchant() {
    return $this->hasOne('App\Merchant', 'uuid', 'sender');
}

I'm using eager loading so have the following in the Transaction model: 

protected $with = ['channel'];

And Channel has:

protected $with = ['merchant']:

This the query I'm trying to convert into Eloquent but I'm unsure how to select columns when they belong to related models. What I don't get is that if the relationships have been defined, why can't I select columns from the other models without having to reuse joins or the with clause?

SELECT SUM(t.amount) AS amount, 
       m.name 
FROM transactionsV2 t JOIN
     channels c
     ON t.entityId = c.uuid JOIN
     merchants m
     ON c.sender = m.uuid
WHERE t.paymentType = 'DB' AND
      t.status = 1 AND
      t.processing_time >= '2019-01-01' AND
      t.processing_time < '2019-01-21'
GROUP BY m.name;
2
laravellaravel-5eloquent
Possible duplicate of How to select columns from joined tables: laravel eloquent - Archie
I've seen this but I've declared "with" in the models. Why would I need to declare it again in the query? - Dally
Laravel uses mutiple queries to get relations, it's not using JOIN statement. That's why you can't use columns from related model so easily - Vincent Decaux

2 Answers

0
votes

You could do something like protected $with = ['merchant:id,name']; or maybe use raw expressions like selectRaw('SUM(t.amount) AS amount, m.name)

0
votes

You can try something like this :

Transaction::sum('amount')
    ->whereStuf(...)
    ->with(['channel.merchant' => function($query){
        $query->select('name')
            ->groupBy('name');
    }])->get();

The channel.merchant allows you to get the nested relation.