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

divanoli's avatar

Help needed with a Complext Query in Database Query Builder

Hello,

Is there any way to run SELECT on a sub query with multiple where conditions along with multiple joins? Here is my query:

SELECT totalS.date AS finalDate, IFNULL(totalS.total_sales,0) AS finalSales, IFNULL(totalP.total_payments,0) AS finalPayments
FROM
(   
    SELECT c.datefield AS date, IFNULL(SUM(a.epoints),0) AS total_sales
    FROM accounts AS a
    RIGHT JOIN calendar AS c ON (DATE(FROM_UNIXTIME(jtime)) = c.datefield)
    WHERE (
            c.datefield BETWEEN
                (SELECT MIN(DATE(FROM_UNIXTIME(jtime))) FROM accounts) 
                AND
                (SELECT MAX(DATE(FROM_UNIXTIME(jtime))) FROM accounts)
            )
            AND
            a.source=3 AND a.status=0
    GROUP BY date
    ORDER BY date DESC

) AS totalS
LEFT JOIN
(
    SELECT c.datefield AS date, IFNULL(SUM(a.epoints),0) AS total_payments
    FROM accounts AS a
    RIGHT JOIN calendar AS c ON (DATE(FROM_UNIXTIME(jtime)) = c.datefield)
    WHERE (
            c.datefield BETWEEN
                (SELECT MIN(DATE(FROM_UNIXTIME(jtime))) FROM accounts)
                AND
                (SELECT MAX(DATE(FROM_UNIXTIME(jtime))) FROM accounts)
            )
            AND
            a.source=1 AND a.status=0 AND fromid='ADMIN'
    GROUP BY date
    ORDER BY date DESC

) AS totalP ON (totalS.date=totalP.date) 
WHERE totalS.date BETWEEN '2015-01-15' AND '2015-01-26'
ORDER BY finalDate ASC;

How do i build this query with Laravel Query Builder? Thanks in advance.

NOTE: I don't want to use Eloquent.

0 likes
3 replies
JarekTkaczyk's avatar

It's easy, just a bunch of DB::raw(..), toSql(), setBindings() and that's all, just translate it according to the docs...

1 like
JarekTkaczyk's avatar
Level 53

@divanoli well, this is how you do it:

$subOne = DB::table(..)->selectRaw('sum(whatever) as my_sum, ...')-> ... ; // your joins, where etc
$subTwo = ... // like above

DB::table(DB::raw( "( {$subOne->toSql()} ) as totalS" ))
   ->mergeBindings($subOne)
   ->leftJoin(DB::raw( "( {$subTwo->toSql()} ) as totalP" ), 'totalS.date', '=', 'totalP.date')
   ->mergeBindings($subTwo)
   ->selectRaw(..)
   ->whereBetween(..)
   ->OrderBy(..)
   ->get();
1 like

Please or to participate in this conversation.