Laravel Eloquent Join with SUM()

December 8, 2021

I am trying to convert MySQL query to Laravel Eloquent, but it is throwing error.
The query is like this:

(
    SELECT
        p.id AS product_id,
        p.name AS product_name,
        SUM(s.quantity) AS product_quantity
    FROM
        products AS p
    INNER JOIN
        stocks AS s
        ON p.id = s.product_id
    GROUP BY
        p.id
);

>Solution :

DB::query()
    ->select(
        'p.id AS product_id',
        'p.name AS product_name',
    )
    ->selectRaw('SUM(s.quantity) AS product_quantity') // need to use selectRaw for aggregate values like this.
    ->from('products', 'p')
    ->join('stocks as s', 'p.id', 's.product_id')
    ->groupBy('p.id')
    ->get();

Using the syntax in your comment:

$this->model
     ->select("products.id as product_id", "products.name as product_name")
     ->selectRaw("SUM(quantity) as product_quantity") // select() doesn't work for aggregate values
     ->join("products", "products.id", "=", "stocks.product_id")
     ->groupBy("products.id")
     ->get()