CODEWITHSUNDEEP
phplaraveljoineloquent
Hardist
Hardist

Reputation: 1983

Laravel Raw and Eloquent Queries

I've got a table transactions which has a paymentable_id and paymentable_type column, for polymorphic relations. I have a Plan model, used for payment plans. Whenever a transaction for that plan is made, the transactions will be related to that plan, using the polymorphic relation.

Now, I want to fetch all Plans from the database, including a field called total_amount. The total_amount field should include the sum of all transactions.amount related for that plan.

So far, and with help from another question I asked, I came up with the following:

$plans = App\Models\Plan::all('*', DB::RAW("(SELECT SUM(amount) FROM transactions WHERE paymentable_id = plans.id AND paymentable_type = 'App\\\\Models\\\\Plan' ) as total_amount") );

Which works totally fine. However, I feel this is just plain wrong:

WHERE paymentable_id = plans.id AND paymentable_type = 'App\\\\Models\\\\Plan'

Also, the need to specify the paymentable_id AND paymentable_type seems wrong to me. I need to be doing this for a lot of other queries as well, since the polymorphic relation can contain up to 5 different models.

Question 1. Is the above a correct query to use and if so, is there perhaps also a "better way" to create that query?

Question 2. The below query also works but I perhaps do not understand this part:

->join('transactions', 'plans.id', '=', 'transactions.paymentable_id')

Whenever I include the ->where('transactions.paymentable_id', '=', 'plans.id'), it works as expected. But without that part, it basically ignores the paymentable_id.

$plans = DB::table('plans')->join('transactions', 'plans.id', '=', 'transactions.paymentable_id')
                           ->where('transactions.paymentable_type', '=', 'App\\Models\\Plan')
                           ->get([DB::raw('sum(transactions.amount) as total_amount')]);

Any pointers are much appreciated.

Upvotes: 0

Views: 432

Answers (1)

vali ntin
vali ntin

Reputation: 40

i do not know you data base 

$plans = DB::table('plans')->join('transactions', 'plans.id', '=', 'transactions.paymentable_id')
                       ->where('transactions.paymentable_type', '=', 'you_plan_type')
                       ->select('plans.*','transactions.*')
                       ->selectRaw('sum(transactions.amount) as total_amount')
                       ->get();

Upvotes: 1

Related Questions