SQLSTATE[42S22]: Column not found: 1054 Unknown column 'items.box_box_barcode' in 'where clause'

Question

I am trying to display data from 2 database tables (boxes and items) that have 2 models with a one-to-many relationship in Laravel 5.8. When I try to display the data in my view I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'items.box_box_barcode' in 'where clause' (SQL: select * from `items` where `items`.`box_box_barcode` = TRTB0001 and `items`.`box_box_barcode` is not null)

See my code:

Box.php (model)

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Box extends Model
{

    protected $guarded = [];


    protected $primaryKey = 'box_barcode';
    public $incrementing = false;
    protected $keyType = 'string';



    public function items(){

        return $this->hasMany(Item::class);
    }
}

Item.php (model)

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Item extends Model
{

    protected $primaryKey = 'item_barcode'; // or null

    public $incrementing = false;

    // In Laravel 6.0+ make sure to also set $keyType
    protected $keyType = 'string';


    public function company(){

        return $this->belongsTo(Company::class);
    }
}

create_boxes_table.php (migration 1)

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateBoxesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('boxes', function (Blueprint $table) {
            //$table->bigIncrements('id');
            $table->string('box_barcode')->primary();
;      //want this to be my id that can increment
            $table->string('sort_description');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('boxes');
    }
}

create_items_table.php (migration 2)

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateItemsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('items', function (Blueprint $table) {
            //$table->bigIncrements('id');
            $table->string('item_barcode')->primary();
; //want this to be my id that can increment
            $table->string('its_box_barcode');
            $table->string('item_quality');
            $table->timestamps();


        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('items');
    }
}

boxesController.php (controller)

<?php

namespace App\Http\Controllers;

use App\Box;
use Illuminate\Http\Request;

class boxesController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $boxes = Box::all();       
        return view('boxes.index', compact('boxes'));
    }


    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        //
    }

    /**
     * Display the specified resource.
     *
     * @param  \App\Box  $box
     * @return \Illuminate\Http\Response
     */
    public function show(Box $box)
    {
        return view('boxes.show', compact('box'));
    }


}

index.blade.php (view)

@extends('layout')


@section('title', 'Boxes')  


@section('content')


    <h1>Boxes</h1>

     <ul style="list-style-type: none;">

       @foreach($boxes as $box) 

         <li>
            <a href="/boxes/{{ $box->box_barcode }}">
                {{$box->box_barcode}}  
            </a>
         </li>

       @endforeach

    </ul>           

 @endsection

show.blade.php (view)

@extends('layout')


@section('title', 'Show Box')  


@section('content')


@if ($box->items->count())
    <div>
        @foreach ($box->items as $item)

            <div>

                <form method="POST" action="/items/{{ $item->id }}">
                    @method('PATCH')
                    @csrf

                    <!-- USE TO STRIKETHROUGH A CONPLETED TASK IN WEB PAGE -->
                    <label class="checkbox {{ $item->in ? 'is-complete' : '' }}" for="in" >

                    <input type="checkbox" name="in" onChange="this.form.submit()" {{ $item->in ? 'checked' : '' }}>
                            {{ $item->item_barcode }}

                    </label>


                </form>



            </div>

        @endforeach

    </div>
@endif

@endsection

Answer 1

By using weird names for columns, you're losing a lot of what Laravel usually does automatically. But as stated in the documentation, you can "override the foreign and local keys by passing additional arguments to the hasMany method."

<?php
namespace App;

use Illuminate\Database\Eloquent\Model;

class Box extends Model {
    protected $guarded = [];
    protected $primaryKey = 'box_barcode';
    public $incrementing = false;
    protected $keyType = 'string';

    public function items() {
        return $this->hasMany(Item::class, 'its_box_barcode', 'box_barcode');
    }
}

You also need to define the inverse relationship between items and boxes, again using your non-standard column names.

<?php
namespace App;

use Illuminate\Database\Eloquent\Model;

class Item extends Model {

    protected $primaryKey = 'item_barcode'; // or null
    public $incrementing = false;
    protected $keyType = 'string';


    public function box() {
        return $this->belongsTo(Box::class, 'its_box_barcode', 'box_barcode');
    }
}

---

You would be well advised to stick with a framework's expected naming convention to save yourself a lot of trouble. i.e. boxes.box_barcode should be boxes.barcode, items.item_barcode should be items.barcode, and items.its_box_barcode should instead be items.box_id, pointing to a standard numeric key column called id. Yes, it's kind of wasteful if you already have another unique value in the database, but you're going to be fighting against convention if you do otherwise. So my suggested model code would be dead simple:

<?php
namespace App;

use Illuminate\Database\Eloquent\Model;

class Box extends Model {
    public function items() {
        return $this->hasMany(Item::class);
    }
}

class Item extends Model {
    public function box() {
        return $this->belongsTo(Box::class);
    }
}

You should also be defining these relationships at the database level using foreign key constraints in your migrations:

Schema::create('boxes', function (Blueprint $table) {
    $table->bigIncrements('id')->primary();
    $table->string('barcode');
    $table->string('sort_description');
    $table->timestamps();
});

Schema::create('items', function (Blueprint $table) {
    $table->bigIncrements('id')->primary();
    $table->string('box_id');
    $table->string('barcode');
    $table->string('quality');
    $table->timestamps();
    $table->foreign('box_id')->references('id')->on('boxes')->onDelete('cascade');
});

Now, in your template you can reference things like this:

@foreach ($boxes as $box)
    {{ $box->barcode }} 
    @foreach ($box->items as $item)
        {{ $item->barcode }}
    @endforeach
@endforeach