Join tables with with a condition and then aggregate columns - development

Join tables with with a condition and then aggregate columns

Given I have a simple join between two tables user and filled forms, filled forms has columns: value, sales and income.

Sales and income are boolean columns if any of them are set to true then I know which value are sales and which are income.

User::where('id', $id)
         ->leftJoin("filled_forms", function($join){

           $join->on("user.form_id", "filled_forms.id")
                ->where("filled_forms.sales", true)
                ->where("filled_forms.income", true);
          })->get();

Now all the fields that sales are set to true I want to
count(filled_forms.value) as number_of_sales and all the incomes
that are set to true I want to do sum(filled_forms.value) as income

I know that I could do something with DB::Raw maybe ?

from Laravel Questions and Answers https://laravelquestions.com/laravel/join-tables-with-with-a-condition-and-then-aggregate-columns/
via Lzo Media