Eloquent Relationship not working

Published 7 months ago by Kaustubh

Hi There I just stucked on Eloquent Relationship. The Two table are not joining. I am getting this error SQLSTATE[42703]: Undefined column: 7 ERROR: column "quantity" does not exist LINE 1: select "quantity" from "mi_cmp_product" where "mi_cmp_produc... ^ (SQL: select "quantity" from "mi_cmp_product" where "mi_cmp_product"."id" is null and "product_id" = ABC3408804)

There are Two Table 1) Company Product & 2) Shop Inventory

  1. Company Product has product_id(PK)
  2. Shop Inventory has product_id(FK)

Company Product Model

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;

class CmpProduct extends Model
{
    protected $table='mi_cmp_product';
    protected $fillable=['product_id','regcity_code','company_id','category_id','sub_category_id','brand_id','hsn_id','tax_gst_id','variant_id','unit_id','alt_product_id','product_name','product_desc','product_barcode_symbol','product_alert_qty','product_track_req','product_tax_method','product_cf1','product_cf2','product_cf3','specification','lastupdated_by','lastupdated_datetime'];

    public function ShopInventory(){
        return $this->hasMany('App\ShopInventory', 'product_id', 'product_id');
    }
}

Shop Inventory Model

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;

class ShopInventory extends Model
{
    protected $table='mi_estshop_inventory';
    protected $fillable=['regcity_code','company_id','product_id','est_shop_id','unit_id','quantity','last_pur_price'];

    public function CmpProduct(){
        return $this->belongsTo('App\CmpProduct', 'product_id');
    }

    public function get_quantity($id)
    {
        $quantity = $this->CmpProduct()->where('product_id',$id)->pluck('quantity')->first();
        return $quantity;
    }
}

Controller

 $shop = new ShopInventory;
 return view('return_purchase',compact('shop');

View

<input type="text" name="available_qty" id="available_qty" value="{{ $shop->get_quantity($pur_items->product_id) }}">

Am i missing something??

Thanks & Regards

shez1983

and your model relationship / controller code? -

arthurvillar

@Kaustubh Your message is really hard to understand. Please format (display) your code properly and include the relationships you have on the Models.

Kaustubh

I just updated

Dexterr

@Kaustubh if im not mistaken, you are trying to pluck a column named Quantity from your CmpProduct model but you did not create the column under the $fillables var in your CmpProduct model, if you wanna join two tables,they need to have same columns aswell.

if Table1.quantity wants to join with Table2, then Table2 has to have a Table2.quantity column name.

Try adding a 'quantity' attribute to your CmpProduct model under $fillables

Kaustubh

@Dexterr I think that will not affect. I dont know why its not Joining two tables

JohnnyGan

In the example above, ShopInventory should have many products, and product belongs a certain ShopInventory, if I understand correct on your logic.

shez1983

i think that $quantity = $this->CmpProduct()->where('product_id',$id)->pluck('quantity')->first();

is the culprit here..

you are doing a where which returns a collection so you need to do:

$quantity = $this->CmpProduct()->where('product_id',$id)->first()->pluck('quantity');

actually i see you had first but in the wrong place

arthurvillar

You misused the hasMany() and the belongsTo() relationships. Why do you have a product_id on both tables?

Basically you have an Inventory and a Product. Since the inventory belongsTo() a single product, that table will have a product_id. But a product hasMany() inventories, so by calling that method Laravel will search in the inventory table for the ones that have a product_id matching the id of the product it is searching from.

This is what I would do

class CmpProduct extends Model
{
    public function ShopInventory(){
        return $this->hasMany('App\ShopInventory', 'product_id');
    }
}

class ShopInventory extends Model
{
    public function CmpProduct(){
        return $this->belongsTo('App\CmpProduct', 'product_id');
    }
}
biishmar

Check whether mi_cmp_product table have quantity column and change the CmpProduct function in ShopInventory Class like

public function CmpProduct(){
        return $this->belongsTo('App\CmpProduct', 'product_id', 'product_id');
    }
Kaustubh

@arthurvillar i followed your method but Still not working, I even changed foreign key name

arthurvillar

Hm... The problem might be here then

$this->CmpProduct()->where('product_id',$id)->pluck('quantity')->first();

$this->CmpProduct() will give you an instance of the product this inventory belongs to. That table does not have a quantity column right? Looks like this column is in the inventory table. If not, this explains the error.

Please sign in or create an account to participate in this conversation.