Level 53
It's easy, just a bunch of DB::raw(..), toSql(), setBindings() and that's all, just translate it according to the docs...
1 like
Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
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.
@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();
Please or to participate in this conversation.