Problem with migrations and relationship

You should have the accessories table migration running before the `bookings table migration so that the table exists to make the foreign key constraint?

@tykus hi, well that helped but after I changed the order as you suggested I have this new error:

[Illuminate\Database\QueryException] SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'booking _id' (SQL: create table booking_vespa (id int unsigned not null auto_in crement primary key, booking_id int unsigned not null, booking_id int n ot null, vespa_id int unsigned not null, vespa_id int not null) default character set utf8mb4 collate utf8mb4_unicode_ci)

Any ideas about it? Thanks, Vincenzo

@vinschi this is the correct code

Schema::create('booking_vespa', function (Blueprint $table) { 
        $table->increments('id'); 
        $table->integer('booking_id')->unsigned(); 
        $table->foreign('booking_id')->references('id')->on('bookings'); 
        $table->integer('vespa_id')->unsigned(); 
        $table->integer('vespa_id')->references('id')->on('vespas');
    }

You need to use the foreign() method when defining the constraint, not integer:

Schema::create('booking_vespa', function (Blueprint $table) { 
        $table->increments('id'); 
        $table->integer('booking_id')->unsigned(); 
        $table->foreign('booking_id')->references('id')->on('bookings'); 
        $table->integer('vespa_id')->unsigned(); 
        $table->foreign('vespa_id')->references('id')->on('vespas');
});

Same problem on the accessory_booking table migration:

Schema::create('accessory_booking', function (Blueprint $table) { 
    $table->increments('id'); 
    $table->integer('accessory_id')->unsigned(); 
    $table->foreign('accessory_id')->references('id')->on('accessories'); 
    $table->integer('booking_id')->unsigned(); 
    $table->foreign('booking_id')->references('id')->on('bookings'); 
});

Thanks guys, now it works.

@tykus and @sabid now I have this error when I'm trying to create a booking:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'vespas.booking_id' in 'where clause' (SQL: select * from vespas where vespas.booking_id is null and vespas.booking_id is not null limit 1)

in my create_vespas table I don't have a booking_id column because I created the pivot table (create_booking_vespa) and I have the relationship from both sides, do you know why I have this error? Do I have to add a booking id also in the create_vespas table? Is not enough having the pivot table?

Thanks, Vincenzo

If it can help, when I post through the form to /bookings I hit this store method:

public function store(Request $request) {

     //Validation
    $this->validate($request, [
         'from_day'          => 'required|date',
        'to_day'            => 'required|date',
        ...
    ]);

$booking = Booking::create([
        'from_day'              => request('from_day'),
        'to_day'       => request('to_day'),
         'user_id' =>    Auth::user()->id,
        'vespa_id' => request('vespa_id'),
        'accessory_id' => request('accessory_id')
        ...
        
    ]);

    
    return redirect()->back();

}

However vespa_id and accessory_id are null.

@tykus and @sabid when I'm trying to print the user from the booking

@foreach($bookings as $booking)
    <tr>
    <td>{{$booking->user->surname}} . ' ' . {{$booking->user->name}}</td>
    ...

I have this error, do you know why or how to fix it? Sorry guys but I'm a beginner :)

Trying to get property of non-object

Thanks

You have a booking with does not have a valid user - check your bookings table for any orphaned bookings

Hi, now seems to be working but without changing anything, the table were fine.

However I have a question related to one suggestion from you @tykus from a while ago, if one booking has more than one accessory ( I take it from the form request) and I need to call booking->accessories it means I need this settings:

In booking.php

public function accessories()
{
        return $this->belongsToMany(Accessory::class);
}

In accessory.php

public function bookings()
{
        return $this->hasMany(Booking::class);
}

then I need a pivot table accessory_booking in this way:

public function up()
    {
        Schema::create('accessory_booking', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('accessory_id')->unsigned();
            $table->foreign('accessory_id')->references('id')->on('accessories');
            $table->integer('booking_id')->unsigned();
            $table->foreign('booking_id')->references('id')->on('bookings');
        });
    }

and in the store method for BookingController:

...
$booking->accessories()->attach($request('accessories_id'));
$booking->save();

am I right? Thanks for the patience :)

All good, except for two suggestions:

1. Both sides of the many-to-many relationship have a belongsToMany relationship definition:

// Accessory.php
public function bookings()
{
        return $this->belongsToMany(Booking::class);
}

1. You do not need an auto-incrementing id on the pivot table.

This assumes that there form submits accessories_id as an array