Support the ongoing development of Laravel.io →
Requests Database Eloquent

I have a terrible query from a design that I inherited. Here it is..

SELECT 
i.id, i.description AS ingredient, effective_date, uom.symbol AS purchase_uom,
(sum(ri.regular_qty * t.regular) + sum(ri.large_qty * t.large)) * uc.factor AS qty

FROM

(SELECT m.id AS mealID, effective_date,
SUM(CASE
WHEN c.portion = -1 THEN 1
WHEN c.portion =  0 THEN 1
WHEN c.portion =  2 THEN 2
END) AS regular,
SUM(CASE
WHEN c.portion = 1 THEN 1
END) AS large
FROM `client` c
JOIN client_menu cm ON c.idUser=cm.idClient
JOIN meal m ON cm.id_meal=m.id
WHERE effective_date BETWEEN '2016-01-20' AND '2016-01-20'
AND c.node_id = '3'
AND id_meal >0
GROUP BY effective_date, m.id
ORDER BY meal_type,m.long_name,cm.notes, c.portion
) AS t

JOIN recipes r ON t.mealID = r.meal_id
JOIN recipe_ingredients ri ON r.id = ri.recipe_id
JOIN ingredients i ON ri.ingredient_id = i.id
JOIN uom_conversions uc ON uc.uom_from = i.recipe_uom AND uc.uom_to = i.purchase_uom
JOIN uom ON uom.id = i.purchase_uom
GROUP BY effective_date, i.id ORDER BY i.description ASC 

when I run the query in phpmyadmin it takes 20 millisec. But within my laravel app the query takes 40-50 seconds.

I did some basic profiling to confirm that the delay is in the query and not somewhere else.

To run teh query in laravel I have tried:

$pdo 	= DB::getPdo();
$query 	= $pdo->query($sql);
$data 	= $query->fetchAll(); 

and also

$data = DB::select(DB::raw($sql)); 

I have spent days on this, I have no clue how to fix it. Please help....

Last updated 3 years ago.
0

Sign in to participate in this thread!

PHPverse

Your banner here too?

marcoacm marcoacm Joined 12 Oct 2015

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2025 Laravel.io - All rights reserved.