CODEWITHSUNDEEP
laravelmodeleloquent
Troy
Troy

Reputation: 971

Limit Eloquent Model to specific columns

I'm pulling from a rather large database and for security reasons, my database user can only select a limited number of columns from the student table: name, graduation_date, and gender. But there are dozens of other columns returned in a select * statement.

In regular SQL, if I run something like: 

SELECT * FROM students

will return an error on that table. Same if I run the eloquent model 

Students::all();

will return an error as well.

I know in Eloquent, you can limit your selects when defining a relationship similar to:

class Students extends Eloquent {

    protected $table        = 'student_info';

    public function classes() {
        return $this->hasMany('classes')->select(array('room', 'time'));
    }

}

So, My question is, can the select limits be done on the main model, similar to limiting it on the classes table. So, when I run Student::all(); it only selects the columns I need.

The main problem is every time I run a student Query, I'm having to do a specific select command each time instead of just saying "Student::all()". Same thing for Student::find(1); will also return an error, because it still runs a SELECT * FROM student_info WHERE id = 1.

I tried setting $visible variable, but it still renders sql equivalent to SELECT * FROM ...

Anyone have a solution?

UPDATE: Please note that I'm looking or a solution on the model level, not the controller level. I can select from the controller side, but that defeats the purpose of a Model concept and have to declare the columns to select at every query.

Thanks! Troy

Upvotes: 2

Views: 8388

Answers (3)

RobbieP
RobbieP

Reputation: 698

Expanding on Jarek's suggestion of using Global Scope you could do it like this, I'm not 100% sure the remove part is right though, will need testing.

SelectLimitTrait.php

trait SelectLimitTrait {

    public static function bootSelectLimitTrait()
    {    
        static::addGlobalScope(new SelectLimitScope);
    }

    public function getQueryable()
    {
        if(! $this->queryable ) return array('*');

        return $this->queryable;
    }  

}

SelectLimitScope.php

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\ScopeInterface;

class SelectLimitScope implements ScopeInterface {

    public function apply(Builder $builder)
    {
        $query = $builder->getQuery();

        $queryable = $builder->getModel()->getQueryable();

        $query->columns = $queryable;
    }

    public function remove(Builder $builder)
    {
        $query = $builder->getQuery();

        $query->columns = null;
    }

}

And then in your Eloquent model put this:

Students.php

class Students extends \Eloquent  {

    use SelectLimitTrait;

    protected $queryable = array('name','graduation_date', 'gender');

}

Now Students::all() and Students::find(1) etc. are limited to querying name, graduation_date and gender

Upvotes: 4

The Alpha
The Alpha

Reputation: 146269

You may use something like this:

public function newQuery()
{
    return parent::newQuery()->select('room', 'time');
}

Put the newQuery method in your Students model and use the Student model normally you would use. It's a hacky way but easiest one. Just override the parent::query(). In this way you'll always get the selected fields.

Upvotes: 3

Kousha
Kousha

Reputation: 36299

You can create an intermediate class and overload the all() function. Let's call this class Elegant

Elegant.php 

abstract class Elegant extends Model
{
    public static $returnable = [];

    public function all()
    {
        return $this->get(static::$returnable)->all();
    }
}

Then you extend this class, and define your returnable columns to it.

Student.php

<?php
class Student extends Elegant
{
    public static $returnable = ['room', 'time'];
}

Now use it as you wanted: Student::all() in your controller. If you leave returnable as an empty array, then you will get everything.

Upvotes: 7

Related Questions