Requesting help in fixing an issue with respect to executing mysql query using laravel query builder #49280
Replies: 1 comment 7 replies
-
|
Attaching the raw query generated for reference.
|
Beta Was this translation helpful? Give feedback.
-
|
And what query does Laravel generate in comparison? |
Beta Was this translation helpful? Give feedback.
-
|
@shaedrich Please find the query generated by Laravel. Laravel version is 9.1 and used dd() to get the raw sql generated by Laravel. Got the qid as a binding as well.
|
Beta Was this translation helpful? Give feedback.
-
|
Here you go, it's still pretty unreadable though. SELECT DISTINCT
table_1.id
FROM
zrx.table_1
INNER JOIN (
SELECT
MAX(uw.completed_date) AS completed_date
FROM
zrx.table_1ASuw
INNER JOIN syn.table_2 AS r ON r.users_wave_id = uw.id
LEFT JOIN zrx.table_3 ON table_3.id = uw.id
LEFT JOIN zrx.table_4 ON table_4.id = uw.user_id
WHERE
uw.status IN(1)
AND uw.wave_id IN(94327)
AND r.qid = 493608
AND CONVERT_TZ(uw.completed_date, "+00:00", IF(table_3.time_zone IS NULL OR table_3.time_zone = "-99.99", "+00:00", table_3.time_zone)) <= ADDTIME("2023-12-14 00:00:00", "23:59:59") AND CONVERT_TZ(uw.completed_date, "+00:00", IF(table_3.time_zone IS NULL OR table_3.time_zone = "-99.99", "+00:00", table_3.time_zone)) >= ADDTIME("2023-11-02 00:00:00", "00:00:00")
GROUP BY
COALESCE(NULLIF(table_3.vendor_npi, ''), NULLIF(table_4.npi, ''), uw.user_id),
uw.wave_id) AS responseLatestOrEarliest ON responseLatestOrEarliest.completed_date = table_1.completed_date
INNER JOIN zrx.wavesONtable_1.wave_id = waves.id
LEFT JOIN zrx.table_4 ON table_1.user_id = table_4.id
LEFT JOIN zrx.table_3 ON table_3.id = table_1.id
LEFT JOIN syn.table_2ASresponses ON responses.users_wave_id = table_1.id
WHERE
table_1.wave_id IN(94327) AND table_1.status IN(1) AND responses.qid = ? AND CONVERT_TZ(table_1.completed_date, "+00:00", IF(table_3.time_zone IS NULL OR table_3.time_zone = "-99.99", "+00:00", table_3.time_zone)) <= ADDTIME("2023-12-14 00:00:00", "23:59:59") AND CONVERT_TZ(table_1.completed_date, "+00:00", IF(table_3.time_zone IS NULL OR table_3.time_zone = "-99.99", "+00:00", table_3.time_zone)) >= ADDTIME("2023-11-02 00:00:00", "00:00:00") |
Beta Was this translation helpful? Give feedback.
-
|
Thanks for the help in making it readable @rdgout |
Beta Was this translation helpful? Give feedback.
-
|
Just a thought. When raw querying the eager loading is not happening. When executing the query using a builder maybe other stuff is executed also. I see you use distinct. The distinct has a fragile use case in laravel that if you don't get right, it backfires. On a second thought, your initial sql ran via builder is cached in mysql and the second time you run it via raw query is very fast. |
Beta Was this translation helpful? Give feedback.
Uh oh!
There was an error while loading. Please reload this page.
Uh oh!
There was an error while loading. Please reload this page.
-
Hi Everyone,
We have a project which built using Laravel. We were facing one peculiar issue, where the query executed using DB facade/query builder is getting executed against the MySQL database for very long time(> 500 secs), whereas the raw query generated from the same query object getting executed within 2 seconds directly in mysql client or even when the raw query is executed from the code using DB::select($rawQuery). We have compared the raw queries and both are exactly same.
We have lot of queries in our application which are built using DB facade/query builder, but we are not facing this issue elsewhere. In one particular flow alone we are facing this issue and we are clueless on why it is happening.
We couldnt find any related discussions or posts in any other sites as well regarding this behaviour. I could see posts or blogs on slowness of eloquent but not on DB facade/query builder.
Can someone throw us light on why this is happening?
Thanks in advance..!
Beta Was this translation helpful? Give feedback.
All reactions