Wednesday, March 1, 2017

Return selected columns in deep nested relationship in Laravel

I'm using Laravel 5.3 and trying to return data from multiple tables using a join.

I'm using 3 models / tables, Customer, Business and Website which are related as follows:

In Customer.php:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Customer extends Model
{
  public function businesses()
  {
    return $this->hasMany('App\Business');
  }
}

In Business.php:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Business extends Model
{
  public function customer() 
  {
    return $this->belongsTo('App\Customer');
  }

  public function websites()
  {
    return $this->hasMany('App\Website');
  }
}

And in Website.php:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Website extends Model
{  
  public function business() {
    return $this->belongsTo('App\Business');
  }
}

So a Customer can have many Businesses which can have many Websites.

Now I'm trying to return certain columns from Customer, Business and Website in (ideally) one Eloquent request. I know I can return all columns from all tables using a simple eager load like so:

Customer::with('businesses.websites');

However I only want to return certain columns and return an array of arrays like so:

[
  0 => {
    'first_name' => 'John',
    'last_name' => 'Smith,
    'email' => 'js@test.com',
    'businesses' => [ 
      0 => {
        'name' => 'Smith Ltd'
        'websites' =>  [
          0 => {
            'domain' => 'smithltd',
            'tld' => '.co.uk'
          }
        ]
      }
    ]
  } ...
]

i.e ignoring ids, foreign keys, created_at timestamps etc etc.

I've been trying to achieve this using the following code (and variations thereof):

  Customer::with([
    'businesses' => function ($q) {
      $q->select('id', 'customer_id')->pluck('name');
    },
    'businesses.websites' => function ($q) {
      $q->select('id', 'business_id')->pluck('domain', 'tld');
    }
  ])->get();

But this is returning id, customer_id and business_id too which I don't want. As far as I know I need to add the foreign_key and primary_key in the select query for the query to work but is there a way to remove it from the collection that is ultimately returned? Or is there a better way to approach this? Any help would be greatly appreciated, many thanks.




via dbatten

Advertisement