Column not found: 1054 Unknown column 'attributevalues.products_id

Question

I try to get products details with products attribute so I make a hasMany relation between two tables products table and attributevalues table but it gives this message

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'attributevalues.products_id' in 'where clause' (SQL: select * from attributevalues where attributevalues.products_id in (14))

my model code

<?PHP

namespace App;
use Illuminate\Database\Eloquent\Model;
class Products extends Model
{

protected $fillable = [
    'user_id', 'menu_id', 'childmenu_id', 'product_name', 'price', 'sale_price', 'quantity', 'stock', 'product_description', 'image', 'slug', 'status', 'meta_title', 'meta_keywords', 'meta_description'
];

public function productattributes()
{
    return $this->hasMany(Attributevalues::class);
}

}

my controller code

public function show($id)
{
    $products = Products::with('productattributes')
                ->leftJoin('menus', 'products.menu_id', '=', 'menus.id')    
                ->leftJoin('childmenus', 'products.childmenu_id', '=', 'childmenus.id')
                ->leftJoin('attributes', 'products.attribute_id', '=', 'attributes.id')
                ->select('products.*', 'menus.menu_name', 'childmenus.childmenu', 'attributes.attribute_name')
                ->findOrFail($id);

    //dd($products);
    return view('frontend.product-details', compact('products'));
}

please help me and tell me how to fix this problem Thanks in advance

OMR OMR · Accepted Answer · 2020-10-03T08:02:08

loading the relation doesn't mean that you joind the table, you should join with that table to be able to get result from it in the main select:

 $products = Products::with('productattributes')
                ->leftJoin('attributes', 'attributes.product_id', '=', 'products.id')    
                ->leftJoin('menus', 'products.menu_id', '=', 'menus.id')    
                ->leftJoin('childmenus', 'products.childmenu_id', '=', 'childmenus.id')
                ->leftJoin('attributes', 'products.attribute_id', '=', 'attributes.id')
                ->select('products.*', 'menus.menu_name', 'childmenus.childmenu', 'attributes.attribute_name')
                ->findOrFail($id);

otherwise you can show the columns form attributevalues inside loaded relation without joining like:

     $products = Products::with(['productattributes'=>function($query){
$query->select(['id','product_id','attribute_name']);
})]) .....

Column not found: 1054 Unknown column 'attributevalues.products_id

1 Answers