Laravel Eloquent Relationships with Many to Many

Question

I'm getting myself a bit confused. Hopefully you all can help.

I have a few different tables in my app:

1. inspections (a basic table that just holds id, date, etc.; all we need from this is the id)
2. customers (table contains the id and other customer info)
3. customer_types (table holds the different customer types, e.g. Buyer, Buyer's Agent, etc.)
4. inspection_customers_types (this table relates the inspection [inspection_id], the customer [customer_id], and the customer type [type_id])

Basically, the customer has a many to many relationship with the inspection and customer_types tables.

For example:

Customer {ID: 345} can belong to Inspection {ID: 10} with Customer Type {ID: 3 - Buyer's Agent}

Customer {ID: 345} can belong to Inspection {ID: 15} with Customer Type {ID: 1 - Buyer}

In my view, I'm attempting to get the customer_types.type value but I can't seem to get it to work, I think I've defined the relationships incorrectly, but I can't seem to get them correct. I'll list my relationships below:

Inspection Model

    public function customers() 
    {
        return $this->belongsToMany(Customer::class, 'inspection_customers_types')
            ->withPivot('type_id')
            ->withTimestamps();
    }

Customer Model

    public function customerTypes()
    {
        return $this->belongsToMany(CustomerType::class, 'inspection_customers_types')
                    ->withPivot('inspection_id')
                    ->withTimestamps();
    }

    public function inspections()
    {
        return $this->belongsToMany(Inspection::class, 'inspection_customers_types')
                    ->withPivot('type_id')
                    ->withTimestamps();
    }

Now, in my controller I'm eager loading the inspection:

$inspection->load('invoice', 'services', 'vendors', 'statusNotes', 'fileUploads', 'customers');

and passing it to my view where I attempt to show each customer with their related type name:

    <div class="row row-cols-4 mb-3">
        @foreach ($inspection->customers as $customer)
        <div class="col">
                <div class="card">
                    <div class="card-header">
                        <h5>{{ $customer->customerTypes->name }}</h5>
                    </div>
                    <div class="card-body">
                        <div class="row">
                            <div class="col">
                                <p class="m-0">{{ $customer->first_name . ' ' . $customer->last_name }}</p>
                                @if ($customer->phone_number_1)
                                    <p class="m-0">{{ $customer->phone_number_1 }}</p>
                                @endif
                                @if ($customer->phone_number_2)
                                    <p class="m-0">{{ $customer->phone_number_2 }}</p>
                                @endif
                                @if ($customer->email_1)
                                    <p class="m-0">{{ $customer->email_1 }}</p>
                                @endif
                                @if ($customer->email_2)
                                    <p class="m-0">{{ $customer->email_2 }}</p>
                                @endif
                                <span class="fw-bold">Lifetime Value:</span> ${{ $customer->lifetime_value }}
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        @endforeach
    </div>

This is where I get stuck... currently, I'm getting this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'inspection_customers_types.customer_type_id' in 'field list'

But I don't see anywhere that I'm calling customer_type_id so I have no idea why it would be giving me this error. This is the SQL statement it spits out if that helps anyone:

SELECT
  `customer_types`.*,
  `inspection_customers_types`.`customer_id` AS `pivot_customer_id`,
  `inspection_customers_types`.`customer_type_id` AS `pivot_customer_type_id`,
  `inspection_customers_types`.`inspection_id` AS `pivot_inspection_id`,
  `inspection_customers_types`.`created_at` AS `pivot_created_at`,
  `inspection_customers_types`.`updated_at` AS `pivot_updated_at`
FROM
  `customer_types`
  INNER JOIN `inspection_customers_types` ON `customer_types`.`id` = `inspection_customers_types`.`customer_type_id`
WHERE
  `inspection_customers_types`.`customer_id` = 1

Answer 1

Laravel cannot automatically set up the relationships by default, we got to be more specific about that:

class Inspection
{
    public function customers() 
    {
        return $this->belongsToMany(Customer::class, 'inspection_customers_types', 'inspection_id', 'customer_id')
            ->withPivot('type_id')
            ->withTimestamps();
    }
}

And:

class Customer
{
    public function customerTypes()
    {
        return $this->belongsToMany(CustomerType::class, 'inspection_customers_types', 'customer_id', 'type_id')
                    ->withPivot('inspection_id')
                    ->withTimestamps();
    }

    public function inspections()
    {
        return $this->belongsToMany(Inspection::class, 'inspection_customers_types', 'customer_id', 'inspection_id')
                    ->withPivot('type_id')
                    ->withTimestamps();
    }
}

Not related to your question, but why are you using the same pivot table for 3 different relationships?

That is very unconventional, what about using two different pivot tables:

customer_inspection
    inspection_id
    customer_id
customer_customer_type
    type_id
    customer_id