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 — user10686911
– user10686911, Commented Jan 21, 2019 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
– Dally, Commented Jan 21, 2019 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
– Vincent Decaux, Commented Jan 21, 2019 at 15:33

user10686911 · Accepted Answer · 2019-01-21 15:32:30Z

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, 2019 at 15:32

user10686911

Sign up to request clarification or add additional context in comments.

Comments

Vincent Decaux · Accepted Answer · 2019-01-21 15:45:08Z

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, 2019 at 15:45

Vincent Decaux

10.9k7 gold badges67 silver badges103 bronze badges

5 Comments

Dally Over a year ago

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.

Vincent Decaux Over a year ago

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.

Dally Over a year ago

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.

Vincent Decaux Over a year ago

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.

Dally Over a year ago

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?

Collectives™ on Stack Overflow

Laravel - Select specific columns from joined relationship models using Eloquent

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related