Querying Child-Parent Table in Laravel

Question

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!

Answer 1

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'));
}