返回数据透视表数据以在Laravel中查看
问题描述：
I'm not quite sure what it is I'm doing wrong...
I've got a recipe project that I'm learning with, but I can't seem to get the data joined by a pivot table into the view.
The user creates a recipe [at (name, description, portions, tags etc), and is then directed to the "create ingredients" view. From here they are adding ingredients via an Ajax request, which is successfully saving to the ingredients table, and the link in recipe_ingredients.
I'm dynamically adding the ingredients to the table via ajax, but when the user refreshes the page, I need it to loop through the ingredients table, and return just those associated with the given recipe.
I'm using the following loop, but it isn't working:
@foreach($recipe->ingredients as $ingredient)
{{$recipe->ingredient->name}}
@endforeach
And I'm getting the following error:
ErrorException (E_ERROR):
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ingredients.recipe_ingredients' in 'where clause' (SQL: select * from `ingredients` where `ingredients`.`recipe_ingredients` is null and `ingredients`.`recipe_ingredients` is not null) (View: C:\xampp\htdocs\sitesecipesesources\views\ingredients\create.blade.php)
INFO:
I've got 3 tables: recipes, ingredients, recipe_ingredients
Recipe Model:
class Recipe extends Model
{
public function ingredients(){
return $this->hasMany('App\Ingredient', 'recipe_ingredients', 'recipe_id', 'ingredient_id');
}
Ingredients Model:
class Ingredient extends Model
{
public function recipes(){
return $this->belongsToMany('App\Recipe', 'recipe_ingredients', 'ingredient_id', 'recipe_id');
}
Ingredients Controller
public function create($recipe_id)
{
$recipe = Recipe::find($recipe_id);
return view('ingredients.create')->withRecipe($recipe);
}
public function store(Request $request)
{
$this->validate($request, [
'name' => 'required|max:255'
]);
$ingredient = new Ingredient;
$ingredient->name = $request->name;
$ingredient->save();
$recipe = Recipe::find($request->id);
$ingredient->recipes()->attach($recipe->id);
$data = [
'success' => true,
'message'=> 'Your AJAX processed correctly',
'name' => $ingredient->name,
'recipe' => $recipe,
] ;
return response()->json($data);
}
Ajax Script for saving ingredients to db:
<script>
$(document).ready(function(){
$("#submit").click(function() {
var name = $("#ingredientName").val();
var token = $("#token").val();
$.ajax({
headers: {
'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')
},
type: "post",
data: "name="+name,
dataType:'json',
url: "{{ route('ingredients.store', ['id' => $recipe->id]) }}",
success:function(data){
console.log(data);
$("#msg").html('<div class="alert alert-success my-0">'+data.name+' added</div>');
$("#msg").toggleClass("invisible")
$("#msg").fadeOut(2000);
$("#ingredientsTable").append('<tr><td scope="col" class="align-middle">'+data.name+'</td></tr>');
}
});
})
})
</script>
Relevant part of the view:
<div class="row">
<div class="col-lg-3">
<div class="card">
<div class="card-body">
<!--
<p><b>Est. prep time:</b></p>
<p><b>Est. cooking time:</b></p>
-->
<p><b>Portions:</b> {{ $recipe->portions }}</p>
<p><b>Creator:</b> <a href="#">{{ $recipe->user->name }}</a></p>
@if($recipe->created_at == $recipe->updated_at)
<p><b>Created:</b> {{ $recipe->created_at->diffForHumans() }}</p>
@else
<p><b>Updated:</b> {{ $recipe->updated_at->diffForHumans() }}</p>
@endif
<!--
<p><b>Rating:</b> <i class="fas fa-star"></i><i class="fas fa-star"></i><i class="fas fa-star"></i><i class="fas fa-star"></i><i class="fas fa-star-half"></i> (52)</p>
-->
<div id="tags">
@foreach($recipe->tags as $tag)
<button type="button" class="btn btn-outline-{{$tag->colour}} btn-sm my-1">{{$tag->name}}</button>
@endforeach
</div>
</div>
</div>
</div>
<div class="col-lg-9">
<table class="table table-hover table-light" style="border: 1px solid rgba(0,0,0,.125);">
<thead>
<tr>
<th scope="col">Ingredients:</th>
</tr>
</thead>
<tbody id="ingredientsTable">
@foreach($recipe->ingredients as $ingredient)
{{$recipe->ingredient->name}}
@endforeach
</tbody>
</table>
</div>
</div>
Routes:
$ php artisan route:list
+--------+-----------+--------------------------+--------------------+------------------------------------------------------------------------+--------------+
| Domain | Method | URI | Name | Action | Middleware |
+--------+-----------+--------------------------+--------------------+------------------------------------------------------------------------+--------------+
| | GET|HEAD | / | | App\Http\Controllers\PagesController@index | web |
| | GET|HEAD | api/user | | Closure | api,auth:api |
| | GET|HEAD | home | home | App\Http\Controllers\HomeController@index | web,auth |
| | GET|HEAD | login | login | App\Http\Controllers\Auth\LoginController@showLoginForm | web,guest |
| | POST | login | | App\Http\Controllers\Auth\LoginController@login | web,guest |
| | POST | logout | logout | App\Http\Controllers\Auth\LoginController@logout | web |
| | POST | password/email | password.email | App\Http\Controllers\Auth\ForgotPasswordController@sendResetLinkEmail | web,guest |
| | GET|HEAD | password/reset | password.request | App\Http\Controllers\Auth\ForgotPasswordController@showLinkRequestForm | web,guest |
| | POST | password/reset | | App\Http\Controllers\Auth\ResetPasswordController@reset | web,guest |
| | GET|HEAD | password/reset/{token} | password.reset | App\Http\Controllers\Auth\ResetPasswordController@showResetForm | web,guest |
| | POST | recipes | recipes.store | App\Http\Controllers\RecipesController@store | web,auth |
| | GET|HEAD | recipes | recipes.index | App\Http\Controllers\RecipesController@index | web,auth |
| | GET|HEAD | recipes/create | recipes.create | App\Http\Controllers\RecipesController@create | web,auth |
| | GET|HEAD | recipes/{id}/ingredients | ingredients.create | App\Http\Controllers\IngredientsController@create | web,auth |
| | POST | recipes/{id}/ingredients | ingredients.store | App\Http\Controllers\IngredientsController@store | web,auth |
| | GET|HEAD | recipes/{recipe} | recipes.show | App\Http\Controllers\RecipesController@show | web,auth |
| | DELETE | recipes/{recipe} | recipes.destroy | App\Http\Controllers\RecipesController@destroy | web,auth |
| | PUT|PATCH | recipes/{recipe} | recipes.update | App\Http\Controllers\RecipesController@update | web,auth |
| | GET|HEAD | recipes/{recipe}/edit | recipes.edit | App\Http\Controllers\RecipesController@edit | web,auth |
| | POST | register | | App\Http\Controllers\Auth\RegisterController@register | web,guest |
| | GET|HEAD | register | register | App\Http\Controllers\Auth\RegisterController@showRegistrationForm | web,guest |
| | POST | steps | steps.store | App\Http\Controllers\StepsController@store | web,auth |
| | GET|HEAD | steps | steps.index | App\Http\Controllers\StepsController@index | web,auth |
| | GET|HEAD | steps/create | steps.create | App\Http\Controllers\StepsController@create | web,auth |
| | GET|HEAD | steps/{step} | steps.show | App\Http\Controllers\StepsController@show | web,auth |
Migrations:
create_recipes_table
Schema::create('recipes', function (Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->text('description');
$table->integer('portions');
$table->integer('user_id');
$table->timestamps();
});
create_ingredients_table
Schema::create('ingredients', function (Blueprint $table) {
$table->increments('id');
$table->text('name');
$table->boolean('approved')->nullable();
$table->datetime('approved_at')->nullable();
$table->integer('approved_by')->nullable();
$table->timestamps();
});
create_recipe_ingredients_table
Schema::create('recipe_ingredients', function (Blueprint $table) {
$table->integer('recipe_id')->unsigned();
$table->foreign('recipe_id')->references('id')->on('recipes');
$table->integer('ingredient_id')->unsigned();
$table->foreign('ingredient_id')->references('id')->on('ingredients');
});
答
You get this error because your relationship on your Recipe model is not correct. Since the relationship between recipes and igredients is many-to-many, both models need to have belongsToMany relationship to the other model:
class Recipe extends Model
{
public function ingredients(){
return $this->belongsToMany('App\Ingredient', 'recipe_ingredients');
}
}
There's also an error in your foreach, you should reference the $ingredient from the loop:
@foreach($recipe->ingredients as $ingredient)
{{ $ingredient->name }}
@endforeach