1

I have tried all kinds of methods of limiting the columns which are returned in my many-to-many relationship, and none seem to work.

Background - Not really necessary, but to give the big picture

Essentially, in my app, I want to build a list of contacts for the currently logged in user. Administrator and Billing users should be able to contact everybody including users of group Customer.

Customer should only be able to contact Administrator and Billing.

So my way to tackle this is firstly to determine the groups that the user is in.

$userGroups = Sentry::getUser()->getGroups()->lists('name', 'id');

Then iterate over the groups, to see if the user is in the group Administrator or Billing or Customer and build the contact groups for that user.

foreach($userGroups as $group)
{
    if ($group === 'Administrator' || $group === 'Billing')
    {
        $contactGroups = \Group::with('users')->get(['id', 'name']);
    }
        else if ($group === 'Customer')
        {
            $contactGroups = \Group::where('name', 'Administrator')
                ->orWhere('name', 'Billing')
                ->with('users')
                ->get(['id', 'name']);
        }
        else 
        {
            return Response::json('No Contacts found', 404);
        }
    }

The problem - It appears that I am unable to select specific columns to select on belongsToMany relations.

I have tried:

$contactGroups = \Group::where('name', 'Administrator')
->orWhere('name', 'Billing')
    ->with(['users', function($q){
        $q->select('id', 'first_name', 'last_name');
    }])
    ->get(['id', 'name']);

I have also tried limiting the select within the Group model

class Group extends Eloquent
{
    protected $table = 'groups';

    public function users()
    {
        return $this->belongsToMany('User', 'users_groups')
                ->select('id', 'first_name', 'last_name', 'email', 'telephone');
    }
}

Either way, the query runs, but it returns the entire user object and completely ignores my selects.

As such, when I return a json response, everything that I do not want is included.

So what I have done as a temporary fix is iterate over each of the users in each of the groups, and unset all the attributes which I do not want. 

    foreach ($contactGroups as $group)
    {
        foreach($group->users as $user)
        {
            unset($user->persist_code);
            unset($user->created_at);
            unset($user->updated_at);
            unset($user->deleted_at);
            unset($user->last_login);
            unset($user->permissions);
            unset($user->activated_at);
            unset($user->activated);
            unset($user->reset_password_code);
            unset($user->pivot);
        }
    }

return Response::json($contactGroups, 200);

This is really clunky, inefficient and seems like a waste of time. Is there a better way of achieving the above?

Gravy
  • 12,264
  • 26
  • 124
  • 193
  • Better answers can be read in thread [Get specific columns using “with()” function in Laravel Eloquent](http://stackoverflow.com/questions/19852927/get-specific-columns-using-with-function-in-laravel-eloquent) – Cyrille37 Jan 31 '17 at 19:38

2 Answers2

0

For some reason selecting specific columns with belongsToMany is not working. But i have found an alternate solution.

There is a provision in laravel, Converting to Arrays or json that allows you to whitelist/blacklist specific columns when using toArray() or toJson.

To prevent specific fields from appearing in the relation : 

class User extends Eloquent{

  protected $hidden = array("persist_code","created_at","updated_at","deleted_at","last_login");
}

Instead if you wish to allow specific fields :

protected $visible = array("Visibile fields");
Varun Nath
  • 5,570
  • 3
  • 23
  • 39
  • Yes, your workaround works, and thanks for your answer... however I am then presented with another problem... sometimes, I may need some of these attributes... and blacklisting or whitelisting will ** always ** hide / show them. As such, I need a way of selecting the columns which I require in the first instance. – Gravy Apr 26 '14 at 15:47
  • e.g. hiding `created_at` in the model will interfere with my `api.v1.admin.user.index` route whereby I do need the `created_at` attribute to be returned in the json response. – Gravy Apr 26 '14 at 15:51
  • yea i didn't think about the admin page, because thats where you'd want all the results. Its not possible to set the $hidden/$visible properties dynamically either. Any news if the laravel team are planning to fix this or is it going to remain like this ? – Varun Nath Apr 26 '14 at 17:40
  • ok so there is one workaround.Only toJson and toArray hide the fields. If you would like to view them u can use model->property to access them. Since the admin page would probably be the only place you'd like to see all the fields, you could use this workaround. – Varun Nath Apr 26 '14 at 18:36
0

try this

$contactGroups = \Group::where('name', 'Administrator')
->orWhere('name', 'Billing')
->with(['users', function($q){
    $q->get(['id', 'first_name', 'last_name']);
}])
->get(['id', 'name']);

or this

$contactGroups = \Group::where('name', 'Administrator')
->orWhere('name', 'Billing')
->with(['users:id,first_name,last_name'])
->get(['id', 'name']);
Hussein Elturkey
  • 1
  • 1
  • 3