jmfs
4 years ago

Stored Procedure-Based Models

Posted 4 years ago by jmfs

Hey, our company uses MSSQL for a bunch of our main DB infrastructure and uses Stored Procedures to streamline many requests across systems. What is the best way to build a model that uses SPs instead of tables? I just fooled around and came up with this that basically would overwrite the methods for functions like ::all() and ::find(), probably not the best solution. (using MySQL here, I know to use Exec and not CALL)

class Location extends Model {

    protected $table = null;

    public static function all($columns = array('*')) {
        // make all fillable
        Model::unguard();
        // start with empy array
        $locations = [];
        // get all locations from SP
        foreach (\DB::select('CALL getLocationAll();') as $attrs) {
            $locations[] = new Location((array)$attrs);
        }
        return new Collection($locations);
    }

    public static function find($id, $columns = array('*')) {
        // make all fillable
        Model::unguard();
        // get data
        $attrs = \DB::select('CALL getLocation(?);',[$id]);
        // return single store
        return new Location((array)$attrs[0]);
    }

    public function menu() {
        return Menu::find($this->id);
    }

}

Obviously, we'd need to add pagination to the SPs and when I try and make another custom method that would mirror a relationship (Location::find(1)->menu), it gives me an error:

Relationship method must return an object of type Illuminate\Database\Eloquent\Relations\Relation

So is there a way to make a relationship just a simple method that calls another sp?

Anyone have suggestions or dealt with this before? Thank you.

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