Laravel有许多对多人的雄辩

Question

I haven't had much luck sorting this out the Laravel way. So I pose two questions.

Given that I have a Car and that Car can have many Features, but that Features are also separated by Feature Type, how can I return all Features, separated the Feature Type, for said Car?

I have four tables, with listings_features being the pivot table:

• listings (id)
• listings_features (listing_id, feature_id)
• listings_features_types (id, type)
• listings_features_values (id, listing_feature_type_id, value)

I have the following code, which produces what I need, but when I use it, I get a Laravel error ... "Call to a member function addEagerConstraints() on string" ... because I invoke it as such:

Listing::with(
        'features',
    )->get();

The code I am using to produce the data in the format I desire (not steadfast on) is

public function features()
{
    $out = array();
    $features = $this->hasMany('App\Models\ListingFeature', 'listing_id', 'id')->select('feature_id')->get();
    $types = ListingFeatureType::all();
    foreach($types as $key => $obj){
        $out[$key]['listing_feature_type_id'] = $obj->id;
        $out[$key]['name'] = $obj->listing_feature_type;
        $out[$key]['features'] = ListingFeatureValue::whereIn('id', $features->toArray())->where('listing_feature_type_id', '=', $obj->id)->get()->toArray();
    }
    return json_encode($out);
}

Which returns:

[
  {
    "listing_feature_type_id": "0f40888c-3b09-40ed-aef6-0fddc1a155b6",
    "name": "Safety",
    "features": [
      {
        "id": "0ed0ad63-a6ed-4818-8c6f-ee048694dcd9",
        "listing_feature_type_id": "0f40888c-3b09-40ed-aef6-0fddc1a155b6",
        "listing_feature_text": "Anti-Lock Brakes"
      },
      {
        "id": "37abeef2-dc22-4995-8503-f89962242ea6",
        "listing_feature_type_id": "0f40888c-3b09-40ed-aef6-0fddc1a155b6",
        "listing_feature_text": "Collision Detection"
      },
      {
        "id": "3c0728e1-91f7-4f44-ac0b-429eda816692",
        "listing_feature_type_id": "0f40888c-3b09-40ed-aef6-0fddc1a155b6",
        "listing_feature_text": "Dual Airbags"
      },
      {
        "id": "4255b8b4-e71c-4059-8a22-1b9894512564",
        "listing_feature_type_id": "0f40888c-3b09-40ed-aef6-0fddc1a155b6",
        "listing_feature_text": "Side Airbags"
      }
    ]
  },
  {
    "listing_feature_type_id": "84dcce5c-34b9-4c8b-9066-5b20a42cf4cd",
    "name": "Interior",
    "features": [
      {
        "id": "1b89581e-1a30-4dce-9455-ab0ad4c49bcf",
        "listing_feature_type_id": "84dcce5c-34b9-4c8b-9066-5b20a42cf4cd",
        "listing_feature_text": "Privacy Glass"
      },
      {
        "id": "59e3628f-3cef-4447-9cb2-71be4a3046a4",
        "listing_feature_type_id": "84dcce5c-34b9-4c8b-9066-5b20a42cf4cd",
        "listing_feature_text": "Onboard GPS"
      },
      {
        "id": "66fe416b-98dc-45c8-979d-78f2ea7fe876",
        "listing_feature_type_id": "84dcce5c-34b9-4c8b-9066-5b20a42cf4cd",
        "listing_feature_text": "In-dash Navigation"
      },
      {
        "id": "8fe836a3-5596-4306-aac1-bae4cb596e20",
        "listing_feature_type_id": "84dcce5c-34b9-4c8b-9066-5b20a42cf4cd",
        "listing_feature_text": "Power Windows"
      },
      {
        "id": "e4addb5a-1b26-4ae3-b0ee-3b8bce892fb9",
        "listing_feature_type_id": "84dcce5c-34b9-4c8b-9066-5b20a42cf4cd",
        "listing_feature_text": "Tinted Windows"
      },
      {
        "id": "f95b8253-a2b8-4bfc-90c0-0fc656c3f200",
        "listing_feature_type_id": "84dcce5c-34b9-4c8b-9066-5b20a42cf4cd",
        "listing_feature_text": "CD Player"
      }
    ]
  },
  {
    "listing_feature_type_id": "8d16e8ea-3d38-48dc-8e56-00d1cc736f0d",
    "name": "Exterior",
    "features": [
      {
        "id": "3aa6dd05-dd3a-4e93-ad06-295687a8dda1",
        "listing_feature_type_id": "8d16e8ea-3d38-48dc-8e56-00d1cc736f0d",
        "listing_feature_text": "Spoiler"
      }
    ]
  }
]

Here are the models (very basic, just starting out):

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class ListingFeatureValue extends Model
{

    protected $table = 'listings_features_values';
    public $timestamps = false;
    public $incrementing = false;

    public function type() {
        return $this->belongsTo('App\Models\ListingFeatureType', 'listing_feature_type_id', 'id');
    }

}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class ListingFeatureType extends Model
{

    protected $table = 'listings_features_types';
    public $timestamps = false;
    public $incrementing = false;

    public function values() {
        return $this->hasMany('App\Models\ListingFeatureValue', 'listing_feature_type_id', 'id');
    }

}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class ListingFeature extends Model
{

    protected $table = 'listings_features';
    public $timestamps = false;
    public $incrementing = false;

}

How can I create the Laravel model relationships to achieve the same result set but calling it as mentioned above? Alternatively, how can I call it as above but stop the error from occurring?

If you made it this far, thanks!

Update:

I wasn't able to get it working as is, I got a SQL error which makes me feel like I need a belongsToManyThrough so I can specify the table names:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'ad_l5.listing_listing_feature' doesn't exist (SQL: select `listings_features`.*, `listing_listing_feature`.`listing_id` as `pivot_listing_id`, `listing_listing_feature`.`listing_feature_id` as `pivot_listing_feature_id` from `listings_features` inner join `listing_listing_feature` on `listings_features`.`id` = `listing_listing_feature`.`listing_feature_id` where `listing_listing_feature`.`listing_id` in (b266c874-1cef-4f49-b65f-f91ddaaf6aee, e93674ca-3f82-45d8-9961-e8569cac164b))

But using the exact code posted as the answer below from @Carter Fort and changing the features() method to

return $this->belongsToMany(ListingFeatureValue::class, 'listings_features', 'listing_id', 'feature_id');

Also adding to the model ListingFeatureValue

public function type()
    {
        return $this->belongsTo(ListingFeatureType::class, 'listing_feature_type_id', 'id');
    }

I get the output of:

"featuresByType": {
"": [
{
"id": "3aa6dd05-dd3a-4e93-ad06-295687a8dda1",
"listing_feature_type_id": "8d16e8ea-3d38-48dc-8e56-00d1cc736f0d",
"listing_feature_text": "Spoiler",
"pivot": {
"listing_id": "e93674ca-3f82-45d8-9961-e8569cac164b",
"feature_id": "3aa6dd05-dd3a-4e93-ad06-295687a8dda1"
},
"type": {
"id": "8d16e8ea-3d38-48dc-8e56-00d1cc736f0d",
"listing_feature_type": "Exterior"
}
},
{
"id": "f95b8253-a2b8-4bfc-90c0-0fc656c3f200",
"listing_feature_type_id": "84dcce5c-34b9-4c8b-9066-5b20a42cf4cd",
"listing_feature_text": "CD Player",
"pivot": {
"listing_id": "e93674ca-3f82-45d8-9961-e8569cac164b",
"feature_id": "f95b8253-a2b8-4bfc-90c0-0fc656c3f200"
},
"type": {
"id": "84dcce5c-34b9-4c8b-9066-5b20a42cf4cd",
"listing_feature_type": "Interior"
}
},
{
"id": "66fe416b-98dc-45c8-979d-78f2ea7fe876",
"listing_feature_type_id": "84dcce5c-34b9-4c8b-9066-5b20a42cf4cd",
"listing_feature_text": "In-dash Navigation",
"pivot": {
"listing_id": "e93674ca-3f82-45d8-9961-e8569cac164b",
"feature_id": "66fe416b-98dc-45c8-979d-78f2ea7fe876"
},
"type": {
"id": "84dcce5c-34b9-4c8b-9066-5b20a42cf4cd",
"listing_feature_type": "Interior"
}
},
{
"id": "0ed0ad63-a6ed-4818-8c6f-ee048694dcd9",
"listing_feature_type_id": "0f40888c-3b09-40ed-aef6-0fddc1a155b6",
"listing_feature_text": "Anti-Lock Brakes",
"pivot": {
"listing_id": "e93674ca-3f82-45d8-9961-e8569cac164b",
"feature_id": "0ed0ad63-a6ed-4818-8c6f-ee048694dcd9"
},
"type": {
"id": "0f40888c-3b09-40ed-aef6-0fddc1a155b6",
"listing_feature_type": "Safety"
}
},
  ...

That is better than I was at, but it would be nice if the results were grouped by type then features within then instead of it being each feature with a type attached. Makes it harder to parse for display. Thanks for the help thus far!

Update 2

These would be my schema files:

// listings
Schema::create('listings', function (Blueprint $table) {
    $table->string('id');
    $table->string('title');
});

// listings to features pivot
Schema::create('listings_features', function (Blueprint $table) {
    $table->string('listing_id');
    $table->string('feature_id');
});

// feature types (i.e. Safety)
Schema::create('listings_features_types', function(Blueprint $table){
    $table->string('id');
    $table->string('listing_feature_type');
});

// feature values (i.e. Anti-Lock Brakes)
Schema::create('listings_features_values', function(Blueprint $table){
    $table->string('id');
    $table->string('listing_feature_type_id'); // links to listings_features_types
    $table->string('listing_feature_text');
});

Update 3 Making the changes from the answer below (I will post all code once working) I get close to what I would like.

"features": [
{
"id": "3aa6dd05-dd3a-4e93-ad06-295687a8dda1",
"listing_feature_type_id": "8d16e8ea-3d38-48dc-8e56-00d1cc736f0d",
"listing_feature_text": "Spoiler",
"pivot": {
"listing_id": "e93674ca-3f82-45d8-9961-e8569cac164b",
"feature_id": "3aa6dd05-dd3a-4e93-ad06-295687a8dda1"
},
"type": {
"id": "8d16e8ea-3d38-48dc-8e56-00d1cc736f0d",
"listing_feature_type": "Exterior"
}
},
{
"id": "f95b8253-a2b8-4bfc-90c0-0fc656c3f200",
"listing_feature_type_id": "84dcce5c-34b9-4c8b-9066-5b20a42cf4cd",
"listing_feature_text": "CD Player",
"pivot": {
"listing_id": "e93674ca-3f82-45d8-9961-e8569cac164b",
"feature_id": "f95b8253-a2b8-4bfc-90c0-0fc656c3f200"
},
"type": {
"id": "84dcce5c-34b9-4c8b-9066-5b20a42cf4cd",
"listing_feature_type": "Interior"
}
},
{
"id": "66fe416b-98dc-45c8-979d-78f2ea7fe876",
"listing_feature_type_id": "84dcce5c-34b9-4c8b-9066-5b20a42cf4cd",
"listing_feature_text": "In-dash Navigation",
"pivot": {
"listing_id": "e93674ca-3f82-45d8-9961-e8569cac164b",
"feature_id": "66fe416b-98dc-45c8-979d-78f2ea7fe876"
},
"type": {
"id": "84dcce5c-34b9-4c8b-9066-5b20a42cf4cd",
"listing_feature_type": "Interior"
}

// .... ]

What I would like is:

{
   "feature_types":[
      {
         "type":{
            "id":"8d16e8ea-3d38-48dc-8e56-00d1cc736f0d",
            "listing_feature_type":"Interior",
            "features":[
               {
                  "id":"3aa6dd05-dd3a-4e93-ad06-295687a8dda1",
                  "listing_feature_type_id":"8d16e8ea-3d38-48dc-8e56-00d1cc736f0d",
                  "listing_feature_text":"GPS"
               },
               {
                  "id":"66fe416b-98dc-45c8-979d-78f2ea7fe876",
                  "listing_feature_type_id":"84dcce5c-34b9-4c8b-9066-5b20a42cf4cd",
                  "listing_feature_text":"In-dash Navigation"
               },

            ]            "pivot":{
               "listing_id":"e93674ca-3f82-45d8-9961-e8569cac164b",
               "feature_id":"f95b8253-a2b8-4bfc-90c0-0fc656c3f200"
            }
         }
      }
   ]
}

</div>

Answer 1

You could use a Many-to-Many relationship between the Listing and the ListingFeatureValue models, then group the related listing features for a given listing by their type using the groupBy Collection method.

The Listing model:

class Listing extends Model {

    protected $hidden = [
        'features'
    ];

    protected $appends = [
        'feature_types'
    ];

    public function features(){
        return $this->belongsToMany(ListingFeatureValue::class, 'listings_features', 'listing_id', 'feature_id');
    }

    public function getFeatureTypesAttribute()
    {
        return $this->features->groupBy(function ($feature, $key) {
            return $feature->type->id;
        })->map(function($features, $key){
            $type = ListingFeatureType::find($key);
            $type->features = $features;
            return $type;
        })->values();
    }

}

The getFeatureTypesAttribute() is the star of the show here, because you can combine that with the appends array to force the Eloquent model to append that to any toArray() calls to the model instance, which is what toJson() uses when converting your model to JSON.

It may seem a little convoluted to first fetch all the listing values then divide them using the groupBy and map collection methods, but there is no native Eloquent mechanism for using hasManyThrough via many-to-many relationships. There's a different approach here if you don't like this one.

The ListingFeatureValue model:

class ListingFeatureValue extends Model
{
    public $table = 'listings_features_values';

    public function type()
    {
        return $this->belongsTo(ListingFeatureType::class, 'feature_type_id');
    }
}

I'm showing this model here because the type() relationship is called in the getFeaturesByTypeAttribute() method above and didn't want there to be any confusion.

And, just for the sake of completeness, the ListingFeatureType model:

class ListingFeatureType extends Model
{
    public $table = "listings_features_types";

    public function listings()
    {
        return $this->hasMany(ListingFeatureValue::class, 'listing_feature_type_id');
    }
}

If you wanted to eager-load the listings with their features and types for a full output of all your listings, you could do so like this:

App\Listing::with('features.type')->get()->toJson();

My migration files look like this:

//create_listings_table
Schema::create('listings', function (Blueprint $table) {
    $table->increments('id');
    $table->string('uuid');
    $table->timestamps();
});

//create_listings_features_values_table
Schema::create('listings_features_values', function (Blueprint $table) {
    $table->increments('id');
    $table->string('listing_feature_text');
    $table->integer('listing_feature_type_id')->unsigned();
    $table->timestamps();
});

//create_listings_features_types_table    
Schema::create('listings_features_types', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->timestamps();
});

//create_listings_features_table
Schema::create('listings_features', function(Blueprint $table){
    $table->integer('listing_id')->unsigned();
    $table->integer('listing_feature_id')->unsigned();
});

You can learn more about the Collection methods here:

https://laravel.com/docs/5.3/collections#available-methods

...and eager-loading here:

https://laravel.com/docs/5.3/eloquent-relationships#eager-loading

...and many-to-many relationships here:

https://laravel.com/docs/5.3/eloquent-relationships#many-to-many