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