Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

StefanSchmalhaus's avatar

SQL to Query Builder Statement

Hi,

I have an SQL statement that I am struggling to convert to a Query Builder statement:

	SELECT IF(t2.sales != 0, (t1.sales/t2.sales - 1) * 100, NULL) AS diff
    FROM  (SELECT SUM(sales) AS sales
    FROM sales_user 
    WHERE year=2023 AND month=6 AND user_id=1) AS t1 
	JOIN (SELECT SUM(sales) AS sales
    FROM sales_user
    WHERE year=2022 AND month=6 AND user_id=1) AS t2
0 likes
3 replies
Osmar's avatar
Osmar
2 years ago
Best Answer
Level 2

I would rather prefer using the DB model but this is one way you can do it

you can use Db raw,

Illuminate\Support\Facades\DB;

$diff = DB::table(DB::raw('(SELECT SUM(sales) AS sales FROM sales_user WHERE year=2023 AND month=6 AND user_id=1) AS t1')) ->join(DB::raw('(SELECT SUM(sales) AS sales FROM sales_user WHERE year=2022 AND month=6 AND user_id=1) AS t2'), function ($join) { $join->on(DB::raw('1'), '=', DB::raw('1')); }) ->select(DB::raw('IF(t2.sales != 0, (t1.sales/t2.sales - 1) * 100, NULL) AS diff')) ->first();

// Accessing the result $diffValue = $diff->diff;

Hope this helps

1 like

Please or to participate in this conversation.