I did this exact thing and it works fine in the eloquent models, you just have to have your create your own custom getData method to return data rather then the built-in find() or where() methods on the models. I haven't noticed any issues doing it at all.

Pro tip... use DB::unprepared('select * from table') instead of DB::raw('select * from table')

DB::raw will mess up nested sub select queries and can only execute them 1 level deep. unprepared runs the true RAW sql and doesn't interfere with the query at all.

Thanks for your feedback and for the DB::unprepared, that would have surely been irritating at some point of the development!

How did you go about pushing the data into the models? Through Eloquent::hydrate?

Hey Will - sounds like we are working on similar projects, I took the approach to separate my complex queries from eloquent into there own classes - an example of one of my crosstab queries that I build manually in its own method

In the current code I just placed these classes in the model directory and call them statically from the controller but I am currently migrating the whole application to be more "Laravel" and utilise repositories and dependency injection.

I found this to be quicker for me in development rather than trying to get my head round how these crosstabs fit into Active Record.

public static function ordertype()
	{
// build Query
        $sql = "select drotid id, drotype name, droseq,";
        
        $sql .= "(select count(1) from drop_sla ds where ds.sordertype = drot.drotid and ds.ssla = 0) as breach,";
        $sql .= "(select count(1) from drop_sla ds where ds.sordertype = drot.drotid and ds.scomplete = 0) as inflight,";
        
        for ($age = 0; $age <= 30; $age++)
        {
            $sql .= "(select count(1) from drop_sla ds where ds.sordertype = drot.drotid ";
            $sql .= "and floor((".time()."/86400) - (ds.spbreach/86400)) = $age and ds.ssla = 0) as day$age, ";
        }
        
        // 31 - 44
        $sql .= "(select count(1) from drop_sla ds where ds.sordertype = drot.drotid ";
        $sql .= "and floor((".time()."/86400) - (ds.spbreach/86400)) between 31 and 43 and ds.ssla = 0) as day31, ";
        
        // 45 - 89
        $sql .= "(select count(1) from drop_sla ds where ds.sordertype = drot.drotid ";
        $sql .= "and floor((".time()."/86400) - (ds.spbreach/86400)) between 45 and 89 and ds.ssla = 0) as day45, ";
        
        // 90+
        $sql .= "(select count(1) from drop_sla ds where ds.sordertype = drot.drotid ";
        $sql .= "and floor((".time()."/86400) - (ds.spbreach/86400)) >= 90 and ds.ssla = 0) as day90 ";
                        
        $sql .= "from drop_ref_ordertype drot ";
        $sql .= "where drot.drotid not in ('4','5','6','7','8','9','A','C', '12') order by drot.droseq";
        
        // Execute and return
        return DB::select( DB::raw($sql));
	}