tduffy
6 months ago

Trying to do a SQLSRV Query using cte_sum in Eloquent

Posted 6 months ago by tduffy

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!

Please sign in or create an account to participate in this conversation.