Get the sum of all payments earnings that belongs to author's books?

User → has many → Book → has many → Order → has many Payment

chema::create('orders', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('payer_id');
            $table->unsignedBigInteger('book_id');
            $table->unsignedInteger('amount');

            $table->foreign('payer_id')->references('id')->on('users');
            $table->foreign('book_id')->references('id')->on('books');
        });

 Schema::create('payments', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('order_id');
            $table->string('payment_method');
            $table->unsignedInteger('author_earning');

            $table->foreign('order_id')->references('id')->on('orders');
        });
    }

How can I list all users with the sum of author_earning for all payments that belongs to their books.

I have tried many things with no luck, I am pretty sure I am doing something wrong.

jlrdw

6 years ago

Level 75

https://www.mysqltutorial.org/mysql-sum/ Is a good tutorial

And

https://laravel.com/docs/6.x/queries#aggregates

Example aggregate usage:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

You just need to practice and work with sum. These types of queries can take a little "trial and error"

Please or to participate in this conversation.