Laravel - Select specific columns from joined relationship models using Eloquent

Question

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;

Possible duplicate of [How to select columns from joined tables: laravel eloquent](https://stackoverflow.com/questions/45437607/how-to-select-columns-from-joined-tables-laravel-eloquent) — , Jan 21 '19 at 15:10
I've seen this but I've declared "with" in the models. Why would I need to declare it again in the query? — Dally, Jan 21 '19 at 15:17
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, Jan 21 '19 at 15:33

score 0 · Answer 1 · answered Jan 21 '19 at 15:32

0

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

answered Jan 21 '19 at 15:32

score 0 · Answer 2 · answered Jan 21 '19 at 15:45

0

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.

answered Jan 21 '19 at 15:45

Vincent Decaux

9,857
6
56
84

This is what I've done before and it's worked but I've declared "with" in the models themselves as a protected property so why would I do it again in the query. – Dally Jan 21 '19 at 15:59
Look my comment, Laravel will use multiple queries to get your `with` relations. So you can't query the columns, since they are not included in the global query. You must use the closures. – Vincent Decaux Jan 21 '19 at 16:01
If that's the case, why even have the feature of declaring with as a protected property in the model? You'll just be repeating yourself every time. – Dally Jan 21 '19 at 16:10
The `with` allows eager loading, and it will be convenient enough in 95% of the cases (just get relations data). What you can do is write your own relation function in your model to make it easy. – Vincent Decaux Jan 21 '19 at 16:11
What would I do if I wanted to include merchant.name in the select? Your code works but I want to be able to specify column names? – Dally Jan 21 '19 at 16:44

Laravel - Select specific columns from joined relationship models using Eloquent

2 Answers2