I am building a search box/calendar. However, I am getting Unknown column 'product_name' in 'where clause'.

Here is parent model - Products

class Products extends Model
{
    use SoftDeletes;

    protected $table = 'products';

    protected $primaryKey = 'id';

    protected $fillable = [
        'product_image',
        'product_name',
        'product_details',
        'product_price',
        'product_description'
    ];

    public function inventory()
    {
        return $this->hasMany(Inventory::class);
    }
}

Child model - Inventory

class Inventory extends Model
{
    use SoftDeletes;

    protected $table = 'inventory';

    protected $primaryKey = 'id';

    protected $fillable = [
        'area', 
        'code',
        'best_before', 
        'in_date',
        'in_qty', 
        'out_date', 
        'out_qty'
    ];

    public function products()
    {
        return $this->belongsTo(Products::class);
    }
}

My Controller for search

public function search()
{
    $other = $_GET['other'];
    $fromDate = $_GET['fromDate'];
    $toDate = $_GET['toDate'];

    $inventory = Inventory::with('products')
        ->where('in_date', '>=', $fromDate.'%')
        ->where('out_date', '<=', $toDate.'%')
        ->where('area', 'LIKE', '%'.$other.'%')
        ->orWhere('code', 'LIKE', '%'.$other.'%')
        ->orWhere('product_name', 'LIKE', '%'.$other.'%')
        ->get();

    return view('inventory.search', compact('inventory'));
}

Migrations

public function up()
{
    Schema::create('products', function (Blueprint $table) {
        $table->id();
        $table->string("product_image");
        $table->string("product_name");
        $table->string("product_details");
        $table->string("product_description");
        $table->string("product_price");
        $table->softDeletes();
        $table->timestamps();
    });
}

public function up()
{
    Schema::create('inventory', function (Blueprint $table) {
        $table->id();
        $table->foreignId('products_id')
            ->constrained('products')
            ->onDelete('cascade')
            ->onUpdate('cascade');
        $table->string("area");
        $table->string("code");
        $table->date("best_before");
        $table->date("in_date");
        $table->integer("in_qty");
        $table->date("out_date")->nullable();
        $table->integer("out_qty")->nullable();
        $table->softDeletes();
        $table->timestamps();
    });
}

I tried to get product_name using whereHas, with, where, and select from various solutions here in stackoverflow to no avail. I changed product_name to products_id and it did run but the search is not working properly.

I've read the documentation, yet I always failed to get a column from the Parent table while using the Child table. English is not my native language so I struggle to understand it even more.

A not too complicated explanation about my error would be greatly appreciated!

CodePudding user response：

You are not correctly using Laravel, you are not following naming conventions and your query is not right.

This code:

public function search()
{
    $other = $_GET['other'];
    $fromDate = $_GET['fromDate'];
    $toDate = $_GET['toDate'];

    $inventory = Inventory::with('products')
        ->where('in_date', '>=', $fromDate.'%')
        ->where('out_date', '<=', $toDate.'%')
        ->where('area', 'LIKE', '%'.$other.'%')
        ->orWhere('code', 'LIKE', '%'.$other.'%')
        ->orWhere('product_name', 'LIKE', '%'.$other.'%')
        ->get();

    return view('inventory.search', compact('inventory'));
}

Should be like this:

public function search(Request $request)
{
    $other = $request->input('other');
    $fromDate = $request->input('fromDate');
    $toDate = $request->input('toDate');

    $inventory = Inventory::with('products')
        ->where('in_date', '>=', $fromDate.'%')
        ->where('out_date', '<=', $toDate.'%')
        ->where('area', 'LIKE', '%'.$other.'%')
        ->orWhere('code', 'LIKE', '%'.$other.'%')
        ->orWhereHas('products', function (Builder $query) {
            return $query->where('product_name', 'LIKE', '%'.$other.'%');
        })
        ->get();

    return view('inventory.search', compact('inventory'));
}