Laravel groupby to get sum but only based on another columns value-CodePudding

I have multiple joins that I am doing to get one list. I can group by a certain value to get a sum but need to merge another column that is not being grouped by. My tables look kinda like this (simplified)

Quote Table

id|Name          |Location       |
---------------------------------
1|My First Quote|123 Main Street|

Quote Items table

quote_id|  item_id
--------------------
1       | 1
--------------------
1       | 2

Items Table

id|Name    |Quantity|Type
--------------------------------
1|Dog Food|2       |product
2|Delivery|1       |service

I need to show a final result like this in datatables

Name             Location         Qty  Item
My First Quote | 123 Main Street | 2 | Dog Food

So basically i need to sum on item.quantity and show the product type as item. I have the group by working with a raw sum on the item grouped by item.id where item type is product but the where gets rid of the item service that i need to show on the row.

CodePudding user response：

You can try this:

DB::table("Quote")
    ->select('Quote.Name','Quote.Location')
    ->(['Items.Quantity AS Qty','Items.Name AS Item'])
    ->join("Items","Items.item_id","=","Quote.quote_id")
    ->get();

CodePudding user response：

You can define a relationship for quotes table

<?php
 
namespace App\Models;
 
use Illuminate\Database\Eloquent\Model;
 
class Quote extends Model
{

    public function items()
    {
        return $this->belongsToMany(Item::class);
    }
}

after that, call a sum method with that relation for the column you want.

Quote::withSum('items','Quantity')->get();