Complex Queries and models

posted 10 years ago

Hey,

I'm new to Laravel but I've done quite a bit of searching and could not find any clear solutions. I've previously used Zend 1.X where we rolled out own simple ORM using the data mapper pattern so ActiveRecord is a little new to me.

I have some complex queries that I do not want to use the query builder for (it makes the queries more unreadable and difficult to optimise, these will use SUM() in the columns, HAVING, Sub Queries etc) it is for building reports. I would like to use the data from these queries with the Eloquent Models (without the save functionality - these are essentially readonly exports of the data).

For example if I create a report which SUM's and COUNT's data from a table and it's underlying tables is it possible to use eloquent models to store the data for ease of visualisation or do I need to roll my own models? (This seems a little backward as I'd like some of the logic within the models to be inherited ,such as the accessors)

Also a similar question, whilst Laravel contains models to handle database interaction and controllers for some business logic I'd like to use my own classes to extend this business logic and run a process which isn't controller or model specific. (For example, we may take some data and run some pretty heavy data cleaning processes against the data before using it in a report - ideally I'd like to be able to call this as-and-when needed globally). Is there a best practice for storing this kind of process? I can see from an example project (https://github.com/andrewelkins/Laravel-4-Bootstrap-Starter-Site/tree/master/app) that the use of a library/ folder in the App root is used - is this the best approach?

Thanks!

Will

Last updated 2 years ago.

iansirkit

replied 10 years ago

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.

Last updated 2 years ago.

williamvicary

replied 10 years ago

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?

Last updated 2 years ago.

daveyoi

replied 10 years ago

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));
	}

Last updated 2 years ago.

Moderators