Level 102
You only have two tables but use belongsToMany. This requires a pivot table. Did you want to use a hasMany perhaps?
1 like
Nov 26, 2022
9
Level 1
Get data in eloquent "withSum()"
I have tried to use withSum according to the documentation. But I think I am making a mistake in thinking.
I have two tables wallet_data wallet_data_transaction (with key wallet_data_id)
I have try this:
$wallet_datas = WalletData::where('....', 0)
->withSum('wallet_data_transactions', 'fees')
->get()
And in my WalletData Model:
public function wallet_data_transactions()
{
return $this->belongsToMany(WalletDataTransaction::class, 'wallet_data_transactions'');
}
The Error
SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'wallet_data_transactions'
It is clear what the error means, but not where it is triggered. What have I misunderstood?
I am a little confused why Laravel generates this query:
select wallet_datas.*,
(
select sum(wallet_data_transactions.fees)
from wallet_data_transactions
inner join wallet_data_transactions on wallet_data_transactions.id = wallet_data_transactions.wallet_data_transaction_id
where wallet_datas.id = wallet_data_transactions.wallet_data_id
) as wallet_data_transactions_sum_complete_fees
from wallet_datas where ......
Why is there an inner join?
Level 1
...... yes. Than kyou. hasMany not belongsToManny...
Level 1
@sinnbeck sorry another question about this
Is there a possibility to make further calculations from "withSum"? I tried a example:
$wallet_datas = WalletData::where('....', 0)
->select('wallet_datas.*', DB::raw('wallet_datas.price - (wallet_datas.price_ws + wallet_datas.complete_fees) AS calculated_price'))
->withSum('wallet_data_transactions', 'fees')
->when((!empty($sort_field) && $sort_field == 'calculated_price'), function ($query) use ($sort_order) {
$query->orderBy(DB::raw('wallet_datas.price - (wallet_datas.price_ws + wallet_datas.complete_fees)'), $sort_order);
})
->get()
Level 102
@KodaC you mean sort by the result or?
Level 1
@Sinnbeck Select the result and sort y result. When I triy this i get an error because calculated_price was not found
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'wallet_data.complete_fees' in 'field list'
Level 102
@KodaC you can name the sum result
->withSum('wallet_data_transactions as total', 'fees')
->orderBy('total')
Level 1
@Sinnbeck Sorry, I think I expressed myself wrongly. English is not my native language
The problem was in the part of the additional calculation and order from this part:
DB::raw('wallet_datas.price - (wallet_datas.price_ws + wallet_datas.complete_fees) AS calculated_price')
Level 102
@KodaC you want to order by that part? Not sure how this is related to the original question?
->orderBy('calculated_price')
Level 1
Thank you. You are right. I will make a separate topic.
Please or to participate in this conversation.