Trying to do a SQLSRV Query using cte_sum in Eloquent

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!

jlrdw

6 years ago

Level 75

I'd look at the PDO_DBLIB for ways to write a query using pdo. And https://docs.microsoft.com/en-us/sql/connect/php/pdo-class?view=sql-server-ver15

A note on "raw queries".

Somehow this forum started to call a regular query a raw query, not so.

A regular query without bindings is a raw query
A query with bindings is a regular query

Eloquent itself converts to a regular sql pdo query with bindings at runtime.

Raw statements Taylor warns about in the docs to bind if necessary.

staudenmeir

6 years ago

Best Answer

Level 24

Laravel has no native support for common table expressions.

I've created a package for it: https://github.com/staudenmeir/laravel-cte

$sum = YourModel::selectRaw(...)
    ->groupBy(...);

YourModel::withRecursiveExpression('cte_sum', $sum)
    ->select('t.YEAR', 't.QUARTER')
    ->selectRaw(...)
    ->join('cte_sum as t', function ($join) {
        $join->on(...)
            ->on(...);
    })
    ->groupBy('t.YEAR', 't.QUARTER')
    ->orderBy('t.YEAR', 't.QUARTER')
    ->get();

1 like

tduffy

6 years ago

Level 3

This is what I need. Thank you!

tduffy

5 years ago

Level 3

@staudenmeir Is it possible to stack more than one cte_sum into this? For example, if I have 3 different cte_sums that I want to include in my query, could I somehow do:

YourModel::withRecursiveExpression(['cte_sum' => $sum, 'cte_sum2' => $sum2, 'cte_sum3' => $sum3])
	->select(...) etc

staudenmeir

5 years ago

Level 24

@tduffy

YourModel::withRecursiveExpression('cte_sum', $sum)
    ->withRecursiveExpression('cte_sum2', $sum)
    ->withRecursiveExpression('cte_sum3', $sum)
    ->select(...)

tduffy

5 years ago

Level 3

Thank you thank you! :)

Please or to participate in this conversation.