I have a Laravel 6 app that uses a MySQL connection for authentication and storing user settings, but it also uses a SQL Server connection to read data for dashboarding. I have a pretty intense SQL query that uses cte_sum that I would really like to convert to Eloquent. I can run the query in RAW just fine from a controller, but I really don't want to do that for a lot of reasons.
Here's what the query looks like:
WITH cte_sum AS ( /* labeled 't' */ SELECT DATEPART(YEAR, RecordDate) [YEAR], DATEPART(QUARTER, RecordDate) [QUARTER], SUM(RecordAmount) AS TotalRecords, COUNT(RecordAmount) AS TotalRecordCount FROM some_table GROUP BY DATEPART(YEAR, RecordDate), DATEPART(QUARTER, RecordDate) ) SELECT t.[YEAR],t.[QUARTER], MAX(t.TotalRecords) AS [TotalRecords], MAX(t.TotalRecordCount) AS [TotalRecordCount], SUM((s.RecordAmount/t.TotalRecords)*s.LowestScore) AS [WAFICO], SUM(s.RecordAmount/t.TotalRecords*s.DTI) AS [WADTI], SUM(s.RecordAmount/t.TotalRecords*s.APR) AS [WAAPR], SUM(s.RecordAmount/t.TotalRecords*s.Term) AS [WATERM] FROM some_table s INNER JOIN cte_sum t ON DATEPART(YEAR, s.RecordDate)=t.[YEAR] AND DATEPART(QUARTER, s.RecordDate)=t.[QUARTER] GROUP BY t.[YEAR],t.[QUARTER] ORDER BY t.[YEAR], t.[QUARTER]
Is it possible to recreate this query in Eloquent? If not, how do I run a raw query in a model so I can get it out of my controller? Any help would be much appreciated!