CODEWITHSUNDEEP
mysqllaraveleloquentlaravel-relations
Ali Rasheed
Ali Rasheed

Reputation: 2815

laravel eloquent query with relations

I'm trying to replace a mysql query with laravel eloquent. This is my structure.

Consumers Table
---------------
id, email, name

Transactions Table
-------------------
id, consumer_id, value, bonus_value

Output that I'm trying to achieve

id, email, name, total_value
1, [email protected], Al, 11000
2, [email protected], Bl, 200000

This is what I have added in Consumer.php

/**
     * Transactions Relationship
     *
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function transactions(){
        return $this->hasMany(Transaction::class, 'consumer_id');
    }

And this is the query I've written so far.

$query = Consumer::query()
                ->join('consumer_transactions AS ct', function($q) {
                    $q->on('consumers.id', '=', 'ct.consumer_id')
                        ->where('ct.status', 'processed')
                        ->where('ct.approved', 1)
                        ->select(DB::raw('SUM(ct.value + ct.bonus_value) AS total_points'))
                    ;
                })
                ->whereIn('consumers.id', $consumerIds)
                ->get()
            ;

It doesn't return total_points

Upvotes: 0

Views: 369

Answers (2)

Bulent
Bulent

Reputation: 3411

I'm not fun of joining tables. That's why I can offer you a different approach.

$consumers = Consumer::whereIn('id', $consumerIds)
                ->with(['transactions' => function($query) {
                    $query
                       ->where('startus', 'processed')
                       ->where('approved', 1)
                }])->get()
                ->map(function($item, $key) {
                   return $item->total_points = $item->transactions->value + $item->transactions->bonus_value
                });

Upvotes: 1

OMR
OMR

Reputation: 12218

Join Clause that passed to your join take Illuminate\Database\Query\JoinClause witch doesn't have a method called 'select'.

you select should be out of join clause.

$query = Consumer::query()
                ->join('consumer_transactions AS ct', function($q) {
                    $q->on('consumers.id', '=', 'ct.consumer_id')
                        ->where('ct.status', 'processed')
                        ->where('ct.approved', 1);
                })
 ->select( ['consumers.*', DB::raw('SUM(ct.value + ct.bonus_value) AS total_points')])
                ->whereIn('consumers.id', $consumerIds)
                ->get();

Upvotes: 1

Related Questions